Using VBA to Copy Paste onto another Workbook

ameenuksg

Board Regular
Joined
Jul 11, 2017
Messages
79
Hi I am trying to copy paste filtered data using VBA onto another workbook under 4 separate sheets but it doesnt seem to work

Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.Offset(2703, 1).Range("A1").Select
    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"
    ActiveCell.Offset(-2703, -2).Range("A1:AQ2801").Select
    ActiveCell.Offset(-2703, -1).Range("A1").Activate
    Selection.Copy
    Windows("Domestic MMM YYYY Invoice Summary Week.xlsm").Activate
    ActiveSheet.Paste
    ActiveSheet.Next.Select
    Windows( _
        "Copy of SGL TO MDT Jun 2018 DOMESTIC SIN KPI STATISTICS 20 Jun 2018 week 24.xlsm" _
        ).Activate
    ActiveWindow.SmallScroll Down:=-15
    ActiveCell.Offset(2703, 6).Range("A1").Select
    ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=8, Criteria1 _
        :="MDT FRH"
    ActiveCell.Offset(-2703, -7).Range("A1:AQ2801").Select
    ActiveCell.Offset(-2703, -6).Range("A1").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Domestic MMM YYYY Invoice Summary Week.xlsm").Activate
    ActiveSheet.Paste
    Windows( _
        "Copy of SGL TO MDT Jun 2018 DOMESTIC SIN KPI STATISTICS 20 Jun 2018 week 24.xlsm" _
        ).Activate
    Application.CutCopyMode = False
    ActiveCell.Offset(2706, 6).Range("A1").Select
    ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=8
    ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=3, Criteria1 _
        :="Outside port"
    ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=8, Criteria1 _
        :="MDT CPH"
    ActiveCell.Offset(-2706, -7).Range("A1:AQ2801").Select
    ActiveCell.Offset(-2706, -6).Range("A1").Activate
    Selection.Copy
    Windows("Domestic MMM YYYY Invoice Summary Week.xlsm").Activate
    ActiveSheet.Next.Select
    ActiveSheet.Paste
    ActiveSheet.Next.Select
    Windows( _
        "Copy of SGL TO MDT Jun 2018 DOMESTIC SIN KPI STATISTICS 20 Jun 2018 week 24.xlsm" _
        ).Activate
    ActiveWindow.SmallScroll Down:=-48
    ActiveCell.Offset(2709, 6).Range("A1").Select
    ActiveSheet.ListObjects("Table132415").Range.AutoFilter Field:=8, Criteria1 _
        :="MDT FRH"
    ActiveCell.Offset(-2709, -7).Range("A1:AQ2801").Select
    ActiveCell.Offset(-2709, -6).Range("A1").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Domestic MMM YYYY Invoice Summary Week.xlsm").Activate
    ActiveSheet.Paste
    Sheets("InsidePort CPH w").Select
    Windows( _
        "Copy of SGL TO MDT Jun 2018 DOMESTIC SIN KPI STATISTICS 20 Jun 2018 week 24.xlsm" _
        ).Activate
    Application.CutCopyMode = False
End Sub


Any help would be grateful
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,234
Members
409,857
Latest member
KailuaTown
Top