Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
I have a spreadsheet of items and their sizes. Each item has 7 sizes. I need
to add 6 rows under each item and copy the data from the from the first row. There are 150 items in the spreadsheet and I basically need to create a macro to copy each item 6 times right underneath the original item. I'm not very familiar with macros. When I started trying this, I just kept getting the macro to create the same row over and over. Any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
Can you post what you had recorded? I might be able to work with that...
Mark "Dave" wrote in message ... I have a spreadsheet of items and their sizes. Each item has 7 sizes. I need to add 6 rows under each item and copy the data from the from the first row. There are 150 items in the spreadsheet and I basically need to create a macro to copy each item 6 times right underneath the original item. I'm not very familiar with macros. When I started trying this, I just kept getting the macro to create the same row over and over. Any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
On Mar 4, 9:46*pm, "Mark Ivey" wrote:
Can you post what you had recorded? *I might be able to work with that.... Mark "Dave" wrote in message ... I have a spreadsheet of items and their sizes. Each item has 7 sizes. I need to add 6 rows under each item and copy the data from the from the first row. There are 150 items in the spreadsheet and I basically need to create a macro to copy each item 6 times right underneath the original item. I'm not very familiar with macros. When I started trying this, I just kept getting the macro to create the same row over and over. Any help would be greatly appreciated.- Hide quoted text - - Show quoted text - ------- Hello everyone, I need similar help. I am trying to build a marco to creat real estate trend charts. I need to work with different amounts of data every time; however, the column layout is the same everytime. I record a basis marco, but it messes up when I have different amount of sales to graph. I am self taught and I think I just need someone to open my eyes a little further. Any help would be greatly appreciated. Anthony. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
Hi Anthony,
The macro you recorded then needed to be changed so it does two more things. 1) Loops 6 times 2) Uses dynamic substitution to change the row and column variables for each new loop 1) For x = 1 to 6 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
Dave, Not sure how your data is laid out but if all your entries are in one column, the macro below will seperate each existing row and copy and paste the data into six cells below same. I've tested this to about 7000 rows with no problem but try it on a copy of your WS just to be safe. Option Explicit Sub AddSixRows() Dim i, t As Long Dim LastRow As Long On Error Resume Next ' find the last row used With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' select the last cell used Range("A" & LastRow).Select ' insert 6 rows between each cell from last cell up For t = LastRow - 1 To 1 Step -1 For i = 1 To 6 Selection.EntireRow.Insert Next Range("A" & t).Select Next ' find the current last row used With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' copy data to the six empty rows For i = 1 To LastRow Step 7 Range("A" & i).Select Selection.Copy For t = 1 To 6 Selection.Offset(1, 0).Select ActiveSheet.Paste Next Next ' cut copy mode Application.CutCopyMode = False End Sub HTH, Don "Dave" wrote: I have a spreadsheet of items and their sizes. Each item has 7 sizes. I need to add 6 rows under each item and copy the data from the from the first row. There are 150 items in the spreadsheet and I basically need to create a macro to copy each item 6 times right underneath the original item. I'm not very familiar with macros. When I started trying this, I just kept getting the macro to create the same row over and over. Any help would be greatly appreciated. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
Dave,
The code I posted assumes your data is in Col A...adjust as necessary. If needed I can add an input box to the macro that will let you choose which column the macro should read. Let me know. Don "Don" wrote: Dave, Not sure how your data is laid out but if all your entries are in one column, the macro below will seperate each existing row and copy and paste the data into six cells below same. I've tested this to about 7000 rows with no problem but try it on a copy of your WS just to be safe. Option Explicit Sub AddSixRows() Dim i, t As Long Dim LastRow As Long On Error Resume Next ' find the last row used With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' select the last cell used Range("A" & LastRow).Select ' insert 6 rows between each cell from last cell up For t = LastRow - 1 To 1 Step -1 For i = 1 To 6 Selection.EntireRow.Insert Next Range("A" & t).Select Next ' find the current last row used With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' copy data to the six empty rows For i = 1 To LastRow Step 7 Range("A" & i).Select Selection.Copy For t = 1 To 6 Selection.Offset(1, 0).Select ActiveSheet.Paste Next Next ' cut copy mode Application.CutCopyMode = False End Sub HTH, Don "Dave" wrote: I have a spreadsheet of items and their sizes. Each item has 7 sizes. I need to add 6 rows under each item and copy the data from the from the first row. There are 150 items in the spreadsheet and I basically need to create a macro to copy each item 6 times right underneath the original item. I'm not very familiar with macros. When I started trying this, I just kept getting the macro to create the same row over and over. Any help would be greatly appreciated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
Don:
Thank you so much for your help. I'm wondering if it would be possible to talk to you offlist. I'm wondering if it would be possible to hire you to help me build a more complex macro for this spreadsheet. Thanks, Melinda "Don" wrote: Dave, The code I posted assumes your data is in Col A...adjust as necessary. If needed I can add an input box to the macro that will let you choose which column the macro should read. Let me know. Don "Don" wrote: Dave, Not sure how your data is laid out but if all your entries are in one column, the macro below will seperate each existing row and copy and paste the data into six cells below same. I've tested this to about 7000 rows with no problem but try it on a copy of your WS just to be safe. Option Explicit Sub AddSixRows() Dim i, t As Long Dim LastRow As Long On Error Resume Next ' find the last row used With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' select the last cell used Range("A" & LastRow).Select ' insert 6 rows between each cell from last cell up For t = LastRow - 1 To 1 Step -1 For i = 1 To 6 Selection.EntireRow.Insert Next Range("A" & t).Select Next ' find the current last row used With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With ' copy data to the six empty rows For i = 1 To LastRow Step 7 Range("A" & i).Select Selection.Copy For t = 1 To 6 Selection.Offset(1, 0).Select ActiveSheet.Paste Next Next ' cut copy mode Application.CutCopyMode = False End Sub HTH, Don "Dave" wrote: I have a spreadsheet of items and their sizes. Each item has 7 sizes. I need to add 6 rows under each item and copy the data from the from the first row. There are 150 items in the spreadsheet and I basically need to create a macro to copy each item 6 times right underneath the original item. I'm not very familiar with macros. When I started trying this, I just kept getting the macro to create the same row over and over. Any help would be greatly appreciated. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to build code
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF TO BUILD SIMPLE CODE | Excel Worksheet Functions | |||
how do i build the macro i need? | Excel Programming | |||
Build in Code | Excel Worksheet Functions | |||
How to build build a macro that automatically imports | Excel Programming | |||
Build a Macro | Excel Programming |