How Can I Re-Write this VBA Macro to be More Efficient? (Copy-Paste Range)-CodePudding

I currently have a full code written to copy the output of one spreadsheet, into certain columns of another spreadsheet. This is part of a project at work, but the VBA codes left to me from an employee that resigned, don't apply well. It's pretty simple in theory.

What I want it to do is pull the value in BB183 from the tab 737-10_1b28_routes in the file 737-10_1b28_routes.csv, and paste it in the tab 737-10 Scenario 1 of file Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx in box L30.

I then want the code to take BB184, and place it in L32. I need the code to skip a line because I want to paste different data in the other line (BB697 goes into to L31 with the same repeating pattern for BB ( 1) and L ( 2). I think once I have a more efficient code, I could figure out the final solution, but need some help. I'm currently running into procedure too large.

I feel like it's j=j 2 from j=30:688 for the L column and BB is like i=i 1 from i=183:512.

Then the second part of the code is j=j 2 from j=31:689 for the L column and BB is like i=i 1 from i=697:1026.

Please see code two to see how it's altered.

Sub vba_copy_data_GCD() 

Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes").Range("BB183").Copy _
Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1").Range("L30")

Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes").Range("BB184").Copy _
Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1").Range("L32")

Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes").Range("BB185").Copy _
Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1").Range("L34")

Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes").Range("BB186").Copy _
Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1").Range("L36")

Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes").Range("BB697").Copy _
Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1").Range("L31")

Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes").Range("BB698").Copy _
Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1").Range("L33")

Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes").Range("BB699").Copy _Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1").Range("L35")

End Sub

CodePudding user response：

Given how sure I am of what you want, please make sure your files are backed-up before trying this:

Sub vba_copy_data_GCD()
    Dim srcWS as Worksheet
    Dim destWS as Worksheet
    Dim i as Long, j as Long

    Set srcWS = Workbooks("737-10_1b28_routes.csv").Worksheets("737-10_1b28_routes")
    Set destWS = Workbooks("Aero Sales Support Modified Att.1 Performance Data Attachment and Fill in Form_20220402.xlsx").Worksheets("737-10 Scenario 1")

    For i = 0 to 329 Step 2
        With destWS
            .Range("L30").Offset(i,0).Value2 = srcWS.Range("BB183").Offset(j,0).Value2
            .Range("L31").Offset(i,0).Value2 = srcWS.Range("BB697").Offset(j,0).Value2
        End With
        j = j   1
    Next i
End Sub