hitch_hiker
Active Member
- Joined
- Feb 21, 2012
- Messages
- 294
I am trying to do a autofilter using an input box. the sheet has filters assigned to each column and one column is "permamently" set at only show blanks.
this is a invoice register filtered by blank in the paid column, this gives me only invoices which have not been paid. The macro is written by using the macro recorder and taking the working section and pasting into the working work in progress, so the range is set to a1 to p11853 the results I get start at row 11843 which has no data in it.
My intention is to get a list of "customer A" with unpaid invoices regardless of age and copy them into a formatted sheet to act as a statement. Then "customer B", "customer C" etc
I have seen comments re removing the pre-existing filters first, this is not the preferred option, I use the sheet with the filters on every day and I have changed the range to c1:c11853 with no effect. Any suggestions for how to move ahead with this.
I test the code at each line and only go to the next line (or group of lines) once it works. I am also rem ing the hell out of it so I can follow it later on
this is a invoice register filtered by blank in the paid column, this gives me only invoices which have not been paid. The macro is written by using the macro recorder and taking the working section and pasting into the working work in progress, so the range is set to a1 to p11853 the results I get start at row 11843 which has no data in it.
My intention is to get a list of "customer A" with unpaid invoices regardless of age and copy them into a formatted sheet to act as a statement. Then "customer B", "customer C" etc
I have seen comments re removing the pre-existing filters first, this is not the preferred option, I use the sheet with the filters on every day and I have changed the range to c1:c11853 with no effect. Any suggestions for how to move ahead with this.
I test the code at each line and only go to the next line (or group of lines) once it works. I am also rem ing the hell out of it so I can follow it later on
VBA Code:
Sub createStatement()
'
' CreateStatement Macro
'
' Keyboard Shortcut: Ctrl+j
'
Rem FROM SHEET "inv reg"
Dim trade As String ' customer name for search, comes from inputbox
Rem rename workbook as "invoice sample test", accept duplicate name warning
Application.DisplayAlerts = False 'disable "file already exists"
ActiveWorkbook.SaveAs FileName:= _
"C: \[PATH] invoice sample test work in progress.xlsm" _ ' *** [path] replaces the existing path for security
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False ' %%% remove "work in progress" when it all works
Application.DisplayAlerts = True ' re-activate "file already exists"
Rem make sure "inv reg" is the active sheet
Sheets("inv reg").Select
Columns("C:C").Select
Rem SEARCH FOR (ENTERED SEARCH CRITERIA as user input)
trade = InputBox("enter customer name or part name the more unique the better", "", "Type Here") ' customer name to search
ActiveSheet.Range("c1:c118542").AutoFilter Field:=3, Criteria1:=trade