VBA Copy worksheet to another workbook

[wilkisa]

I am trying to copy a worksheet called "Survey Extract Agents" from one workbook into another called "Raw Test Data". "Raw Test Data" is also the name of the sheet in the workbook of the same name. I want the "Survey Extract Agents" to be inserted after the "Raw Test Data" sheet. My macro keeps choking on it. Can someone look at this and tell me what I'm doing wrong, please?

Sub InsertSurveyData()
'
' InsertSurveyData Macro
' Insert a new Column C then make a copy of Sheet1 from file
' w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx and place it at
' the end of the file w:\test stuff\help desk projects\doe, john\Raw Test Data.xlsx;
' rename Sheet1 to Survey Data. Insert the VLookup formula.
'

'
Range("c1").Select
Selection.EntireColumn.Insert
Workbooks.Open ("w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx")
Sheets("Sheet1").Select

***THIS IS WHERE IT KEEPS FAILING***

Sheets("Sheet1").Copy After:=Workbooks("w:\test stuff\help desk projects\doe, john\Raw Test Data.xlsm").Sheet1


Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Survey Data"
Sheets("Raw Test Data").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Survey Data'!C[-2]:C[-1],2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9"), Type:=xlFillDefault
Range("C2:C9").Select
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9")
Range("C2:C9").Select
End Sub

 

[Norie]

Shirlene

Well the first problem is that you seem to be trying to use the entire path and filename of the workbook you've just opened.

Perhaps you want something like this.

Set wbOpen = Workbooks.Open ("w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx")
 
ThisWorkbook.Sheets("Sheet1").Copy wbOpen (After:=wbOpen.Sheets("Raw Test Data"))

 

[wilkisa]

Thanks, Norie. I knew I was doing something wrong. But, I recorded the macro and this is what it gave me.

However, I ran yours and I am getting a compile error at

(After:=wbOpen.Sheets("Raw Test Data"))

with the (After:= being highlighted. Any ideas here?

 

[wilkisa]

The compile error I get says "named arguments not allowed". I'm not good enough with VBA to understand what needs to be fixed. Can someone please help?

Thanks!
Shirlene

 

[wilkisa]

I haven't heard from anyone. Isn't there someone who can help me?

 

[wilkisa]

Here is my code again. Please, I really need help and I have read everything I can find but nothing is jumping out at me on this. It is failing at (After:=wbOpen.Sheets


Sub InsertSurveyData()
'
' InsertSurveyData Macro
' Insert a new Column C then make a copy of Sheet1 from file
' w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx and place it at
' the end of the file w:\test stuff\help desk projects\doe, john\Raw Test Data.xlsm;
' rename Sheet1 to Survey Data. Insert the VLookup formula.
'

'
Range("c1").Select
Selection.EntireColumn.Insert
Set wbOpen = Workbooks.Open("w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx")
ThisWorkbook.Sheets("Sheet1").Copy wbOpen(After:=wbOpen.Sheets("Raw Test Data"))
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Survey Data"
Sheets("Raw Test Data").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Survey Data'!C[-2]:C[-1],2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9"), Type:=xlFillDefault
Range("C2:C9").Select
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9")
Range("C2:C9").Select
End Sub

 

[RoryA]

Small adjustment to Norie's code:

Sub InsertSurveyData()
'
' InsertSurveyData Macro
' Insert a new Column C then make a copy of Sheet1 from file
' w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx and place it at
' the end of the file w:\test stuff\help desk projects\doe, john\Raw Test Data.xlsm;
' rename Sheet1 to Survey Data. Insert the VLookup formula.
'
'
Dim wbOpen As Workbook
Range("c1").Select
Selection.EntireColumn.Insert
Set wbOpen = Workbooks.Open("w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx")
ThisWorkbook.Sheets("Sheet1").Copy After:=wbOpen.Sheets("Raw Test Data")
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Survey Data"
Sheets("Raw Test Data").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'Survey Data'!C[-2]:C[-1],2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9"), Type:=xlFillDefault
Range("C2:C9").Select
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C9")
Range("C2:C9").Select
End Sub

Regards,
Rory

 

[wilkisa]

Thanks, Rory, but I am still getting an error on that same line of code. This time it says "Run-time error '1004' Method 'Open' of object 'Workbooks' failed."

Can you tell me what to fix?

Thanks,
Shirlene

 

[RoryA]

That error must come on the previous line - is the workbook already opened?

 

[wilkisa]

No, the code opens the file without a problem but then chokes on the next step. What I want it to do is to copy the only sheet in the file that it just opened then paste it into the original sheet at the end. It will be after sheet RAW TEST DATA. This is where it keeps failing.

Any other ideas?