Excel Macro To Create New Worksheet Closed

Question

I have a invoice tracking spreadsheet, which I would like to update to highlight overdue payments in a new worksheet. I would like the spreadsheet to scan the data every time its opened and generate a new worksheet populated with only the overdue invoices (which I can then email to client to chase!)

The Master sheet contains 13 rows of data as follows:

Invoice No./ LR Work Order No./ Property Address/ Works /Cost/ Date invoiced/ date overdue/ days overdue/ PM/ Notes/ Paid: Y / N/ Paid Amount/ Amount Owed

I would like all rows copied into the new worksheet (titled 'outstanding payments), when 'N' is entered into column K

I would be grateful if anyone could help

Thank you

Zohaib R · Answer

Hi vikkitoria,

Please download the sample sheet from the below mentioned link:

http://speedy.sh/Qy9TZ/CreateWorksheet.xlsm

I have created a sample sheet using Microsoft Excel 2010 and added some data to it. I have added a button to the sheet at the top right. When you click the button it will create a new Worksheet titled "outstanding payments" and copy the overdue data to this sheet.

You can edit the macro to suit your needs.

Hope this helps.

vikkitoria · Answer

Hi Zohaib
Thank you for the template you created 

I am probably doing something wrong, however the button isnt creating a new worksheet when I click it after I have entered Y/N in the column K. I am a bit of a Excel novice, so sorry if I am being blind!

Thanks 
Vikkitoria

Zohaib R · Answer

Hi vikkitoria,

No problem, I can help you further. Please check the below mentioned link and download the file again:

http://speedy.sh/nkvSB/CreateWorksheet.xlsm

If the Macro does not work, please disable "Macro Security" in the excel sheet. Please check the below mentioned link to find steps on how to disable "Macro Security":

https://support.microsoft.com/en-us/office/enable-or-disable-macros-in-office-files-12b036fd-d140-4e74-b45e-16fed1a7e5c6?ocmsassetid=ha010031071&correlationid=2d335057-ff3a-458b-bbf8-7c85749cdb96&ui=en-us&rs=en-us&ad=us#BM12

Please reply if you have any further questions.

vikkitoria · Answer

Hi Zohaib
The macro now works, but it is copying all the data from the master sheet. IS there a way to only copy data where there is a 'N' entered into cells in comlumn K?

THanks again for your help

Vikkitoria

Zohaib R · Answer

Hi vikkitoria,

Please edit the macro and replace the following line of code:

ActiveSheet.Range("$A$1:$M$11").AutoFilter Field:=8, Criteria1:="<>"

With:

ActiveSheet.Range("$A$1:$M$12").AutoFilter Field:=11, Criteria1:="N"

To find steps on how to edit a Macro, please go through the following article:

https://support.microsoft.com/en-us/office/edit-a-macro-ed9e8c3d-58fd-47a1-83eb-bdee680376bb?ocmsassetid=hp010014112&correlationid=1a921320-2b1b-4da0-b268-f7d69fcfdec7&ui=en-us&rs=en-us&ad=us

Please revert for clarification.

Vikkitoria · Answer

I haven't yet had a chance to try, but will this work on Excel on a Apple Mac?

Thank you for your continued help Zohaib

Vikkitoria

Zohaib R · Answer

Hi vikkitoria,

Microsoft Excel for Mac OS is a bit different from Excel for Microsoft Windows. Please refer to the below mentioned link for steps on creating Macros in Excel:

https://support.microsoft.com/en-us/help/288117

You can then use the same Macro Code from the earlier uploaded file.

Please revert for clarification.

Excel Macro To Create New Worksheet

7 responses

Similar discussions

Subscribe To Our Newsletter!