Hi All
I have posted here a group of VBA codes which I would like to modify to expand the functionality and consolidate into one single code. What I want it to do:
1. Open a file and copy the entire table of data after it has been filtered with columns and rows hidden. But the code below only copies a certain range instead of the entire table.
Sub wbCopyFrom()
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
'-------------------------------------------------------------
'Open file with data to be copied
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If
'--------------------------------------------------------------
'Copy Range
wsCopyFrom.Range("B6:E12").Copy
wsCopyTo.Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
2. So before the copying and pasting, I want the VBA to hide some rows and columns:
Sub HideColsnRows()
'
' HideColsnRows Macro
' Hide unwanted columns and rows for billing
'
'
Rows("1:3").Select
Selection.EntireRow.Hidden = True
Columns("A:A").Select
Range("A4").Activate
Selection.EntireColumn.Hidden = True
Columns("G:G").Select
Range("G4").Activate
Selection.EntireColumn.Hidden = True
Columns("I:Q").Select
Range("I4").Activate
Selection.EntireColumn.Hidden = True
Columns("T:W").Select
Range("T4").Activate
Selection.EntireColumn.Hidden = True
Columns("AF:AF").Select
Range("AF4").Activate
Selection.EntireColumn.Hidden = True
Columns("AI:AL").Select
Range("AI4").Activate
Selection.EntireColumn.Hidden = True
Columns("AM:AP").Select
Range("AM4").Activate
Selection.EntireColumn.Hidden = True
Columns("AQ:AQ").Select
Range("AQ4").Activate
Selection.EntireColumn.Hidden = True
End Sub
3. Then I want it to filter data certain data before pasting:
Sub FilterInPoDirectCPH()
'
' FilterInPoDirectCPH Macro
' Filter Inside Port direct & indirect with MDT CPH
'
'
ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=3, Criteria1 _
:="=Inside Port Direct", Operator:=xlOr, Criteria2:= _
"=Inside Port Indirect"
ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=8, Criteria1 _
:="MDT CPH"
End Sub
Can anybody help me with this? I would gratefully appreciate any help in this and thank you in advance.
I have posted here a group of VBA codes which I would like to modify to expand the functionality and consolidate into one single code. What I want it to do:
1. Open a file and copy the entire table of data after it has been filtered with columns and rows hidden. But the code below only copies a certain range instead of the entire table.
Sub wbCopyFrom()
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
'-------------------------------------------------------------
'Open file with data to be copied
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets(1)
End If
'--------------------------------------------------------------
'Copy Range
wsCopyFrom.Range("B6:E12").Copy
wsCopyTo.Range("A1").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
2. So before the copying and pasting, I want the VBA to hide some rows and columns:
Sub HideColsnRows()
'
' HideColsnRows Macro
' Hide unwanted columns and rows for billing
'
'
Rows("1:3").Select
Selection.EntireRow.Hidden = True
Columns("A:A").Select
Range("A4").Activate
Selection.EntireColumn.Hidden = True
Columns("G:G").Select
Range("G4").Activate
Selection.EntireColumn.Hidden = True
Columns("I:Q").Select
Range("I4").Activate
Selection.EntireColumn.Hidden = True
Columns("T:W").Select
Range("T4").Activate
Selection.EntireColumn.Hidden = True
Columns("AF:AF").Select
Range("AF4").Activate
Selection.EntireColumn.Hidden = True
Columns("AI:AL").Select
Range("AI4").Activate
Selection.EntireColumn.Hidden = True
Columns("AM:AP").Select
Range("AM4").Activate
Selection.EntireColumn.Hidden = True
Columns("AQ:AQ").Select
Range("AQ4").Activate
Selection.EntireColumn.Hidden = True
End Sub
3. Then I want it to filter data certain data before pasting:
Sub FilterInPoDirectCPH()
'
' FilterInPoDirectCPH Macro
' Filter Inside Port direct & indirect with MDT CPH
'
'
ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=3, Criteria1 _
:="=Inside Port Direct", Operator:=xlOr, Criteria2:= _
"=Inside Port Indirect"
ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=8, Criteria1 _
:="MDT CPH"
End Sub
Can anybody help me with this? I would gratefully appreciate any help in this and thank you in advance.