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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,113,775
Messages
5,544,148
Members
410,595
Latest member
Tatum2020
Top