Copy + paste data range using loop on paste + X spaces

[Imbetteratjava]

Hi,

New to some of the VB terminology in excel and I can't seem to get this to work.
I've taken some examples on this site and put them together with others but I'm coming up short on a solid solution.

I have data in a range of B2:G2 down to blank rows (undefined)
I want to copy the first row of data (B2:G2) and paste it in another sheet starting at point (A2) then + ~about 10 to 20 spaces to give me room for a large picture
Then copy the next set eg (B3:G3)

Here's what I've been messing around with so far.

Sub NewMacro()
Dim ws1, ws2 As Worksheet
Dim copyRng, cell As Range
Dim lCopyLastRow As Long

Set ws1 = ThisWorkbook.Sheets(1)
Set ws2 = ThisWorkbook.Sheets(2)

'Find last used row in the copy range based on data in column A
lCopyLastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    

Set copyRng = ws1.Range("B2:G" & lCopyLastRow).End(x1Down).Row    'Getting an application-defined or object-defined error

For Each cell In copyRng
    ws2.Range("A" & 2 + 10 * copyRng.Rows(cell.Row).Row - 30).Value = cell.Value
Next cell


End Sub

Any tips or help appreciated.
Thanks.

 

[Fluff]

Hi & welcome to MrExcel.
You have x1Down but it should be xlDown (lower case L not number 1)

 

[Imbetteratjava]

Hi & welcome to MrExcel.
You have x1Down but it should be xlDown (lower case L not number 1)

That might've been an unfortunate accident when I copied it over, my code is showing that xldown not x1. I'll fix that.

 

[Fluff]

Ok, try it like

Sub NewMacro()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyRng As Range, Rw As Range
Dim lCopyLastRow As Long, i As Long

Set ws1 = ThisWorkbook.Sheets(1)
Set ws2 = ThisWorkbook.Sheets(2)

'Find last used row in the copy range based on data in column A
lCopyLastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
    

Set copyRng = ws1.Range("B2:G" & lCopyLastRow)    'Getting an application-defined or object-defined error
i = 2
For Each Rw In copyRng.Rows
   ws2.Range("A" & i).Resize(, 7).Value = Rw.Value
   i = i + 20
Next Rw


End Sub

 

[Imbetteratjava]

Ok, try it like

Sub NewMacro()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyRng As Range, Rw As Range
Dim lCopyLastRow As Long, i As Long

Set ws1 = ThisWorkbook.Sheets(1)
Set ws2 = ThisWorkbook.Sheets(2)

'Find last used row in the copy range based on data in column A
lCopyLastRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row
   

Set copyRng = ws1.Range("B2:G" & lCopyLastRow)    'Getting an application-defined or object-defined error
i = 2
For Each Rw In copyRng.Rows
   ws2.Range("A" & i).Resize(, 7).Value = Rw.Value
   i = i + 20
Next Rw


End Sub

This worked liked a charm, thank you for the help.

 

[Fluff]

You're welcome & thanks for the feedback.