VBA code to copy and paste a range of cells in a dynamic way

[kelly mort]

I have two sheets:
Sheet1 and sheet2

On sheet1 I am using column A to determine the used cells. That is from A7 to last used cell.

Now on sheet2 I am copying B4:E14 then paste it at G4:J14

Then I paste the same B4:E14 (already copied in the clipboard) to B16:E26

And same range in the clipboard pasted to G16:J26 and so on.

So if I have seven used cells in the column A as defined above, then I will paste the range B4:E14 six times as defined in the copying process above.

I hope someone can help me out with this.

Thanks

 

[NdNoviceHlp]

Hi Kelly. Perhaps I can be of more assistance today than yesterday. HTH. Dave

Sub test()
Dim Rng As Range, Acnt As Integer, LastRow As Integer
'Grab Some Data and Store it in a "Range" variable
LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Acnt = LastRow - 8
Set Rng = Sheets("Sheet1").Range("B4:E" & Acnt)
'Transfer Values to another worksheet (Mimics PasteSpecial Values Only)
Worksheets("Sheet2").Range("G4").Resize(Rng.Rows.Count, _
                       Rng.Columns.Count).Cells.Value = Rng.Cells.Value
Worksheets("Sheet2").Range("B16").Resize(Rng.Rows.Count, _
                       Rng.Columns.Count).Cells.Value = Rng.Cells.Value
'etc
End Sub

 

[kelly mort]

@NdNoviceHlp
The code is copying data from sheet1 instead of sheet2

All the copy and paste is needed on the sheet2

I am only using the sheet1 to determine how many times I need to paste the copied data (B4:E14)

 

[NdNoviceHlp]

Whoops. My misunderstanding. Dave

Set Rng = Sheets("Sheet2").Range("B4:E" & Acnt)

 

[kelly mort]

Great!
It worked when I updated this line as well

Acnt = LastRow

So reading your code again, I think the Rng should be a static range

Set Rng = Sheets("Sheet2").Range("B4:E14”)

With this been said or taken care of, the next move is to be able to paste to the various ranges as described in the original post.

I think a few for loops and a creative offset parameters could get the job done for me but I can’t figure it out yet.