VBA to read "form"

[Guest]

Hi-

I have a "pretty" excel spreadsheet, that to the users, looks like a form.
The top half of the "form" has Company info and the bottom half has a "table"
showing multiple serial and model numbers.

This has worked wonderfully in a manual system.

Now, I would like to learn how to move this data into a "useable"
spreadsheet for me, while keeping it "pretty" for the users entering the data.

Basically, I need a crash course on how to:
-Prompt for what file to look at
-Read the top half of that file, placing each needed cell into a new workbook
-Loop thru the bottom half of that file, placing each need cell into a row
with the above info

Before:
Auto Parts - Store #1
1234 Hollywood Blvd.
123-456-7890
etc.
-------------
Q-Oil 121212 V-Oil 445544
Wiper 383838 Fuse 999999

After:
Auto Parts Store #1 1234 Hollywood Blvd. 123-456-7890 Q-Oil 121212
Auto Parts Store #1 1234 Hollywood Blvd. 123-456-7890 V-Oil 445544
Auto Parts Store #1 1234 Hollywood Blvd. 123-456-7890 Wiper 383838

This file I can Pivot and put into Access.
I would like to open another file (Store #2) and add that data below Store #1

Any help would be appreciated.
If I get a "shell" VBA that is well explained, I hope to be able to tweak
the rest (Although this will be me first real VBA code - so go easy on me).
Thanks a bunch.

 

[Guest]

Well, if this is just an excel worksheet, all the cells are addressed. it
should be pretty easy. I probably do things the hard way but here are some
examples:

to get data from a cell ,save it to a variable, and paste it into a row with
a changing number. This code will probably run rather clumsily but it is a
start.

'first declare your variables
dim rrow 'row number of destination cell
dim cval 'variable to use to pass data to other worksheet.
dim current ' current worksheet name.
dim cbook ' current workbook name
current = activeworksheet.name
cbook = activeworkbook.name


rrow = 10 'starting row of data in another worksheet.

range("a1").select
cval = selection.formula
windows("destination.xls").activate 'active excel file called "destination"
cells(rrow,1).select 'select cell in row number RROW in column 1
selection.formula = cval 'paste in data
windows(cbook).activate 'return to source spreadsheet
worksheet(current).select 'just in case you left your source page.
rrow = rrow+1 ' increment row number (only works if global, I would save
this value to a hidden cell if you need it later or write a while loop)





Throw stuff like this into a loop and you should have no problems moving
your data.