Simplifying Code Efficiently

[ExcelFabs]

Hi All,


i have written a code that looks quite repetitive and could probably be structured much easier with a loop / case, however i'm struggling to apply a proper form. I've highlighted the only difference in bold.

Could you kindly have a look at my code below (it's an extract, the code repeats more often) and let me know if it can be arranged more efficiently, or guide me towards the correct approach?

What it does is basically copy a few rows as per the variables from one Worksheet to another Worksheet into the specified Tabs. My question is purely about the "Coding Efficiency" rather than the task itself, as it works just fine


Thanks so much in advance!

Cheers, ExcelFabs


Code:

    'EUR Receivables
    Windows("A Forecasting.xlsm").Activate
    Sheets("[B]Rec EUR fcst[/B]").Select
    Range(Cells(fRow, "I"), Cells(tRow, "I")).Select
    Selection.Copy


    Windows("B Forecasting.xlsb").Activate
    Sheets("OP_EAI_EUR").Select
        
    Cells([B]12[/B], fcFRange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
        
    'EUR Payables
    Windows("A Forecasting.xlsm").Activate
    Sheets("[B]Pay EUR fcst[/B]").Select
    Range(Cells(fRow, "I"), Cells(tRow, "I")).Select
    Selection.Copy
    
    Windows("B Forecasting.xlsb").Activate
    Sheets("OP_EAI_EUR").Select
    
    Cells([B]22[/B], fcFRange).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

 

[Fluff]

How about

Sub ExcelFabs()
   Dim WbkA As Workbook
   Dim WsB As Worksheet
   Dim Ary As Variant
   Dim i As Long
   
   Set WbkA = Workbooks("A Forecasting.xlsm")
   Set WsB = Workbooks("B Forecasting.xlsb").Sheets("OP_EAI_EUR")
   Ary = Array("Rec EUR fcst", 12, "Pay EUR fcst", 22)
   For i = 0 To UBound(Ary) Step 2
      With WbkA.Sheets(Ary(i))
         .Range(.Cells(fRow, "I"), .Cells(tRow, "I")).Copy
         WsB.Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
      End With
   Next i
End Sub

 

[ExcelFabs]

Thanks so much Fluff!

The code works perfectly fine, i'm trying to figure out the pieces and how to extend it in my case.

Only one part i should have mentioned is, that in the B Forecasting Sheet (so your Set WsB), also changes Sheets from OP_EAI_EUR to OP_EAI_USD etc. (different currencies)
How would i implement the change of that Sheet within the loop? Or would that overcomplicate things and i should just re-use the same loop for example with WsC?


Thanks again for the prompt reply! Really appreciated

 

[ExcelFabs]

Thanks so much Fluff!

The code works perfectly fine, i'm trying to figure out the pieces and how to extend it in my case.

Only one part i should have mentioned is, that in the B Forecasting Sheet (so your Set WsB), also changes Sheets from OP_EAI_EUR to OP_EAI_USD etc. (different currencies)
How would i implement the change of that Sheet within the loop? Or would that overcomplicate things and i should just re-use the same loop for example with WsC?


Thanks again for the prompt reply! Really appreciated

I somehow can't Edit this anymore sorry...
But i understood your approach and have applied it to the second WsB as well, here is what i came up with:

   Set WbkA = Workbooks("Forecasting A.xlsm")   
   Set WsB = Workbooks("Forecasting B.xlsb").Sheets("OP_EAI_EUR")
   Set WsC = Workbooks("Forecasting B.xlsb").Sheets("OP_EAI_USD")
   AryWs = Array(WsB, WsC)
   Ary = Array("Rec EUR fcst", 12, "Pay EUR fcst", 22, "Rec USD fcst", 12, "Pay USD fcst", 22)
    For t = 0 To UBound(AryWs) Step 1
        For i = 0 To UBound(Ary) Step 2
            With WbkA.Sheets(Ary(i))
                .Range(.Cells(fRow, "I"), .Cells(tRow, "I")).Copy
                AryWs(t).Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
            End With
        Next i
    Next t

Code works , does it make "sense" from an efficiency point of view?

Thanks!!

 

[Fluff]

If you want to copy the data from the 4 sheets is WbkA to both the WbkB sheets, I'd dot it like

Sub ExcelFabs()
   Dim WbkA As Workbook
   Dim WsB As Worksheet, WsC As Worksheet
   Dim Ary As Variant
   Dim i As Long

   Set WbkA = Workbooks("A Forecasting.xlsm")
   Set WsB = Workbooks("B Forecasting.xlsb").Sheets("OP_EAI_EUR")
   Set WsC = Workbooks("Forecasting B.xlsb").Sheets("OP_EAI_USD")
   Ary = Array("Rec EUR fcst", 12, "Pay EUR fcst", 22, "Rec USD fcst", 12, "Pay USD fcst", 22)
   For i = 0 To UBound(Ary) Step 2
      With WbkA.Sheets(Ary(i))
         .Range(.Cells(fRow, "I"), .Cells(tRow, "I")).Copy
         WsB.Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
         WsC.Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
      End With
   Next i
End Sub

which saves the extra loop

 

[ExcelFabs]

If you want to copy the data from the 4 sheets is WbkA to both the WbkB sheets, I'd dot it like

Sub ExcelFabs()
   Dim WbkA As Workbook
   Dim WsB As Worksheet, WsC As Worksheet
   Dim Ary As Variant
   Dim i As Long

   Set WbkA = Workbooks("A Forecasting.xlsm")
   Set WsB = Workbooks("B Forecasting.xlsb").Sheets("OP_EAI_EUR")
   Set WsC = Workbooks("Forecasting B.xlsb").Sheets("OP_EAI_USD")
   Ary = Array("Rec EUR fcst", 12, "Pay EUR fcst", 22, "Rec USD fcst", 12, "Pay USD fcst", 22)
   For i = 0 To UBound(Ary) Step 2
      With WbkA.Sheets(Ary(i))
         .Range(.Cells(fRow, "I"), .Cells(tRow, "I")).Copy
         WsB.Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
         WsC.Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
      End With
   Next i
End Sub

which saves the extra loop

Thanks again Fluff!

I actually posted a different Code, but i think the internet messed up with my edit & posting in this thread.

Here is what i have now:

   Dim WbkA As Workbook
   Dim WsB, WsC As Worksheet
   Dim Ary As Variant
   Dim i, t As Long
   
   Set WbkA = Workbooks("A Forecasting.xlsm")
   Set WsB = Workbooks("B Forecasting.xlsb").Sheets("OP_EAI_EUR")
   Set WsC = Workbooks("B Forecasting.xlsb").Sheets("OP_EAI_USD")
    AryWs = Array(WsB, WsB, WsC, WsC)
    Ary = Array("Rec EUR fcst", 12, "Pay EUR fcst", 22, "Rec USD fcst", 12, "Pay USD fcst", 22)
    For i = 0 To UBound(Ary) Step 2
        With WbkA.Sheets(Ary(i))
            .Range(.Cells(fRow, "I"), .Cells(tRow, "I")).Copy
            AryWs(t).Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        End With
        t = t + 1
    Next i

From Array Ary only "Rec EUR fcst", 12, "Pay EUR fcst", 22 get copied into WsB,
and then onward "Rec USD fcst", 12, "Pay USD fcst", 22 into WsC.

I believe that creating an Array AryWs with duplicates is not most efficient, but it seems to work. Any ideas?


Thanks so much!!

 

[Fluff]

How about

   Ary = Array("Rec EUR fcst", 12, WsB, "Pay EUR fcst", 22, WsB, "Rec USD fcst", 12, WsC, "Pay USD fcst", 22, WsC)
   For i = 0 To UBound(Ary) Step 3
      With WbkA.Sheets(Ary(i))
         .Range(.Cells(fRow, "I"), .Cells(tRow, "I")).Copy
         Ary(i + 2).Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
      End With
   Next i

 

[ExcelFabs]

How about

   Ary = Array("Rec EUR fcst", 12, WsB, "Pay EUR fcst", 22, WsB, "Rec USD fcst", 12, WsC, "Pay USD fcst", 22, WsC)
   For i = 0 To UBound(Ary) Step 3
      With WbkA.Sheets(Ary(i))
         .Range(.Cells(fRow, "I"), .Cells(tRow, "I")).Copy
         Ary(i + 2).Cells(Ary(i + 1), fcFRange).PasteSpecial Paste:=xlPasteValues, Transpose:=True
      End With
   Next i

Thanks, that looks pretty awesome to me! Very neat


Went from a full page screen page of repetitive code to this awesome loop with arrays, perfect!

Have a great day ahead Fluff, appreciate taking the time for me!

 

[Fluff]

Glad to help & thanks for the feedback