Macro to build code

[Dave]

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.

 

[Mark Ivey]

Can you post what you had recorded? I might be able to work with that...

Mark

 

[Don]

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

 

[Don]

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

 

[Dave]

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]

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.... (e-mail address removed)

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

 

[Mark Ivey]

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

 

[Dave]

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]

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

 

[Anthony0247]

Can you post what you had recorded?  I might be able to work with that....

Mark








- 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.

 

[Greg Glynn]

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