Won't Paste to Next Blank Row?

[msgtgumby]

Kind of new to this. I'm trying to copy range A:H from sheet 17 of one workbook to sheet 1 of another workbook (both already open). I got it to work, but it pasted to the top of sheet 1, I need it to paste to the next available blank row. I'm new at this and can't really figure out how to set my range to that? I tried a few things I found throughout various sites and forms, but couldn't get it to work. What's below is the last thing I've tried, still pops an error.

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook

Set Wb1 = ThisWorkbook
Set wb2 = Workbooks("invoiceTEST.xls")

Wb1.Sheets(17).Range("A:H").CurrentRegion.Copy wb2.Sheets("Invoice1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Thanks for any help you can give me.

 

[BarryL]

try,

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook
Set Wb1 = ThisWorkbook
Set wb2 = Workbooks("invoiceTEST.xls")
Wb1.Sheets(17).Range("A:H").CurrentRegion.Copy wb2.Sheets("Invoice1").Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
End Sub

 

[JLGWhiz]

Kind of new to this. I'm trying to copy range A:H from sheet 17 of one workbook to sheet 1 of another workbook (both already open). I got it to work, but it pasted to the top of sheet 1, I need it to paste to the next available blank row. I'm new at this and can't really figure out how to set my range to that? I tried a few things I found throughout various sites and forms, but couldn't get it to work. What's below is the last thing I've tried, still pops an error.

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook

Set Wb1 = ThisWorkbook
Set wb2 = Workbooks("invoiceTEST.xls")

Wb1.Sheets(17).Range("A:H").CurrentRegion.Copy wb2.Sheets("Invoice1").Range("A" & Rows.Count).Offset(1, 0)

End Sub

Thanks for any help you can give me.

If column A is blank, then your last row with data will be row 1, even though you have data in other columns, because you are using column A as the criteria column. To avoid that quirk, try this.

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook, lr As Long
lr = wb2.sheets("Invoice1").Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set Wb1 = ThisWorkbook
Set wb2 = Workbooks("invoiceTEST.xls")
Wb1.Sheets(17).Range("A:H").CurrentRegion.Copy wb2.Sheets("Invoice1").Range("A" & lr).Offset(1, 0)
End Sub

 

[msgtgumby]

try,

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook
Set Wb1 = ThisWorkbook
Set wb2 = Workbooks("invoiceTEST.xls")
Wb1.Sheets(17).Range("A:H").CurrentRegion.Copy wb2.Sheets("Invoice1").Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
End Sub

This now copies it to row 1055 for some reason? The row isn't blank, the first available blank row is 17790.

 

[msgtgumby]

If column A is blank, then your last row with data will be row 1, even though you have data in other columns, because you are using column A as the criteria column. To avoid that quirk, try this.

Sub CopyData()
Dim Wb1 As Workbook, wb2 As Workbook, lr As Long
lr = wb2.sheets("Invoice1").Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set Wb1 = ThisWorkbook
Set wb2 = Workbooks("invoiceTEST.xls")
Wb1.Sheets(17).Range("A:H").CurrentRegion.Copy wb2.Sheets("Invoice1").Range("A" & lr).Offset(1, 0)
End Sub

A isn't blank, I had to make sure of it, a few other things I run on the sheet required A to have data.

 

[BarryL]

what does the below give you?

sub test()
dim lr as long
lr = Workbooks("invoiceTEST.xls").Sheets("Invoice1").Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1)
msgbox lr
End sub

 

[msgtgumby]

When I run it in invoiceTEST.xls it says 0, when I run it from my other workbook it says 110. I have no clue what's going on here, it's 17k+ rows of data so I don't understand.

 

[BarryL]

sorry should be below

sub test()
dim lr as long
lr = Workbooks("invoiceTEST.xls").Sheets("Invoice1").Range("A" & Rows.Count).End(xlUp).Row
msgbox lr
End sub

 

[JLGWhiz]

A isn't blank, I had to make sure of it, a few other things I run on the sheet required A to have data.

Did you try the code in post #3?