Transferring data from one workbook to another

Question

The below script currently loops through a list of data and copies a range from one workbook to another depending on a value in a cell.

For i = 3 To LastRow

If Cells(i, 1) <> "" And Cells(i, 13) = "ü" Then
Range(Cells(i, 1), Cells(i, 12)).Select
Selection.Copy

workbooks.Open Filename:=iForm
Worksheets("Statistics").Select
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Worksheets("Statistics").Cells(erow, 1).PasteSpecial Paste:=xlPasteValues
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.CutCopyMode = False
End If
Next i

Rather copying the range from A:L I would like to copy columns A & L, I've tried updating this however I keep getting errors, please help. I thank you in advance.

When using multiple workbooks/worksheets, you should use variables to store those. [Don't use `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). You're probably getting errors from that. For instance, `erow` should equal `ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Offset(1, 0).Row`, but you should swich out the `Activesheet` with the one you intend to work with. Also, what errors do you get, at what line? — BruceWayne, Aug 17 '16 at 17:28
@BruceWayne. In an attempt to copy A & L i adjusted the following line which returns an error 13 (type mismatch) `Range(Cells(i, 1) And Cells(i, 12)).Select` — Paul, Aug 17 '16 at 17:40
That's odd - it shouldn't. What is the code before that? I'm going to assume it's your use of the worksheets. If the data is on the "Statistics" sheet, try this: `Worksheets("Statistics").Range(Worksheets("Statistics").Cells(i, 1), Worksheets("Statistics").Cells(i, 12)).Select` — BruceWayne, Aug 17 '16 at 17:50
Isn't the solution provided copying the range from A to L rather than A and L? Coping the entire row is not an issue, i'm just trying to copy those two columns in particular — Paul, Aug 17 '16 at 18:06

score 0 · Accepted Answer · answered Aug 17 '16 at 18:25

I think I see what you're trying. I suggest giving your worksheets a variable, and then you can select entire columns via Range():

Sub t()
Dim mainWS As Worksheet
Set mainWS = ActiveWorkbook.Worksheets("Sheet1") ' Tweak as necessary, this sheet is where your main data is (the non-blank cell, and "ü") 

lastRow = mainWS.Cells(mainWS.Rows.Count,1).End(xlUp).Row 'or whatever your formula is. Make sure to reference the worksheet

For i = 3 To lastRow
    If mainWS.Cells(i, 1) <> "" And mainWS.Cells(i, 13) = "ü" Then
        mainWS.Range("A:A,L:L").Select
        Selection.Copy

        Workbooks.Open Filename:=iForm
        'Worksheets("Statistics").Select
        Set statWS = ActiveWorkbook.Worksheets("Statistics")

        erow = statWS.Cells(statWS.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

        statWS.Cells(erow, 1).PasteSpecial Paste:=xlPasteValues
        ActiveWorkbook.Save
        ActiveWorkbook.Close
        Application.CutCopyMode = False
    End If
Next i

End Sub

And note, I left in .Select, just to keep it similar to your OP. Although you should definitely look into avoid using .Select

This is great, thanks Bruce. Admittedly it's not quite working yet but I can tweak it to work with my sheet. Thank you :) — Paul, Aug 17 '16 at 18:56

Transferring data from one workbook to another

1 Answers1