Macro to copy and paste a variable range

[richard_a]

Hi,

I am looking to write a macro that will copy and paste several ranges of cell values from one worksheet (Team Report) to another (Data).

The width of the ranges will always be constant, 63 columns (A to BK)
The height of the ranges however will vary (from day to day). The number of rows in each range is specified in cell B5 in "Team Report"

When running the macro I would like it to copy a range "B5" rows down by 63 columns wide from the starting point of A11 in "Team Report" and paste only the values into "Data" starting at cell B11.

I would then like it to return to "Team Report" and copy the next range (again "B5" rows down by 63 columns wide, starting from where the last range had finished), pasting it into "Data" starting at cell B71.

I have attempted this a few times myself, but my excel skills leave a lot to be desired when it comes to VBA. I suspect that it may need to involve the OFFSET function, using the value in B5 to dictate where to begin the copy selection.

Any help would be greatly appreciated.

Regards,

Richard

 

[Rick Rothstein]

Does this macro do what you want...

Sub TeamReportToData()
  Dim X As Long, RowCount As Long, DataRow As Long, LastRow As Long
  LastRow = Worksheets("Team Report").Cells(Rows.Count, "A").End(xlUp).Row
  RowCount = Range("B5").Value
  DataRow = 11
  For X = 11 To LastRow Step RowCount
    Worksheets("Data").Cells(DataRow, "B").Resize(RowCount, 63).Value =  _
       Worksheets("Team Report").Cells(X, "A").Resize(RowCount, 63).Value
    DataRow = DataRow + 60
  Next
End Sub

 

[richard_a]

Rick,

Thank you for this. I just tried it and it works great. Would there be any way to extend the code so that it keeps copying in that fashion until there is no data left to copy?

 

[Rick Rothstein]

Rick,

Thank you for this. I just tried it and it works great. Would there be any way to extend the code so that it keeps copying in that fashion until there is no data left to copy?

Uh, it is already supposed to do that... are you saying it doesn't? If so, then that means Column A (which is what I used) is the wrong column to use when determining the last row of data. Is there a column guaranteed to always contain the last piece of data? If so, use that column letter in place of the "A" in this line of code from my posted macro...

LastRow = Worksheets("Team Report").Cells(Rows.Count, "A").End(xlUp).Row

If not, then use this line of code in place of it...

LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Row

 

[richard_a]

My apologies Rick, it worked perfectly the first time, I just hadn't realised.

Thanks again for your help with this

 

[richard_a]

Hi Rick,

I was wondering if you may be able to help with this other query of mine, or perhaps suggest someone that could?

http://www.mrexcel.com/forum/excel-questions/701974-import-data-2-csv-files-into-same-worksheet.html

Many Thanks

Richard