Complex copy and paste in excel

[matrix7410]

Hello, I have a spreadsheet that has the listings of all the 50 states
in Column A. But, I would like to copy each state in a different
spreadsheet with 6 columns apart. For example.

State
Alabama
Alaska
Arizona

This is what I would like to see
State
Alabama
-
-
-
-
-
-
Alaska
-
-
-
-
-
-
Arizona

 

[Joel]

Sub MoveStates()

With Sheets("Sheet1")
OldRow = 1
NewRow = 1
Do While .Range("A" & OldRow) <> ""
.Range("A" & OldRow).Copy _
Destination:=Sheets("sheet2").Range("A" & NewRow)

OldRow = OldRow + 1
NewRow = NewRow + 6
Loop

End With

End Sub

 

[John Bundy]

Assuming you have the states in Column 1 beginning at row 2 and that you want
to output to column 1 starting at row 2. Place this in a module
Sub copyStates()
i = 2
For j = 2 To 52
Sheet2.Cells(i, 1) = Sheet1.Cells(j, 1)
i = i + 7
Next
End Sub

 

[John Bundy]

I just saw you sain colums, did you want 6 columns in between? It looks like
by your example you want rows which i gave.

 

[matrix7410]

I just saw you sain colums, did you want 6 columns in between? It looks like
by your example you want rows which i gave.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.





- Show quoted text -

Hi John Bundy, yes, I meant rows. I pasted your code and I'm getting
error message that says "Run timer error 424': Object required". Now,
my state listings are in the sheet "Data" and starts on A3 and I want
to paste that info to the sheet "Facility" on A9. Here is edited
version of the code that you gave me that I typed in the macro on
"Facility" sheet.
Sub Copy()
i = 3
For j = 3 To 52
Facility.Cells(i, 1) = Data.Cells(j, 1)
i = i + 6
Next
End Sub

thanks!

 

[matrix7410]

Hi John Bundy, yes, I meant rows.  I pasted your code and I'm getting
error message that says "Run timer error 424': Object required".  Now,
my state listings are in the sheet "Data" and starts on A3 and I want
to paste that info to the sheet "Facility" on A9.  Here is edited
version of the code that you gave me that I typed in the macro on
"Facility" sheet.
Sub Copy()
i = 3
For j = 3 To 52
Facility.Cells(i, 1) = Data.Cells(j, 1)
i = i + 6
Next
End Sub

thanks!- Hide quoted text -

- Show quoted text -

I just chaged the name of the sheets to "Sheet1" and "Sheet2" and the
code works. I'm wondering why did I had an error when I had different
names for the sheets.