Copy from one workbook to another workbook

V

vonryan

New member
Joined
Feb 4, 2021
Messages
10
Reaction score
0
Points
0
Excel Version(s)
365
Hello everyone,

I know this will be a very basic question but I cannot figure out the issue.

I have two workbooks and I am trying to copy a work sheet ("2021-02-02") from Book1 ("Compare Data.xlsm") to another workbook ("Archive.xlsx") after the only sheet ("Main Sheet"). All the VBA is in Book1 ("Compare Data.xlsm")


An extract of the code is as follows:

Code: 
Application.ScreenUpdating = False
Sheets("2021-02-02").Select
Sheets("2021-02-02").Copy After:=Workbooks("C:\Datasheets Compare\Archive.xlsx").Sheets("Main Sheet")
Application.ScreenUpdating = True

I have tried dimensioning the workbooks and no success.

I have ensured that the Marco Security for the folder is enabled.

Any assistance would be grateful.

Regards

Vonryan
 
Last edited by a moderator:
You can try this.
This assumes that the workbook where you copy INTO is closed.
Code: 
Sub Copy_Sheet_Into_Closed_Workbook()
    Dim wb1 As Workbook, wb2 As Workbook
    Application.ScreenUpdating = False
    Set wb1 = ActiveWorkbook
        Set wb2 = Workbooks.Open("C:\Datasheets Compare\Archive.xlsx")
            If Not wb2.Sheets("2021-02-02") Is Nothing Then wb2.Sheets("2021-02-02").Delete
            wb1.Sheets("2021-02-02").Copy After:=wb2.Sheets("Main Sheet")
        wb2.Close True
    Application.ScreenUpdating = True
End Sub
 
Dear Jolivanes,

Many thanks for your response.

I have an update on this topic.

I got fed up with everything I tried on my company laptop and copied the files to a USB SSD and tried the same code on my personal computer.

It worked first time without any errors.

Even though I have enabled Macro Security on the company laptop it still refuses to work correctly. I will have to ask the IT guy.

Regarding your code. Once again thank you very much. You have given me an insight into checking if the sheet already exists in the Archive file prior to copying with the statement:

If Not wb2.Sheets("2021-02-02") Is Nothing Then wb2.Sheets("2021-02-02").Delete

Great help.

If you have any ideas on the company laptop settings I would be grateful

Best Regards

Vonryan
 
Replace this single line
Code: 
If Not wb2.Sheets("2021-02-02") Is Nothing Then wb2.Sheets("2021-02-02").Delete
with these 5 lines
Code: 
On Error Resume Next
    Application.DisplayAlerts = False
    wb2.Sheets("2021-02-02").Delete
    Application.DisplayAlerts = True
On Error GoTo 0
 
Dear Jolivanes,

Thank you for the further help.

Best Regards

vonryan:smile::smile:
 
You must log in or register to reply here.
Back
Top