Macro to build code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - 05 Mar 2008 04:11 GMT

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.

Reply to this Message

Mark Ivey - 05 Mar 2008 04:46 GMT

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

Mark

> I have a spreadsheet of items and their sizes. Each item has 7 sizes. I
> need
[quoted text clipped - 8 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

Anthony0247@gmail.com - 15 Apr 2008 04:43 GMT

> Can you post what you had recorded? I might be able to work with that...
>
[quoted text clipped - 14 lines]
>
> - 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.

Reply to this Message

Greg Glynn - 15 Apr 2008 06:45 GMT

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

Reply to this Message

Don - 05 Mar 2008 05:07 GMT

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

> 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.
[quoted text clipped - 5 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

Don - 05 Mar 2008 05:11 GMT

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,
>
[quoted text clipped - 60 lines]
> >
> > Any help would be greatly appreciated.

Reply to this Message

Dave - 07 Mar 2008 03:47 GMT

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

> Dave,
>
[quoted text clipped - 68 lines]
> > >
> > > Any help would be greatly appreciated.

Reply to this Message

Don - 07 Mar 2008 18:00 GMT

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.... brner2spams@spamsaol.com

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

> Don:
>
[quoted text clipped - 77 lines]
> > > >
> > > > Any help would be greatly appreciated.

Reply to this Message

Mark Ivey - 08 Mar 2008 13:48 GMT

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

> Be happy to talk to you Melinda, but I'm certainly not an expert at
> building
[quoted text clipped - 101 lines]
>> > > >
>> > > > Any help would be greatly appreciated.

Reply to this Message

Dave - 08 Mar 2008 14:20 GMT

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

> Payment is not necessary (but is always appreciated)...
>
[quoted text clipped - 123 lines]
> >> > > >
> >> > > > Any help would be greatly appreciated.

Reply to this Message

Mark Ivey - 08 Mar 2008 14:40 GMT

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

> Thank you both so much for your posts. I just needed some big help and
> didn't
[quoted text clipped - 171 lines]
>> >> > > >
>> >> > > > Any help would be greatly appreciated.

Reply to this Message