extracting from excel spreadsheet and creating several files or sh

[Guest]

Hello all,

I have an excel database spreadsheet that has many columns, two of which
interest me. “Number†and “Systemâ€. What I want to do is go down each row
starting at say column 4 and extract the data from two columns, say column A
and B from row 4. Then put that extracted data into certain cells in another
sheet or even another file. (sheet will be simpler but file would be more
convenient ). And then save the document as a separate file so we can print
it. Then go to row 5 and so on. If the row’s column A is blank then there
should not be a new sheet/file created.

Basically I’m trying to make an automated form filling system so I don’t
have to copy and paste 1000+ entries. What I need/am trying to do is to go
down a list and extract and put the data for each row in that list into
another file and save it according (or put into another sheet and save the
workbook?) How to start this… I don’t know completely yet.

Does anyone know how to do this? Or even if visual basic is needed? I know
how to link cells to each other but I think I need to go beyond that here.

Example Database (Columns A-C… and Rows 4 to 6)
A B C …
4 X X X
5 Y Y Y
6 Z Z Z
..
..

Example form where the extracted first two columns per row will go:

Number __items go here (from row 4 column A)__
System __items go here (from row 4 column B)__

Then another sheet or file for row 5 column A and B, etc.

Any help would be greatly appreciated!

Thanks,
Lobo

 

[Tom Ogilvy]

with Worksheets("Data")
set rng = .Range(.Cells(4,1),.Cells(rows.count,1).End(xlup))
End With
for each cell in rng
if not isempty(cell) then
workbooks("Template1.xls").Worksheets(1).Copy
set sh = Activesheet
sh.Range("B9").Value = Cell
sh.Range("C3").Value = Cell.offset(0,1)
Application.DisplayAlerts = False
Activeworkbook.SaveAs "C:\NewBooks\" & cell.Value & ".xls"
Application.DisplayAlerts = True
ActiveWorkbook.Close
end if
Next

 

[Guest]

Heyas,
Thanks for the reply Tom.

I've having a little trouble trying to get this to run. Its giving me a
subscript out of range error. I'm trying to understand what the code does...
hmm

 

[Guest]

I managed to get passed that... It needed the template.xls to be open in the
background.... will be doing testing on it now... hehe

You the man!

 

[Guest]

Ok an update

The code is creating the files with the right names however it doesnt seem
to be putting the data in the new files it creates. I do however see the last
two cells that were supposed to be copied in the original sheet....hmm

I'm still playing around with it but any help would be appreciated.

Thanks again!

 

[Tom Ogilvy]

I set up a workbook with a sheet named Data. In data I had

A4 B4 C4
A5 B5 C5
A6 B6 C6

entered as text in the respective cells they describe.

I opened a new workbook and saved it as Template1.xls

I then went to B3 and typed in System:
I went to A9 and typed in Number:
Then saved it.

I create a directory C:\NewBooks

I put this code in the workbook that contained the sheet named data

Sub AA()
With Worksheets("Data")
Set rng = .Range(.Cells(4, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
If Not IsEmpty(cell) Then
Workbooks("Template1.xls").Worksheets(1).Copy
Set sh = ActiveSheet
sh.Range("B9").Value = cell
sh.Range("C3").Value = cell.Offset(0, 1)
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs "C:\NewBooks\" & cell.Value & ".xls"
Application.DisplayAlerts = True
ActiveWorkbook.Close
End If
Next

End Sub

I ran the code and it placed 3 workbooks in NewBooks
one named A4.xls, one named A5.xls, on named A6.xls

Each workbook was a single sheet workbook with the information contained in
the first sheet of Template.xls entered in the single sheet. Additionally,
next to each label was the appropriate value from the corresponding row from
the sheet named data.

System: B4





Number: A4

as an example.

So it works fine for me for the scenario I created - you provide a complete
requirement, so you would have to change where the data is placed in the
first sheet in template.xls.

this should be trivial.

 

[Guest]

Yes sir, i tried to understand the code, It took me a while and a lot of
playing around.. but I finally got it to work. I posted a lot because I
only get internet access from 12-1 at the place i'm working. I had to sneak
on someone elses computer to post before..hehe.

Thank you for all the help. Also, thanks for the second detailed reply.
You have saved me a lot of time. Much appreciated, keep up the great work.

Terrel Lobo