S
Screamerz
First of all, apologies for using a fake email addy. Just trying to ward off
the spammers...
A little background on what I'm trying to do. Every year, we print out
labels for boxes that are part of a food basket charity that donates boxes
of food and supplies to low-income people so they can have something for
Christmas. The issue is that the customer wants a label for each box, saying
box 1 of 4, box 2 of 4, and so on. In the excel file there is only one line
with the person's name, address and how many boxes the person is to receive.
In order to create a label for each box, I have to insert a blank line for
each box, and then fill down the data from the first line so I have a
duplicate record for each box, then fill in a series to get 1, 2, 3, and so
on. It's a cumbersome process and there are 330 families, for a total of
1770 boxes.
What I am trying to do is to create a macro that when I select a single cell
that is on the row of each family, it would do the process I described
above, by reading the column that contains how many boxes a family is to
receive. I did a macro recording to get an idea. Here's a sample for a
family that is to receive 4 boxes:
Sub OFBSetup()
Rows("1:1").Select
Selection.Insert Shift:=xlUp
Selection.Insert Shift:=xlUp
Selection.Insert Shift:=xlUp
Rows("4:4").Select
Selection.AutoFill Destination:=Rows("1:4"), Type:=xlFillCopy
Rows("1:4").Select
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E4"), Type:=xlFillSeries
Range("E1:E4").Select
End Sub
I have an idea how to make it work, where I would select the first cell in a
row that I want to expand, then execute the macro-which is to read the value
in column F, which is the number of boxes total, subtract one, then do a
loop where a row would be inserted below the original row, copy the data
from the original row, then increment the value in Column E by 1 in the
newly copied row. The loop will repeat until the value in column E - 1 is
reached.
To clarify, If I select a cell of a row where the value in Column F is "4",
the macro will insert 3 rows, copy down all the values of the entire row
into the blank rows, and do a series fill down into each row of Column E, so
each successive row will read 1, 2, 3, 4.
I hope this helps anyone understand what I want to do. I do have a basic
understanding of programming, but I'm totally new at creating macros for
Excel and am not knowledgeable on the syntax needed to accomplish what I'm
trying to do. I need to have this macro working by Wednesday afternoon since
we have to have the labels printed by Thursday. If I did it manually, it
would have taken me all day to do it, and the macro would be a great
timesaver.
Thanks for your help!!!
the spammers...
A little background on what I'm trying to do. Every year, we print out
labels for boxes that are part of a food basket charity that donates boxes
of food and supplies to low-income people so they can have something for
Christmas. The issue is that the customer wants a label for each box, saying
box 1 of 4, box 2 of 4, and so on. In the excel file there is only one line
with the person's name, address and how many boxes the person is to receive.
In order to create a label for each box, I have to insert a blank line for
each box, and then fill down the data from the first line so I have a
duplicate record for each box, then fill in a series to get 1, 2, 3, and so
on. It's a cumbersome process and there are 330 families, for a total of
1770 boxes.
What I am trying to do is to create a macro that when I select a single cell
that is on the row of each family, it would do the process I described
above, by reading the column that contains how many boxes a family is to
receive. I did a macro recording to get an idea. Here's a sample for a
family that is to receive 4 boxes:
Sub OFBSetup()
Rows("1:1").Select
Selection.Insert Shift:=xlUp
Selection.Insert Shift:=xlUp
Selection.Insert Shift:=xlUp
Rows("4:4").Select
Selection.AutoFill Destination:=Rows("1:4"), Type:=xlFillCopy
Rows("1:4").Select
Range("E1").Select
Selection.AutoFill Destination:=Range("E1:E4"), Type:=xlFillSeries
Range("E1:E4").Select
End Sub
I have an idea how to make it work, where I would select the first cell in a
row that I want to expand, then execute the macro-which is to read the value
in column F, which is the number of boxes total, subtract one, then do a
loop where a row would be inserted below the original row, copy the data
from the original row, then increment the value in Column E by 1 in the
newly copied row. The loop will repeat until the value in column E - 1 is
reached.
To clarify, If I select a cell of a row where the value in Column F is "4",
the macro will insert 3 rows, copy down all the values of the entire row
into the blank rows, and do a series fill down into each row of Column E, so
each successive row will read 1, 2, 3, 4.
I hope this helps anyone understand what I want to do. I do have a basic
understanding of programming, but I'm totally new at creating macros for
Excel and am not knowledgeable on the syntax needed to accomplish what I'm
trying to do. I need to have this macro working by Wednesday afternoon since
we have to have the labels printed by Thursday. If I did it manually, it
would have taken me all day to do it, and the macro would be a great
timesaver.
Thanks for your help!!!