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.

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.

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.

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.

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.

Be happy to talk to you Melinda, but I'm certainly not an expert at building
code, I'm more like a self-taught beginner at this. If you'd like to contact
me you can do so at this email less the spams....

And I'm not good enough to be charging for this service, but there are many
on here that do that for a living....and they are "GOOD", and they're more
than helpful sharing their knowledge.

Have a good day,

Don

"Dave" wrote:

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.

Payment is not necessary (but is always appreciated)...

There are many on here that just like to help out (including me) just for
the challenge and to stay fresh with coding.

With your particular problem, it may be easier if someone could look at your
data layout. Sometimes a description just doesn't draw a good picture of
what needs to be accomplished. When you are posting with a problem like
this, it is helpful to have a description and a brief example of your data.
It is difficult to format that type of data for this newsgroup sometimes,
but I would at least try to show two or more examples of your data to give
us all in here a chance to see if we can help out.

Otherwise, you would have to email your workbook to someone for help.

Some of the data I work with (my own stuff) cannot be sent to anyone outside
my workplace due to the nature of my work. In this type of situation (if I
still need outside help), I will alter my information (not to reveal company
info) and post an example of what I want help with.

Mark



"Don" wrote in message
...
Be happy to talk to you Melinda, but I'm certainly not an expert at
building
code, I'm more like a self-taught beginner at this. If you'd like to
contact
me you can do so at this email less the spams....


And I'm not good enough to be charging for this service, but there are
many
on here that do that for a living....and they are "GOOD", and they're more
than helpful sharing their knowledge.

Have a good day,

Don

"Dave" wrote:

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.

Thank you both so much for your posts. I just needed some big help and didn't
want to take advantage.

Here is what my ultimate goal is - I need to build UPC codes off a
spreadsheet and thought I could use a macro to accomplish. My original post
goal was to create six lines between products, then I need to go back through
and take the model, color and size to create the upc. I thought if I started
with a macro to build 7 individual items, then I can do a second macro to
pull from the different cells to build the upc code.

I've included a sample here. Ultimately the upc code should look like this
for the first item: z100whtxxs, z100whtxs, z100whts, z100whtm, etc. for every
item I have.

This may not be possible, but if anyone could help, I'd greatly appreciate it!

Here is the sample data that goes from Column a to Colum S with the header
as the first row and then sample data - due to the size of the window cost is
bumped under UPC Code when in reality it is just a header.

Product UPC
Code MODEL COLOR DECO Print COST COST COST RETAIL XXS XS S M L XL XXL
DRI-BALANCE TEE Z100 WHT
DRI-BALANCE TEE Z101 WHT
DRI-BALANCE TEE Z102 WHT
DRI-BALANCE TEE Z103 WHT








"Mark Ivey" wrote:

Payment is not necessary (but is always appreciated)...

There are many on here that just like to help out (including me) just for
the challenge and to stay fresh with coding.

With your particular problem, it may be easier if someone could look at your
data layout. Sometimes a description just doesn't draw a good picture of
what needs to be accomplished. When you are posting with a problem like
this, it is helpful to have a description and a brief example of your data.
It is difficult to format that type of data for this newsgroup sometimes,
but I would at least try to show two or more examples of your data to give
us all in here a chance to see if we can help out.

Otherwise, you would have to email your workbook to someone for help.

Some of the data I work with (my own stuff) cannot be sent to anyone outside
my workplace due to the nature of my work. In this type of situation (if I
still need outside help), I will alter my information (not to reveal company
info) and post an example of what I want help with.

Mark



"Don" wrote in message
...
Be happy to talk to you Melinda, but I'm certainly not an expert at
building
code, I'm more like a self-taught beginner at this. If you'd like to
contact
me you can do so at this email less the spams....


And I'm not good enough to be charging for this service, but there are
many
on here that do that for a living....and they are "GOOD", and they're more
than helpful sharing their knowledge.

Have a good day,

Don

"Dave" wrote:

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.

Dave,

I think I see what you are trying to do, but I think I still need a bit more
to go on. Would you mind sending me the workbook?

My email address is: wmivey6311 AT hotmail DOT com

I have worked with UPCs once before. Will the UPC require a leading or
trailing asterisk mark?

Mark

"Dave" wrote in message
...
Thank you both so much for your posts. I just needed some big help and
didn't
want to take advantage.

Here is what my ultimate goal is - I need to build UPC codes off a
spreadsheet and thought I could use a macro to accomplish. My original
post
goal was to create six lines between products, then I need to go back
through
and take the model, color and size to create the upc. I thought if I
started
with a macro to build 7 individual items, then I can do a second macro to
pull from the different cells to build the upc code.

I've included a sample here. Ultimately the upc code should look like this
for the first item: z100whtxxs, z100whtxs, z100whts, z100whtm, etc. for
every
item I have.

This may not be possible, but if anyone could help, I'd greatly appreciate
it!

Here is the sample data that goes from Column a to Colum S with the header
as the first row and then sample data - due to the size of the window cost
is
bumped under UPC Code when in reality it is just a header.

Product UPC
Code MODEL COLOR DECO Print COST COST COST RETAIL XXS XS S M L XL XXL
DRI-BALANCE TEE Z100 WHT
DRI-BALANCE TEE Z101 WHT
DRI-BALANCE TEE Z102 WHT
DRI-BALANCE TEE Z103 WHT








"Mark Ivey" wrote:

Payment is not necessary (but is always appreciated)...

There are many on here that just like to help out (including me) just for
the challenge and to stay fresh with coding.

With your particular problem, it may be easier if someone could look at
your
data layout. Sometimes a description just doesn't draw a good picture of
what needs to be accomplished. When you are posting with a problem like
this, it is helpful to have a description and a brief example of your
data.
It is difficult to format that type of data for this newsgroup sometimes,
but I would at least try to show two or more examples of your data to
give
us all in here a chance to see if we can help out.

Otherwise, you would have to email your workbook to someone for help.

Some of the data I work with (my own stuff) cannot be sent to anyone
outside
my workplace due to the nature of my work. In this type of situation (if
I
still need outside help), I will alter my information (not to reveal
company
info) and post an example of what I want help with.

Mark



"Don" wrote in message
...
Be happy to talk to you Melinda, but I'm certainly not an expert at
building
code, I'm more like a self-taught beginner at this. If you'd like to
contact
me you can do so at this email less the spams....


And I'm not good enough to be charging for this service, but there are
many
on here that do that for a living....and they are "GOOD", and they're
more
than helpful sharing their knowledge.

Have a good day,

Don

"Dave" wrote:

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.

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.

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