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

What do you mean you are getting the error on the same line of code?

It's not trying to open anything.

Is this the actual line causing the problem?

Workbooks.Open("w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx")

 

[wilkisa]

I can only tell you what it is showing me. The place in the code where it keeps stopping is on the next line after the Open where the Copy After statement is. At this point, both of my workbooks are open. I want the Sheet1 from file SURVEY EXTRACT AGENTS to be copied after sheet named Raw Test Data in the file also called RAW TEST DATA. I'm not sure that is exactly what the code is trying to do but I just don't know enough to fix it.

Thanks,
Shirlene

 

[RoryA]

Try this version:

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, wksNew As Worksheet, strWbk As String
strWbk = "w:\test stuff\help desk projects\doe, john\Survey Extract Agents.xlsx"
Range("c1").EntireColumn.Insert
Set wbOpen = Workbooks.Open(strWbk)
wbOpen.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set wksNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
wksNew.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

 

[wilkisa]

Yes, Rory, that fixed it. I now how other problems but I think I can figure them out.

Many thanks to you and to Norie for all of the help!!!

Shirlene

 

[MSU]

Hi Shirlene

Please find the shortest code (4 line code) to meet your above requirement.
You need to execute this macro from Raw_Test_Data.xls and you can add additional lines to format this files later.
'********************************************
Sub InsertSurveyData()
'
'NOTEs:
'1. Open Raw_Test_Data.xls / file in which you want to copy sheet.
'2. Paste below code in place of your macro.
'3. here my assumption is you want to copy:
' 3a) "SurveyExtractAgents" work sheet from "Survey Extract Agents.xls" workbook into
' 3b) another workbook called "Raw Test Data.xls" which already contains a worksheet with name "RawTestData"
' 3c) copied workbook has to be renamed as "SurveyData"

'Here:
'sourceworkbook=Survey Extract Agents.xls
'Targetworkbook=Raw_Test_Data.xls
'which already contains worksheet "RawTestData",and New sheet needs has to be copied
'after this worksheet.
'Rename this sheet as "SurveyData"
'============OPERATIONAL CODE STARTS HERE==============
'Opens source file
Workbooks.Open ("d:\test\Survey Extract Agents.xls")

'copy sheet from another workbook before first tab of Book2
Workbooks("Survey Extract Agents.xls").Sheets("SurveyExtractAgentsSheet").Copy After:=Workbooks("Raw_Test_Data.xls").Sheets("RawTestData")
'Sheets("SurveyExtractAgentsSheet").Select
Sheets("SurveyExtractAgentsSheet").Name = "SurveyData"
'=============OPERATIONAL CODE ENDS HERE================
End Sub

'********************************************

Thought above may help to other if it is no more required to sherline.

Regards
MSU

 

[mr.paul.c]

@MSU

I used that code try and solve a similar problem that I have, however when I try to run it I get a 'run time error 9' on the line

Workbooks("Survey Extract Agents.xls").Sheets("SurveyExtractAgentsSheet").Copy After:=Workbooks("Raw_Test_Data.xls").Sheets("RawTestData")

obviously I have changed the filenames etc to match my workbooks, can't figure out why the error is happening

 

[RoryA]

That would imply that one of the names (workbook or sheet is incorrect in some way) or that one of the workbooks is not open.

 

[mr.paul.c]

my bad. I had another look through and realised where the problem was, it now works as intended :D