Complex copy and paste in excel
 

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

Complex copy and paste in excel
 

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


" wrote:

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

Complex copy and paste in excel
 

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
Please rate when your question is answered to help us and others know what
is helpful.


" wrote:

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

Complex copy and paste in excel
 

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.


" wrote:

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

Complex copy and paste in excel
 

On Nov 6, 12:28*pm, John Bundy (remove) wrote:
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.



" wrote:
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- Hide quoted text -

- 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!

Complex copy and paste in excel
 

On Nov 6, 1:25*pm, wrote:
On Nov 6, 12:28*pm, John Bundy (remove) wrote:





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.

" wrote:
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- Hide quoted text -

- 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!- 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.