Maybe try declaring the FSO as Object. "Dim FSO" without a declaration declares the FSO as a variant. Just a...
Each block is a named range. There are 6 Key colors which are based on the information in column “Y”(Rental...
I cleaned up the code. This will place next month and year in cell A1. So today is Sep30 2018...
This was tested in a blank worksheet (no data). The macro will place next Month and Year in cell A1....
Updated the code to include the possibility of blank cells in the raw data. If a blank cell is found...
Lets try this again. You will need 3 sheets. Sheet1, Sheet 2 and Sheet3 Sheet1 your raw data Sheet2 is...
Hey Thank you so much. Apologies for not been very clear on the question. Using your example, in the output...
To be honest, I not sure I understand your question. Below is my understand of what I think you need....
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.
Not sure if this will help. When you save your file click Tools>General Options and then click on Always create backup. No macro needed to keep a backup of your work.
Save a backup file
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.
Please share using the share button above.
If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.
Found the answer useful and wants to credit the user, then vote the answer (vote up).