Help >> Looping form generation from spreadsheet list

[JokerFrowns]

Hello everybody,

I have a moderate understanding of excel and macro creation but
nothing to the level I believe I would need to do what I need. Once a
month I have to generate labels (in the form of a 1-page printout) for
a number of customers I have been filling them out by hand in the
past, but was wondering if it would be possible to make an excell
macro to do it for me. An independent file for each label by customer
must also be generated for my records.

My spreadsheet of itemized orders looks as follows

Name Pick Up Loc Delivery Loc Date Item
A A1 A2
A3 ItemA1
A A1 A2
A3 ItemA2
A A1 A2
A3 ItemA3
B B1 B2
B3 ItemB1

and so on, my list is well over 1000 rows. There is no limit or
pattern to the number of independent items an individual could have
ordered.

With my limited knowledge of programing, I would imagine something
like the following would need to take place.

For (Spreadsheet) Do until no more data

For -NAME- Do until -NEW CONTENTS IN NAME-

Create a new form from file (Form template.xls);

Take all data cells and copy them into appropriate cells on a form
including name;

Copy each equipment into its own cell in a vertical sequence of
cells on the form;

Save the form to (Labels directory) as (*Name*.xls);

Close the form;

End

End

Can anyone help me with this? Is it even possible in excell?

Many thanks.

Ryan

 

[Tom Ogilvy]

http://www.rondebruin.nl/copy5.htm

 

[JokerFrowns]

Thanks, I will look into this.

I may not have mentioned but my label form does not match (cell/column/
header references) my source data spreadsheet (as Ron states it
should). Also I don't believe I have the savey to loop this process
within my needs. I am quite new to VB code within excel and was
wondering if anyone had coded a loop like this before and had it
available otherwise I fear it would take me less time to simply fill
in the forms by hand than learn complex coding loops...

Thanks,

Ryan

 

[JokerFrowns]

In Ron's example I am trying to work away at a solution, but I keep
getting stuck on this line, what do I need to "change"

Set ws1 = Sheets("Sheet1") '<<< Change

 

[Tom Ogilvy]

You would replace "sheet1" with the name of a sheet. I don't know where it
is used in the specific code you have selected to work with, so I can't say
whether it is the name of the sheet containing the original data or the name
of the sheet where you are placing a subset of the data matching a particular
key value.

 

[Ron de Bruin]

Read this

Name of the worksheet with your data table
Set ws1 = Sheets("Sheet1") '<<< Change

 

[JokerFrowns]

In Ron's example I am trying to work away at a solution, but I keep
getting stuck on this line, what do I need to "change"

Set ws1 = Sheets("Sheet1") '<<< Change

 

[JokerFrowns]

In Ron's example I am trying to work away at a solution, but I keep
getting stuck on this line, what do I need to "change"

Set ws1 = Sheets("Sheet1") '<<< Change

Alright, I have gotten it to successfully split my data list into one
list per client, however I still have absolutely no idea how to make
it into a format to match cell reference to data form and save
appropriately.

 

[Dave Peterson]

What's the name of the sheet that contains the data?

Say it's "My Data" (without the double quotes), then you'd use:

set ws1 = sheets("My Data")