last column (as long or string) with .copy function

Question

I'm trying to use a snippet of code i found here .

Sub foo()
Dim x As Workbook
Dim y As Workbook
Dim wbname As String
Dim lastcol As Long


wbname = Replace(ActiveWorkbook.name, ".xlsx", "")
'## Open both workbooks first:
Set x = Workbooks.Open("N:\blah\deposit\" & wbname & "*.xlsx")
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
mycol = GetColumnLetter(lastcol)

'Now, copy what you want from x:
x.Sheets("sheet1").Range("mycol:mycol").Copy
x.Close
'Now, paste to y worksheet:
y.Sheets("sheet1").Range("mycol:mycol").PasteSpecial
End Sub

Function GetColumnLetter(colNum As Long) As String
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter = vArr(0)
End Function

I can't seem to find how to copy the last column. It says error 9 subscript is out of range. I tried x.Sheets("sheet1").Columns(lastcol).Copy x.Sheets("sheet1").Range("mycol").copy , x.Sheets("sheet1").Range(mycol) and x.Sheets("sheet1").Range(Columns(mycol)).Copy

Is there something else I was supposed to do or declare? I serously do not understand why this is not working. :(

Change `Range("mycol:mycol")` to `Range(mycol & ":" & mycol)`. Varibles must be outside the quotes. — Scott Craner, Apr 18 '16 at 15:43
Also you can skip the Function and simply put: `x.Sheets("sheet1").Columns(lastcol).Copy` — Scott Craner, Apr 18 '16 at 16:00

score 0 · Accepted Answer · answered Apr 18 '16 at 16:05

0

To greatly simplify your code

Sub foo()
Dim x As Workbook
Dim y As Workbook
Dim wbname As String
Dim lastcol As Long


wbname = Replace(ActiveWorkbook.name, ".xlsx", "")
'## Open both workbooks first:
Set x = Workbooks.Open("N:\blah\deposit\" & wbname & "*.xlsx")
lastcol = Cells(1, Columns.Count).End(xlToLeft).Column

'Now, copy what you want from x:
x.Sheets("sheet1").Columns(lastcol).Copy

'Now, paste to y worksheet:
y.Sheets("sheet1").Columns(lastcol).PasteSpecial
x.Close
End Sub

You don't need the function to achieve what you're trying

answered Apr 18 '16 at 16:05

Tom

9,725
3
31
48

Thanks! I had used the function as an idea to solve the out of range error. It still goes error 9 at this line. does this mean my X has no sheet1? – Lara Apr 19 '16 at 07:51
Could very well be possible - Have a look at the sheet names and see if there is a `sheet1` there – Tom Apr 19 '16 at 08:59
their was. I managed to understand what was wrong. It was an intricate thing with changing sheet names. I added a step where I renamed them to the same name. It works now. thanks. – Lara Apr 20 '16 at 08:30
Well done. To avoid naming mishaps from the tab, you can use the sheet name in vba (and use whatever you like) if you click on the sheet in the vba editor then look at the poperties of it, you can see a box for the name. You can reference this in your code and avoid errors from the name being changed in the tab – Tom Apr 20 '16 at 08:33

last column (as long or string) with .copy function

1 Answers1