Exporting data

[EB21]

I have several workbooks for 1 office-1 main workbook in which I would like
the data to flow into 3 seperate workbooks. I would like to place a formula
to enter name that would place all the information from the row into one of
the seperate work books. For instance the main workbook holds the
information for all vehicle manufacturers, make, models, pricing, inventory,
etc. I would like to have individual workbooks import the information for
each manufacturer streaming from the original workbook, is this possible?

 

[Otto Moehrbach]

Yes. But you would need VBA (programming) to do that. I'll try to work up
something for you but I need more information. Using your example of
automobile manufacturers, I take it that you have only 3 manufacturers
listed in the main workbook. Is that right?
I'll assume that, in the main workbook, the name of the manufacturers are in
Column A, starting in A2.
I'll assume that the 3 workbooks are each named the name of one of the 3
manufacturers.
I'll assume that you want to import 10 columns starting with Column A.
I'll assume that you do not want the main workbook changed in any way.
Post back if you can add more. HTH Otto

 

[Otto Moehrbach]

Look at this macro and see if it works for you. As written, this macro
assumes that there is a file named for each manufacturer, i.e. "Buick.xls",
and that each such file is open. This macro also assumes that each file has
a sheet named "The Sheet" and the data will be placed into that sheet. This
macro must be placed in the main file and the main file must be the active
file when this macro is run.
Sub CopyData()
Dim TheWB As Workbook
Dim rColA As Range
Dim i As Range
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
For Each i In rColA
Set TheWB = Workbooks(i.Value & ".xls")
With TheWB.Sheets("The Sheet")
i.Offset(, 1).Resize(, 9).Copy
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
xlPasteValues
End With
Next i
Application.ScreenUpdating = True
MsgBox "Task is done."
End Sub

 

[EB21]

I will try that thank you.

Look at this macro and see if it works for you. As written, this macro
assumes that there is a file named for each manufacturer, i.e. "Buick.xls",
and that each such file is open. This macro also assumes that each file has
a sheet named "The Sheet" and the data will be placed into that sheet. This
macro must be placed in the main file and the main file must be the active
file when this macro is run.
Sub CopyData()
Dim TheWB As Workbook
Dim rColA As Range
Dim i As Range
Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
For Each i In rColA
Set TheWB = Workbooks(i.Value & ".xls")
With TheWB.Sheets("The Sheet")
i.Offset(, 1).Resize(, 9).Copy
.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial
xlPasteValues
End With
Next i
Application.ScreenUpdating = True
MsgBox "Task is done."
End Sub