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.

I have attached an example of how "Team Report" looks. The number of players can change on a daily basis but the value is always displayed in B5.

Any help would be greatly appreciated.

Regards,

Richard

[RHCPgergo]

Hi,

should it copy only twice (once to B11 and once to B71) or continue copying until there is nothing left to copy? (Meaning: copy to B11, then to B71, then to B131, B191, B251, etc.)

[richard_a]

Hi,

I'd like it to keep going until there was nothing left

Thanks

[RHCPgergo]

Try running this macro. I assumed that there's already an existing worksheet named "Data".

Please Login or Register  to view this content.

[richard_a]

Thank you for the reply.

Yes there is a worksheet named "Data"

I tried running the macro but got an error message "400"

[RHCPgergo]

It worked for me on the example file. Don't know what it could be... can you please try running this one as well? It might give a more detailed error message. If so, can you paste it here?

Please Login or Register  to view this content.

*** edit ****

Maybe your Data worksheet is hidden?
Or are there hidden cells (rows, columns) on that worksheet?
Or merged cells maybe?

[richard_a]

Thank you for your suggestions. For some reason it wouldn't work. The data sheet wasn't hidden.

A suggestion by another member did work so I thought I'd share it:

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