Last Row, Last Column Copy - Error in My code

[bvanscoy678]

Hello,


New to VBA, just finished Chandoo's VBA Course. I am missing something after I find the last row and last column of the data I want to import. I find the last row and last column, but I am not copying it. Confused on that portion of the with Statement.


I also have an error when I attempt to paste it in my new range. Basically I am copying everything in a worksheet, then adding it to the last row of my existing worksheet.


This is well documented all over the place, but I just can't quite figure it out.


Thanks for look at it. Brent


[pre]Option Explicit</p>
<p>Public Sub ImportFile()<br />
' Get workbook...<br />
Dim ws As Worksheet<br />
Dim filter As String<br />
Dim targetWorkbook As Workbook, wb As Workbook<br />
Dim Ret As Variant<br />
Dim Caption As String<br />
Dim TargetRng As Range<br />
Dim MyLastRow As Integer<br />
Dim MyLastColumn As Integer</p>
<p> Set targetWorkbook = Application.ActiveWorkbook</p>
<p> ' get the customer workbook<br />
filter = "Excel files (*.xls),*.xls"<br />
Caption = "Please Select an input file "<br />
Ret = Application.GetOpenFilename(filter, , Caption)</p>
<p> If Ret = False Then Exit Sub</p>
<p> Set wb = Workbooks.Open(Ret)</p>
<p> With wb.ActiveSheet<br />
'find last used row in column A<br />
MyLastRow = Cells(Rows.Count, "A&#34.End(xlUp).Row<br />
'find last used column in row 1<br />
MyLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column</p>
<p> End With</p>
<p> With targetWorkbook("imported&#34.ActiveSheet<br />
MyLastRow = .Range("A" & Rows.Count).End(xlUp).Row<br />
.Range("A" & MyLastRow + 1).PasteSpecial Paste:=xlPasteValues<br />
End With</p>
<p>End Sub[/pre]

 

[bvanscoy678]

I think I have the first part:

With wb.ActiveSheet

'find last used row in column A

MyLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'find last used column in row 1

MyLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column

Range("a2", .Cells(MyLastRow, MyLastColumn)).Select

[code]

 

[bvanscoy678]

I think I fixed it:


Option Explicit

[pre]<br />
Public Sub ImportFile()<br />
' Get workbook...<br />
Dim ws As Worksheet<br />
Dim filter As String<br />
Dim targetWorkbook As Workbook, wb As Workbook<br />
Dim Ret As Variant<br />
Dim Caption As String<br />
Dim TargetRng As Range<br />
Dim MyLastRow As Integer<br />
Dim MyLastColumn As Integer</p>
<p> Set targetWorkbook = Application.ActiveWorkbook</p>
<p> ' get the customer workbook<br />
filter = "Excel files (*.xls),*.xls"<br />
Caption = "Please Select an input file "<br />
Ret = Application.GetOpenFilename(filter, , Caption)</p>
<p> If Ret = False Then Exit Sub</p>
<p> Set wb = Workbooks.Open(Ret)</p>
<p> With wb.ActiveSheet<br />
'find last used row in column A<br />
MyLastRow = Cells(Rows.Count, "A&#34.End(xlUp).Row<br />
'find last used column in row 1<br />
MyLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column<br />
.Range("a2", .Cells(MyLastRow, MyLastColumn)).Copy<br />
End With</p>
<p> 'Note for activating target workbook: Workbooks("Book1&#34.Worksheets("Sheet1&#34</p>
<p> With targetWorkbook.Worksheets("Imported&#34<br />
MyLastRow = .Range("A" & Rows.Count).End(xlUp).Row<br />
.Range("A" & MyLastRow + 1).PasteSpecial Paste:=xlPasteValues<br />
End With</p>
<p>End Sub<br />
[/pre]