PDA

View Full Version : copying data from different workbooks.



nickm687
04-02-2007, 02:32 AM
Hi everyone,

Was wandering if you could help me on a problem i need solving.

I have four workbooks in a folder, each exactly the same with a table on sheet1. the table is 8 colums wide, starting from column A row 5. the only difference is that the tables will have different data in them and a different amount of rows being used.

I have another workbook (master) which when the macro is run, copies the data from each table from the four workbooks and adds them to sheet1 of the master copy one after another. creating a master table containing all the data from each of the four workboks.

Any help would be gratly appreciated. If you need more information please ask.

Regards
Nick

Simon Lloyd
04-02-2007, 04:07 AM
Try these kb entries!
http://vbaexpress.com/kb/getarticle.php?kb_id=829
http://vbaexpress.com/kb/getarticle.php?kb_id=773

Regards,
Simon

nickm687
04-02-2007, 04:58 AM
thanks for the help. very uselful.

one more question.

i have a variable and a loop

Dim tWS As Worksheet
....
For Each tWS In tWB.Worksheets
.....

but i do not want loop through each worksheet.

how can i assign tWS to just one sheet. e.g. Dashboard.

i tried this but itdidnt work tWS = tWB.Sheets("Dashboard")

Any ideas?

Cheers
Nick

Simon Lloyd
04-02-2007, 05:08 AM
You would either make it a "With" statement or if you have a select few sheets you want to loop through you would create an array like:

Dim Arr
Arr= Array("sheet1", "Sheet2".....etc)
For each tWS in Arr
Regards,
Simon

nickm687
04-02-2007, 07:23 AM
Im still having a bit of trouble editing the code to fit the way i want it.

I am using the KBase article 'Combine All Data From All Worksheets in All Workbooks in a Specified Directory' posted by mvida.

1) Instead of scrolling through every worksheet i only want to use one. ("Dashboard")

2) I want to predefine the range i want copied. (A8:Z14)

Thanks in advance.

Nick

mdmackillop
04-03-2007, 10:32 AM
Using DRJ's KB item. If there are more tha 4 files in your folder, put their names in an array and loop through them.

Sub CombineFiles()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Dim Rng As Range

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
Set Rng = Wkb.Sheets("Dashboard").Cells(8, "A").Resize(7, 26) 'Resize to suit
Rng.Copy Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Wkb.Close False
FileName = Dir()
Loop
Set Wkb = Nothing
Set Rng = Nothing
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub