Macro Highlights All Worksheets after Running

tsymic

New Member
Joined
Oct 1, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
I thought this was a minor issue, but if you don't notice it, it changes cells on the other tabs and messes up the workbook. I've already lost most of my formulas.

Why is it doing this? Here is the macro. I've tried changing ActiveWorkbook.SaveAs to ActiveSheet.SaveAs but that didn't work.

Sub NextInvoice()
' Keyboard Shortcut: Ctrl+q

Worksheets.PrintOut _
From:=1, _
To:=1, _
Copies:=1, _
ActivePrinter:="SHARP MX-M365N PCL6"

Dim NewFN As Variant
' Copy Invoice to new workbook
ActiveSheet.Copy
NewFN = "C:\Users\anviladmin\anvilmachine.ca\Document Portal - Documents\Work Orders\" & Range("P6").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Range("P6").Value = Range("P6").Value + 1
Range("J9, J11, O11:Q11, J12, J15:Q16, J17:Q17, J18:Q28").ClearContents
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
The lines of code below ActiveWorkbook.Close are affecting the workbook that you copied the sheet from. If you wanted to clear those cells in the copied version, they need to copied above the CLOSE statement. As far as changing many of the other sheets, there's nothing in this macro that is selecting many sheets. Is there another macro that is running before this?

VBA Code:
Sub NextInvoice()
' Keyboard Shortcut: Ctrl+q

Worksheets.PrintOut _
From:=1, _
To:=1, _
Copies:=1, _
ActivePrinter:="SHARP MX-M365N PCL6"

Dim NewFN As Variant
' Copy Invoice to new workbook
ActiveSheet.Copy
NewFN = "C:\Users\anviladmin\anvilmachine.ca\Document Portal - Documents\Work Orders\" & Range("P6").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Range("P6").Value = Range("P6").Value + 1
Range("J9, J11, O11:Q11, J12, J15:Q16, J17:Q17, J18:Q28").ClearContents
End Sub
 
Upvote 0
The clear contents works just fine. I need a copy saved and the original cleared.

There are no other macros running. I've checked many times. That's why I'm so confused.
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top