How to write this code in one line

[Snipes00]

Hey All,

I have compiled this code to copy columns of data from one workbook to another, but I wondered if I could write the copying part all in one line. Since the columns aren't all in a row or in order for copying I had to create separate lines of code for it, so if anyone can help I would be very grateful. Thanks.

Option Explicit
Sub Declaration_BusinessActivities_OtherAssets_BaseStations()
Workbooks.Open ("C:\Users\ImaniS\OneDrive - Vodafone Group\Documents\Declaration Template\2022 Renewal Data\01.  Vodafone Information\1.5 Business Activities.xlsx")
    Dim lr1    As Long
    Dim lr2    As Long
    On Error Resume Next
    With Workbooks("1.5 Business Activities.xlsx").Sheets("Sheet1")
        lr1 = .Cells(Rows.Count, 1).End(xlUp).Row 'last used row in sheet Workbook
        .AutoFilterMode = False
        .Range("A1:BY" & lr1).AutoFilter Field:=1, Criteria1:="GR01" 'filter range on EVO Code
        lr2 = Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Cells(Rows.Count, 1).End(xlUp).Row + 1 'first unused row in sheet Declaration
        .Range("A9:A" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B10").PasteSpecial xlPasteAll 'Copy/Paste
        .Range("B9:B" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B11").PasteSpecial xlPasteAll
        .Range("C9:C" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B12").PasteSpecial xlPasteAll
        .Range("F9:F" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B13").PasteSpecial xlPasteAll
        .Range("H9:H" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B14").PasteSpecial xlPasteAll
        .Range("I9:I" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B15").PasteSpecial xlPasteAll
        .Range("J9:J" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B16").PasteSpecial xlPasteAll
        .Range("K9:K" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B19").PasteSpecial xlPasteAll
        .Range("L9:L" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B20").PasteSpecial xlPasteAll
        .Range("M9:M" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B21").PasteSpecial xlPasteAll
        .Range("O9:O" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B22").PasteSpecial xlPasteAll
        .Range("P9:P" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B23").PasteSpecial xlPasteAll
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
Workbooks("1.5 Business Activities.xlsx").Close

Workbooks.Open ("C:\Users\ImaniS\OneDrive - Vodafone Group\Documents\Declaration Template\2022 Renewal Data\03. Property Damage & Business Interruption\3.3 Other Assets Declaration (inc Totals).xlsx")
    With Workbooks("3.3 Other Assets Declaration (inc Totals).xlsx").Sheets("Sheet1")
        .AutoFilterMode = False
        .Range("A1:BY" & lr1).AutoFilter Field:=1, Criteria1:="GR01" 'filter range on EVO Code
        .Range("N9:N" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("A27").PasteSpecial xlPasteValues  'Copy/Paste
        .Range("J9:J" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B27").PasteSpecial xlPasteValues
        .Range("L9:L" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("C27").PasteSpecial xlPasteValues
        .Range("S9:S" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("A36").PasteSpecial xlPasteValues
        .Range("U9:U" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B36").PasteSpecial xlPasteValues
        .Range("W9:W" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("C36").PasteSpecial xlPasteValues
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
Workbooks("3.3 Other Assets Declaration (inc Totals).xlsx").Close

Workbooks.Open ("C:\Users\ImaniS\OneDrive - Vodafone Group\Documents\Declaration Template\2022 Renewal Data\03. Property Damage & Business Interruption\3.2 Base Station Declaration.xlsx")
    With Workbooks("3.2 Base Station Declaration.xlsx").Sheets("Sheet1")
        .AutoFilterMode = False
        .Range("A1:BY" & lr1).AutoFilter Field:=1, Criteria1:="GR01" 'filter range on EVO Code
        .Range("H9:H" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("A32").PasteSpecial xlPasteValues  'Copy/Paste
        .Range("J9:J" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("B32").PasteSpecial xlPasteValues
        .Range("L9:L" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("C32").PasteSpecial xlPasteValues
        .Range("U9:U" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("D32").PasteSpecial xlPasteValues
        .Range("W9:W" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("E32").PasteSpecial xlPasteValues
        .Range("Y9:Y" & lr1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Declaration_Template.xlsm").Sheets("TEMP").Range("F32").PasteSpecial xlPasteValues
        .AutoFilterMode = False
    End With
    Application.CutCopyMode = False
Workbooks("3.2 Base Station Declaration.xlsx").Close
End Sub

 

[Fluff]

Duplicate to: Help with simplifying code

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.