I'm writing a macro which will autofilter, copy and paste data
depending on values entered into certain cells

e.g.
cell A1, named 'lastrow', has the last row number of the available
data e.g. 468
cell A2 has the last column e.g. AM
cell A3 named 'mydatarange', contains a text string built using
concatenate e.g. $A$1:$AM:$468

in the macro, it should run the autofilter as below (illustrated with
fixed range data)

Sheets("MyRawData").Select
Range("A1").Select
ActiveSheet.Range("$A$1:$AM:$468").AutoFilter Field:=29,
Criteria1:= _
"pending"
Rows("1:465").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pending"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


What I'm aiming for however is something like the below

Sheets("MyRawData").Select
Range("A1").Select
ActiveSheet.Range("mydatarange").AutoFilter Field:=29, Criteria1:=
_
"pending"
Rows("1:" & lastrow).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pending"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Hoping it's just a syntax knowledge shortfall....

Is this what you are looking for?

Range("A3").Name = "mydatarange"
Range("mydatarange") = _
Range("$A$1:" & Range("A2").Value & Range("A1").Value)

Which should come out to
Range("mydatarange") = Range("A1:AM468")

"Brotherharry" wrote:

I'm writing a macro which will autofilter, copy and paste data
depending on values entered into certain cells

e.g.
cell A1, named 'lastrow', has the last row number of the available
data e.g. 468
cell A2 has the last column e.g. AM
cell A3 named 'mydatarange', contains a text string built using
concatenate e.g. $A$1:$AM:$468

in the macro, it should run the autofilter as below (illustrated with
fixed range data)

Sheets("MyRawData").Select
Range("A1").Select
ActiveSheet.Range("$A$1:$AM:$468").AutoFilter Field:=29,
Criteria1:= _
"pending"
Rows("1:465").Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pending"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


What I'm aiming for however is something like the below

Sheets("MyRawData").Select
Range("A1").Select
ActiveSheet.Range("mydatarange").AutoFilter Field:=29, Criteria1:=
_
"pending"
Rows("1:" & lastrow).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Pending"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Hoping it's just a syntax knowledge shortfall....