Firstly I am something of a novice at Macros – I have to use the recorder to create them as writing VBA is a bit of a mystery. Please bear this in mind if you choose to reply to my query.
My macro operates a series of steps which start by applying a filter to a data input sheet.
It takes a number from a cell on a different sheet and uses that in the filter.
My problem is that the ‘recorder’ has recorded the cell (E2) as the one to copy. It currently (at the time of recording the macro) contained “003”. The following week’s data means that ‘E2’ may contain a different number eg “010”. But when the macro runs it still ‘sees’ “003”.
It then goes onto read cell “F2” on the same sheet and has the same problem with the contents “003Wk17”
I need the macro to recognise the contents of the cell at the time the macro runs rather than staying with what was in the cell at the time of the recording the Macro.
I have included my coding to help
Any help will be gratefully received.
Sub Macro1()
'
' Macro1 Macro
'
' Range("E2").Select
Selection.Copy
Sheets("Report Data").Select
ActiveSheet.Range("$A$1:$P$61").AutoFilter Field:=3, Criteria1:="=003", _
Operator:=xlAnd
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=ActiveSheet
Sheets("Sheet1").Select
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Sheets("File Namer").Select
Range("F2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "003WK17"
Sheets("003WK17").Select
Application.CutCopyMode = False
Sheets("003WK17").Move
Sheets("003WK17").Select
Sheets("003WK17").Name = "003WK17"
ChDir "E:\A Files Today\New folder\Macro Problem"
ActiveWorkbook.SaveAs Filename:= _
"E:\A Files Today\New folder\Macro Problem\003WK17.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End Sub