CODEWITHSUNDEEP
vbaexcel
cardoc
cardoc

Reputation: 21

Copying entire sheet from master workbook and pasting in another workbook

I need to copy an entire spreadsheet from master workbook to another workbook (workbook2) everyday. I then need to rename the tab with the current date in workbook 2. I don't want to open workbook 2. I want to automatically hit the macro button on the master workbook to update the other workbook saved in another location.

This is the macro I tried recording

Sub Graph()
    Cells.Select
    Selection.Copy
    ActiveWindow.ActivateNext
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet2").Name = "04 08 2017"
    Cells.Select
    ActiveSheet.Paste
    ActiveWindow.ActivateNext
End Sub

Upvotes: 2

Views: 569

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

As @CLR already mentioned you will need to open a workbook to paste a sheet into it.

Something like the following will work, but note that this is an example only and not a full working solution.

You will still at least need to implement a proper error handling to …

  1. catch errors while opening the workbook and it is protected (because already opened by another user) or wrong file/path.
  2. catch errors while renaming the worksheet, because the name has to be unique. Re-Using an existing name will throw an error.

Not implementing error handling can result in an undetermined condition if an error is caused.

Option Explicit 'first line in your module forces correct variable declare.

Public Sub Graph()
    Application.ScreenUpdating = False 'Disable screenupdating

    'Open destination workbook
    Dim DestWorkbook As Workbook
    Set DestWorkbook = Workbooks.Open(Filename:="C:\YourPathHere\Workbook2.xlsx") 

    With DestWorkbook
        'Copy ActiveSheet
        ThisWorkbook.ActiveSheet.Copy After:=.Sheets(.Sheets.Count)
        'Instead of copying the ActiveSheet I recommend to copy a specific sheet by name
        'ThisWorkbook.Worksheets("YourSourceSheetName").Copy After:=.Sheets(.Sheets.Count)

        'Rename last sheet (the one we inserted above)
        .Sheets(.Sheets.Count).Name = "04 08 2017"

        'Close and save workbook2
        .Close SaveChanges:=True
    End With

    Application.ScreenUpdating = True 'Enable screenupdating
End Sub

Upvotes: 2

Related Questions