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

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


"(E-Mail Removed)" 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
>

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.


"(E-Mail Removed)" 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
>

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.


"(E-Mail Removed)" 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
>

On Nov 6, 12:28*pm, John Bundy <jmbu...@gmail.com(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.
>
>
>
> "matrix7...@yahoo.com" 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!

On Nov 6, 1:25*pm, matrix7...@yahoo.com wrote:
> On Nov 6, 12:28*pm, John Bundy <jmbu...@gmail.com(remove)> wrote:
>
>
>
>
>
> > I just saw you sain colums, did you want 6 columns in between? It lookslike
> > 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.
>
> > "matrix7...@yahoo.com" 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.