DanielFran
New Member
- Joined
- Apr 19, 2018
- Messages
- 35
Hi,
My objective is to select a worksheet which has several columns, and I need to filter a specific column for true values; then i need to copy some columns (not all), therefore I am hiding these and paste it into another worksheet.
The recorded macro i have does the job but it sometimes selects the wrong worksheet.
The macro I have is the following:
Sub ActiveItem()
'
' ActiveItem Macro
'
Range("B4:I5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B4").Select
Workbooks.Open Filename:="H:\Client Project\Employee Data.xlsx"
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("F:Y").Select
Selection.EntireColumn.Hidden = True
Columns("AA:AE").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.ListObjects("fEmployee").Range.AutoFilter Field:=4, Criteria1:= _
"TRUE"
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Range("B2:AI6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Data Operations.xlsm").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Windows("Employee Data.xlsx").Activate
ActiveWindow.Close
Range("B2").Select
End Sub
My objective is to select a worksheet which has several columns, and I need to filter a specific column for true values; then i need to copy some columns (not all), therefore I am hiding these and paste it into another worksheet.
The recorded macro i have does the job but it sometimes selects the wrong worksheet.
The macro I have is the following:
Sub ActiveItem()
'
' ActiveItem Macro
'
Range("B4:I5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B4").Select
Workbooks.Open Filename:="H:\Client Project\Employee Data.xlsx"
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Columns("F:Y").Select
Selection.EntireColumn.Hidden = True
Columns("AA:AE").Select
Selection.EntireColumn.Hidden = True
ActiveSheet.ListObjects("fEmployee").Range.AutoFilter Field:=4, Criteria1:= _
"TRUE"
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
Range("B2:AI6").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Data Operations.xlsm").Activate
Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B2").Select
Windows("Employee Data.xlsx").Activate
ActiveWindow.Close
Range("B2").Select
End Sub