debschofield
New Member
- Joined
- Oct 1, 2019
- Messages
- 10
HI - I wonder if someone can tell me where I have gone wrong? Thank you 
Sub TestMacro()
' written to test look up of data in raw data spreadsheet and copy/paste to another sheet
' I want to select the data in column AA and filter on only items that map to type 01. Income from the VLOOKUP table
' I then want to copy/paste only the Income items from the GL Raw data worksheet (columns A to U) to the Income Data worksheet
' I think the best approach is to autofilter on column AA which is field 27
With ThisWorkbook.Sheets("GL Raw Data").Select
. I've checked the worksheet name and this is correct
.AutoFilter field:=27, Criteria1:="1. Income"
End With
Sheets("GL Raw Data").Select
Copyvalues.Range ("A:U")
'I think the below command will correctly paste the data
Sheets("Income Data").Select
'***Insert Values
WKS.Range("A1").PasteSpecial xlPasteValues
'Then we need to refresh the Income Pivot, close the income data worksheet and return to the header worksheet
End Sub

Sub TestMacro()
' written to test look up of data in raw data spreadsheet and copy/paste to another sheet
' I want to select the data in column AA and filter on only items that map to type 01. Income from the VLOOKUP table
' I then want to copy/paste only the Income items from the GL Raw data worksheet (columns A to U) to the Income Data worksheet
' I think the best approach is to autofilter on column AA which is field 27
With ThisWorkbook.Sheets("GL Raw Data").Select

.AutoFilter field:=27, Criteria1:="1. Income"
End With
Sheets("GL Raw Data").Select
Copyvalues.Range ("A:U")
'I think the below command will correctly paste the data
Sheets("Income Data").Select
'***Insert Values
WKS.Range("A1").PasteSpecial xlPasteValues
'Then we need to refresh the Income Pivot, close the income data worksheet and return to the header worksheet
End Sub