Copy-paste from partly unknown range VBA

[dotsent]

Hi!

Sorry for the vague subject. I have a seemingly simple task at hand, however lacking ideas towards solution. Can't even figure out great keywords for search.

I need to copy some data from Sheet2 to Sheet1 via VBA. There are 10 columns of data on Sheet2 (range A:J - can copy them all). However, the actual row count is unknown (varies). Still, I know data is consistent, so first empty row in Sheet2 column A means the end of area to be copied.

How can I copy an area where the row count is yet to be identified (although column count is known)?

Ideally I'd like to copy starting from Sheet2 cell A2 and paste to Sheet1 cell A2, as both sheets have headers, but overwriting them is not a big deal. Still, Copy-Paste just values should be more important, but I don't expect trouble handling this.

I would prefer NOT to hardcode the range which would likely cover all the filled area to the like of:

Worksheets("Sheet2").Range("A2:J5000").Copy

Thanks a bunch!

 

[dotsent]

Phew, here's perhaps a partial answer. I guess I have to identify the final row and somehow integrate that value in the Range selection? So this should give me the final row number to be copied:

Sub Last_row()


Dim sht As Worksheet
Dim LastRow As Long


Set sht = ActiveSheet
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


End Sub

However how to use LastRow variable next to column J instead of 5000?

Worksheets("Sheet2").Range("A2:J5000").Copy

 

[Fluff]

How about

Sub dotsent()
   With Sheets("sheet1")
      .UsedRange.ClearContents
      Sheets("sheet2").Range("A1").CurrentRegion.Copy
      .Range("A1").PasteSpecial xlPasteValues
   End With
   Application.CutCopyMode = False
End Sub

 

[dotsent]

Thanks, that does the basic job quite well! Didn't know the .UsedRange and .CurrentRegion methods, but we are here to learn! I don't really need (nor want) to first clean the entire Sheet1 as there is some necessary data there and the area where I'm pasting in Sheet1 will be empty anyway.

However does the CurrentRegion.Copy function provide any flexibility in ignoring the 1st row, so I could not copy the header from Sheet2 (and I could then run the Paste function in A2, leaving the existing header intact)?

 

[Fluff]

How about

Sub dotsent()
   Sheets("sheet2").Range("A1").CurrentRegion.Offset(1).Copy
   Sheets("sheet1").Range("A2").PasteSpecial xlPasteValues
   Application.CutCopyMode = False
End Sub

 

[dotsent]

Thank you, looks great!

 

[Fluff]

You're welcome & thanks for the feedback