Macro to copy and paste a variable range between worksheets

[mdesroc]

What is the most efficient way to take all of the values from A2 down in worksheet 1 and past it into worksheet 2 starting with A7? The issue I am running into the is number of values I will be copying changes and could go from as small as one row all the way up to 1000s of rows.

Im still learning VBA, so any advice or ideas would be appreciated

 

[Trevor G]

If A2 is the first row of data you could use something like Range("A2").currentRegion.Copy then move to worksheet and paste from there, have you tried recording the macro to do this to see the code?

 

[JackDanIce]

"take all of the values from A2 down in worksheet 1"

If you mean all values from a single column where you do not know the last row used, you can use something like:

Dim Last_Row as Long
With Sheets("Sheet1")
  Last_Row = .Range("A" & Rows.Count).End(xlUp).Row
  .Range("A2:A" & Last_Row).Copy
End With
Sheets("Sheet2").Range("A7").PasteSpecial xlPastevalues