asked on

Edit macro - copy sheet to another workbook

Hi

Currently the macro below will, copy the active sheet from a workbook to a new workbook and will then open a 'save as' window to save the new workbook.

I would like to edit the macro so that rather than copying the active sheet to a new workbook, the sheet is instead copied and added to the workbook C:\Users\RobMarr\Google Drive\Supplier Meetings status.xlsm

Reminders
It is important the active sheet is copied and not removed from the original workbook. This will be a repeat process so I will be adding copysheets to the supplier meetings status on a daily basis.

If someone could edit the code for me that would be great.

Sub Macro()

ActiveSheet.Copy
Res = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xlsx), *.xlsx")
If Res <> 0 Then ActiveWorkbook.SaveAs Res
End Sub

Thanks
Mike

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikes6058

ASKER

Thanks this worked,

I didn't want to close the destination work book so I've removed the final line. I've also changed the file path to leave the code below.

I need to add some additional steps to this macro, I've opened a new question, see link below

https://www.experts-exchange.com/questions/28693226/Macro-change-work-sheet-name-create-hyperlink.html

Sub Macro()

Set sh = ActiveSheet
Set DestWbk = Workbooks.Open("P:\Supplier Relations\Supplier Meetings status.xlsm")
sh.Copy After:=DestWbk.Sheets(DestWbk.Sheets.Count)
End Sub