shanks1983
05-16-2007, 04:15 AM
Hi All,
I am still new to VBA and i am having a problem with the below code.
What i need it to do is open all the workbooks in the folder specified and collect all the data from the sheet named "data" and copy the data on row 1 to a documnet called "Survey Results" and paste the info into the sheet named "data"
when i run the code it returns a error "400" can anyone see were i am going wrong and perhaps help me out.
Many thanks
Sub open_workbooks_same_folder()
Dim folder As String
Dim Wb As Workbook, sFile As String
folder = "G:\TEAM HR Central Support BNB Resourcing\Survey Results\"
sFile = Dir(folder & "*.xls")
Do While sFile <> ""
On Error GoTo skip
Set Wb = Workbooks.Open(folder & sFile)
Wb.Activate
Sheets("Data").Select
Range("A2:AZ2").Select
Selection.Copy
Windows("Survey Results.xls").Activate
Sheets("Data").Select
Range("A2").Select
ActiveSheet.Paste
Wb.Close True
skip:
sFile = Dir
Loop
End Sub
I am still new to VBA and i am having a problem with the below code.
What i need it to do is open all the workbooks in the folder specified and collect all the data from the sheet named "data" and copy the data on row 1 to a documnet called "Survey Results" and paste the info into the sheet named "data"
when i run the code it returns a error "400" can anyone see were i am going wrong and perhaps help me out.
Many thanks
Sub open_workbooks_same_folder()
Dim folder As String
Dim Wb As Workbook, sFile As String
folder = "G:\TEAM HR Central Support BNB Resourcing\Survey Results\"
sFile = Dir(folder & "*.xls")
Do While sFile <> ""
On Error GoTo skip
Set Wb = Workbooks.Open(folder & sFile)
Wb.Activate
Sheets("Data").Select
Range("A2:AZ2").Select
Selection.Copy
Windows("Survey Results.xls").Activate
Sheets("Data").Select
Range("A2").Select
ActiveSheet.Paste
Wb.Close True
skip:
sFile = Dir
Loop
End Sub