I have used the forum and found ‘Copy Data from one Worksheet to Another in Excel VBA’ which worked fantastic, using the below code:
I liked this code as it kept the comments I had inserted on the tabs and copied them to ‘Sheet 2’
What I would like to do, is copy Sheet 1 in exactly the same way, and when running the macro, Save as a new file (acting as a copy).
Can anyone help me please? I only want to save a range of the sheet 1 data, which is why I am not using a macro to save a copy of the sheet.
I did have this code:
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
‘~~> Source/Input Workbook
Set wbI = ThisWorkbook
‘~~> Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets(“Sheet1”)
‘~~> Destination/Output Workbook
Set wbO = Workbooks.Add
‘~~> Set the relevant sheet to where you want to paste
Set wsO = wbO.Sheets(“Sheet1″)
‘~~>. Save the file
.SaveAs Filename:=”C:\Desktop\Customer Copy.xlsm”, FileFormat:=56
‘~~> Copy the range
‘~~> Paste it in say Cell A2. Change as applicable
wsO.Range(“A2”).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
Which saves as a new file which is great, but it looses the inserted comments I had.
Hi Hyside2 Thank you for the comment, this wont work as I only want to copy selected information, which is why I was using the macro,
The reason for this, is that I have a Master file, and then certain information on a Copy which then means when a colleague or myself update the Master it updates the copy also.on January 2, 2018.