Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex copy and paste looping VBA help | Excel Programming | |||
Complex Copy/Paste HELP.... | Excel Programming | |||
Complex Copy/Paste help | Excel Programming | |||
Complex Copy/Paste help | Excel Programming | |||
complex copy and paste | Excel Programming |