Excel Macro To Create New Worksheet

[Closed]
Report
Posts
3
Registration date
Wednesday April 24, 2013
Status
Member
Last seen
May 1, 2013
-
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
-
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

7 replies

Posts
3
Registration date
Wednesday April 24, 2013
Status
Member
Last seen
May 1, 2013
1
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
45
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.
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
45
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.
Posts
3
Registration date
Wednesday April 24, 2013
Status
Member
Last seen
May 1, 2013
1
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
45
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.
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
Posts
2368
Registration date
Sunday September 23, 2012
Status
Member
Last seen
December 13, 2018
45
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.