Open up multiple workbooks to obtain data with vba

[renderman7]

Hi All,

I am attempting to get some code working in excel, where it will open up a series of workbooks, copy the data from a sheet out, then paste it onto the main workbook, ideally under the previously pasted data.

I have a list of names for what each workbook is called, so I need some code to go through and open the work book based on value of fname and then fdate, once the copy is done, close the open workbook, without changes and then move onto the next in the list.

Once the list is done, I need a message to appear, saying action complete.

I have this code at the moment, as my starting point, but I can't get it to even recognise the Filename1 part. Any help would be great.

Sub CopyStuff()

Dim wb1 As Workbook, wb2 As Workbook
Dim Filename1 As String

Set wb1 = ActiveWorkbook

fname = Sheets("QA Officer Results").Range("A4").text
fdate = Sheets("QA Officer Results").Range("B2").text

Set Filename1 = fname & " " & fdate
Set wb2 = Workbooks.Open(filename1)

wb2.Sheets(1).Cells.Copy.wb1.Sheets(1).Cells

wb2.Close SaveChanges:=False

Set wb2 = Nothing
Set wb1 = nothing


End Sub

 

[Fluff]

You only Set objects, not strings, the code should be

Filename1 = fname & " " & fdate