VBA - Code is duplicating paste into column not specified in code

Question

I'm using the below code to copy column B in combinedWorkbook to column B in ThisWorkbook but when running the macro it seems to paste column B into column C of ThisWorkbook as well as pasting into column B. I've stepped through the code and it works fine. This seems very strange and would be grataeful with any help on why it's also pasting into column C in ThisWorkbook.

Sub ImportWriteOffs()
    Dim filter As String
    Dim caption As String
    Dim combinedFilename As String
    Dim combinedWorkbook As Workbook
    ' Open BRAM Report Source Data
    MsgBox ("Select 'SRMF0035 BRAM Pre Repurchase'")
    filter = "Text files (*.*),*.*"
    caption = "Select 'SRMF0035 BRAM Pre Repurchase'"
    combinedFilename = Application.GetOpenFilename(filter, , caption)
    If combinedFilename <> "False" Then
        Set combinedWorkbook = Application.Workbooks.Open(combinedFilename)
    Else
        MsgBox "No file was uploaded", vbExclamation
        GoTo LastLine
    End If
    If combinedWorkbook.Worksheets(1).Range("D7").Value = "Periodic Insurance" Then
        ' Copy and Paste into working file
        Sheets("Tabular Version").Select
        Range("B10:B100000").Select
        Selection.Copy
        ThisWorkbook.Activate
        Sheets("Input - Write offs").Select
        Range("B10:B100000").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        combinedWorkbook.Close False
        ' Delete last row
        ThisWorkbook.Activate
        Sheets("Input - Write offs").Select
        Range("B10").Select
        Selection.End(xlDown).Select
        Selection.EntireRow.Delete
    Else
        MsgBox "Incorrect File Selected"
        combinedWorkbook.Close False
        Exit Sub
    End If
LastLine:
End Sub

Please check out **how to create a [mcve]**, to include only the minimal amount - but all of - the code required to produce the problem. (Plus, often the process of *creating* the [mcve] leads to a solution.) Also you could try using [F8] to step through your code to determine exactly where the problem lies... more on that in: [**Debugging VBA**](http://www.cpearson.com/excel/DebuggingVBA.aspx) from Chip Pearson. After that, it would be helpful if you remove extra spacing from the code but indent properly; this is another way that bugs can often be spotted. — ashleedawg, Aug 22 '18 at 13:06
side note, instead of `goto LastLine`, just use `Exit Sub`. Also the very last line that currently says `Exxit Sub` is unnecessary since the sub is finished anyway. And one more recommended post to read: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — ashleedawg, Aug 22 '18 at 13:14

urdearboy · Answer 1 · 2018-08-22T15:16:37.803

You can try this. Notice that you do not need to .Select a cell to copy it. It defeats the purpose of VBA! Just get right to the point: State the range and copy it. No need to select.

Also, no need for GoTo as mentioned by the infamous @ashleedawg, just Exit Sub when needed.

Sub ImportWriteOffs()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Input - Write offs")

Dim filter As String, caption As String, combinedFilename As String
Dim combinedWorkbook As Workbook, ws2 as Worksheet

MsgBox ("Select 'SRMF0035 BRAM Pre Repurchase'")
    filter = "Text files (*.*),*.*"
    caption = "Select 'SRMF0035 BRAM Pre Repurchase'"
    combinedFilename = Application.GetOpenFilename(filter, , caption)

If combinedFilename <> "False" Then
    Set combinedWorkbook = Application.Workbooks.Open(combinedFilename)
    Set ws2 = combinedWorkbook.Sheets("Tabular Version")
Else
    MsgBox "No file was uploaded", vbExclamation
    Exit Sub
End If

If combinedWorkbook.Worksheets(1).Range("D7") = "Periodic Insurance" Then
    ws2.Range("B10:B" & ws2.Range("B" & ws.Rows.Count).End(xlUp).Row - 1).Copy
        ws.Range("B10").PasteSpecial xlPasteValues
        ws.Range("B10").PasteSpecial xlPasteFormats
            combinedWorkbook.Close False
Else
    MsgBox "Incorrect File Selected"
    combinedWorkbook.Close False
 End If

End Sub

score 0 · Answer 2 · answered Aug 22 '18 at 14:34

This is happening because the select is actually using a relative reference. But it would be clearer what you want to do if you used Cells instead:

For r = 10 to 10000
  ActiveWorkbook.Worksheets("Input - Write-offs").Cells(r, 2) = combinedWorkbook.Worksheets("Tabular Version").Cells(r, 2)
Next

You can implement something similar for deleting the last row, if you are so inclined.

VBA - Code is duplicating paste into column not specified in code

2 Answers2