Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column
A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
Say datalist is on Sheet1.
You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
This works, however, I get a "selection to large" error. So I have to do
this in smaller increments. I appreciate your help. "RagDyeR" wrote: Say datalist is on Sheet1. You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
reneets
VBA macro can do it in one step. Makes a copy of the worksheet first and inserts the blank rows on that sheet. Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With ActiveSheet.Copy Befo=Sheets(1) For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(9, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Wed, 21 Mar 2007 08:34:02 -0700, reneets wrote: This works, however, I get a "selection to large" error. So I have to do this in smaller increments. I appreciate your help. "RagDyeR" wrote: Say datalist is on Sheet1. You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
You've made me curious!
Approximately how many rows could you copy down before you received the "error"message? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "reneets" wrote in message ... This works, however, I get a "selection to large" error. So I have to do this in smaller increments. I appreciate your help. "RagDyeR" wrote: Say datalist is on Sheet1. You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
Thanks for the link, I really need to bone up on my VBA
"Gord Dibben" wrote: reneets VBA macro can do it in one step. Makes a copy of the worksheet first and inserts the blank rows on that sheet. Sub InsertRow_At_Change() Dim i As Long With Application .Calculation = xlManual .ScreenUpdating = False End With ActiveSheet.Copy Befo=Sheets(1) For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If Cells(i - 1, 1) < Cells(i, 1) Then _ Cells(i, 1).Resize(9, 1).EntireRow.Insert Next i With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + r to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. Run or edit the macro by going to ToolMacroMacros. You can also assign this macro to a button or a shortcut key combo. Gord Dibben MS Excel MVP On Wed, 21 Mar 2007 08:34:02 -0700, reneets wrote: This works, however, I get a "selection to large" error. So I have to do this in smaller increments. I appreciate your help. "RagDyeR" wrote: Say datalist is on Sheet1. You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
less than 100
"Ragdyer" wrote: You've made me curious! Approximately how many rows could you copy down before you received the "error"message? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "reneets" wrote in message ... This works, however, I get a "selection to large" error. So I have to do this in smaller increments. I appreciate your help. "RagDyeR" wrote: Say datalist is on Sheet1. You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
Strange?!?
What version do you have? Perhaps your WB is large and using a lot of resources. On my XL02, new blank WB, I copied down the *entire* range in a *single* pass. Down to Row 9126 ! ! ! -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "reneets" wrote in message ... less than 100 "Ragdyer" wrote: You've made me curious! Approximately how many rows could you copy down before you received the "error"message? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "reneets" wrote in message ... This works, however, I get a "selection to large" error. So I have to do this in smaller increments. I appreciate your help. "RagDyeR" wrote: Say datalist is on Sheet1. You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
fill series
I am on a hunt to see what is chewing up my resources. I appreciate the help.
"RagDyeR" wrote: Strange?!? What version do you have? Perhaps your WB is large and using a lot of resources. On my XL02, new blank WB, I copied down the *entire* range in a *single* pass. Down to Row 9126 ! ! ! -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "reneets" wrote in message ... less than 100 "Ragdyer" wrote: You've made me curious! Approximately how many rows could you copy down before you received the "error"message? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "reneets" wrote in message ... This works, however, I get a "selection to large" error. So I have to do this in smaller increments. I appreciate your help. "RagDyeR" wrote: Say datalist is on Sheet1. You want first cell of data (A1) copied to, say B1 of new sheet. Second cell of data (A2) copied to B10 of new sheet (8 empty rows between 1 and 10). Try this in B1 - (can be entered *anywhere*, not necessarily B1): =INDEX(Sheet1!$A$1:$A$1014,ROWS($1:9)/9) NOW ... select B1 *TO* B9 (with rows 2 to 9 empty). Click on the fill handle of this *9 row selection*, and drag down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "reneets" wrote in message ... I have a worksheet tab that has 1014 rows of data numbered 1 - 1014 in column A. I need to create another tab (within the workbook) that will take the numbered row data from column A of the tab with the 1014 rows of data and inserting 8 rows between each number. What is the best way to accomplish this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill series | New Users to Excel | |||
fill series - I think! | Excel Discussion (Misc queries) | |||
Edit Fill Series - How do I fill using minute increments | Excel Discussion (Misc queries) | |||
FILL SERIES | Excel Discussion (Misc queries) | |||
Fill series but a little different | Excel Discussion (Misc queries) |