Open workbook, copy data, save and close error

[Jessica.Bush]

Hi there,

I hope someone may be able to help me please?

I have a workbook (workbook A) with lots of data in.
A second workbook (workbook B) is to be found in a location which doesn't change, opened, some sheets copied over and for it to be saved as a copy in the same location as workbook A. I have a code to do this and it seemed to work perfectly.

However I have added a third workbook, workbook C which I want exactly the same thing to happen as with workbook B. The data copied will be slightly different but the process is the same.

When I use the same code for workbook C I don't get a VBA error but it says the excel needs to close and about an error report - could anyone help me with the reason why please and how I can fix it?

My current code is below. Many thanks.

Sub Proceed()

Dim newpathway As String

Application.ScreenUpdating = False

With Sheets("Sheet3")
    .Cells.Copy
    .Cells.PasteSpecial Paste:=xlValues
End With

On Error Resume Next
ActiveWorkbook.Names("Name1").Delete
ActiveWorkbook.Names("Name2").Delete
ActiveWorkbook.Names("Name3").Delete
On Error GoTo 0

newpathway = ThisWorkbook.Path

WorkbookB

Workbooks("WorkbookA.xlsm").Activate
newpathway = ThisWorkbook.Path

WorkbookC
Workbooks("WorkbookA.xlsm").Activate

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Sheet5").Delete
On Error GoTo 0
Application.DisplayAlerts = True

MsgBox ("Please continue in the new workbooks")
Exit Sub

Application.ScreenUpdating = True

End Sub

Sub WorkbookB()

Dim excelfile As String
Dim newpathway As String

excelfile = "WorkbookB.xlsm"
Workbooks.Open "C:\My Documents" & excelfile

Workbooks("WorkbookA.xlsm").Activate
Sheets(Array("Sheet1", "Sheet3")).Copy After:=Workbooks("WorkbookB.xlsm").Sheets("Front Page")

Workbooks("WorkbookB.xlsm").Activate

Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=newpathway & ActiveWorkbook.Name
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

Sub WorkbookC()

Dim excelfile As String
Dim newpathway As String

excelfile = "WorkbookC.xlsm"
Workbooks.Open "C:\My Documents\" & excelfile

Workbooks("WorkbookA.xlsm").Activate
Sheets(Array("Sheet2", "Sheets3", "Sheet4", "Information")).Copy After:=Workbooks("WorkbookC.xlsm").Sheets("Front Page")

Workbooks("WorkbookC.xlsm").Activate

Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=newpathway & ActiveWorkbook.Name
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

[dangelor]

Possibly this line of code is the problem...

    ActiveWorkbook.SaveCopyAs Filename:=newpathway & ActiveWorkbook.Name

The string "newpathway" is never assigned a value, so the SaveCopyAs method is saving a duplicate file with the same name in the same directory as the original file?

[Jessica.Bush]

Hi dangelor,

Thanks for your reply.

newpathway = thisworkbook.path

It worked when I only wanted workbookB fine and would make a copy and save it in the same location as workbookA from which the macro was being run... do you have any other suggestions that I could try please?

Many thanks.

[dangelor]

Here is your code, unchanged except for my comments on possible problems.

I ran it on my system and in only generated a VBA error on the misspelling of Sheet3 in the WorkbookC subroutine.

About the newpathway string... if you are not changing the path to save the files in to a new location, why include it? and why use the SaveCopyAs method? As written, your code overwrites the existing files.

One other possibility could be a corrupted workbook.

HTH -Rich

Sub Proceed()

Dim newpathway As String

Application.ScreenUpdating = False

With Sheets("Sheet3")
    .Cells.Copy
    .Cells.PasteSpecial Paste:=xlValues
End With

On Error Resume Next
ActiveWorkbook.Names("Name1").Delete
ActiveWorkbook.Names("Name2").Delete
ActiveWorkbook.Names("Name3").Delete
On Error GoTo 0

newpathway = ThisWorkbook.Path 'NOT USED. DIMENSIONED FOR THIS SUBROUTINE ONLY

WorkbookB

Workbooks("WorkbookA.xlsm").Activate
newpathway = ThisWorkbook.Path 'AGAIN, NOT USED IN THIS SUBROUTINE

WorkbookC
Workbooks("WorkbookA.xlsm").Activate

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Sheet5").Delete
On Error GoTo 0
Application.DisplayAlerts = True

MsgBox ("Please continue in the new workbooks")
Exit Sub ' PREVENTS THE LINE BELOW FROM EXECUTING

Application.ScreenUpdating = True

End Sub

Sub WorkbookB()

Dim excelfile As String
Dim newpathway As String 'NOT USED IN SUBROUTINE

excelfile = "WorkbookB.xlsm"
Workbooks.Open "C:\My Documents" & excelfile 'MISSING \

Workbooks("WorkbookA.xlsm").Activate
Sheets(Array("Sheet1", "Sheet3")).Copy After:=Workbooks("WorkbookB.xlsm").Sheets("Front Page")

Workbooks("WorkbookB.xlsm").Activate

Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=newpathway & ActiveWorkbook.Name
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

Sub WorkbookC()

Dim excelfile As String
Dim newpathway As String

excelfile = "WorkbookC.xlsm"
Workbooks.Open "C:\My Documents\" & excelfile

Workbooks("WorkbookA.xlsm").Activate
Sheets(Array("Sheet2", "Sheets3", "Sheet4", "Information")).Copy After:=Workbooks("WorkbookC.xlsm").Sheets("Front Page") 'POSSIBLE MISSPELLING OF "Sheet3"?

Workbooks("WorkbookC.xlsm").Activate

Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:=newpathway & ActiveWorkbook.Name 'newpathway = NOTHING, NEVER ASSIGNED A VALUE
ActiveWorkbook.Close
Application.DisplayAlerts = True

End Sub

[Jessica.Bush]

Hi Rich,

Thanks so much for your help with this.

The workbooks B and C are saved on the system as a 'template', always in the same place. The user has to save workbook A in a folder specific to the client being worked on. I want it to not overwrite the template but to save as a copy in the same folder as in A which is the workbook from where the code is run. This did work with just workbook B but not when I added workbook C.

There may well be spelling mistakes, the original code had some confidential names and things in which I probably shouldn't publish so quickly pasted over them.

I will look over your comments now and let you know whether it is solved or not.

Thanks!

[Jessica.Bush]

Hi Rich,

Well I played with names, swapping workbooks round etc. and it now works. It must have been a corrupted file as you said. I copied it all into a new workbook and used this as workbookC and suddenly it works!

Sorry if I wasted your time but many thanks for your time and help, I do appreciate it and would have never have thought of a corrupted file if you hadn't mentioned it!

Jessica

[dangelor]

Glad to help. Enjoy the day!