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