Simplifying Code Efficiently

ExcelFabs

New Member
Joined
Oct 9, 2018
Messages
9
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:
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
How about
Code:
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
 
Upvote 0
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 :)
 
Last edited:
Upvote 0
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:

Code:
   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 :eek:, does it make "sense" from an efficiency point of view?

Thanks!!
 
Upvote 0
If you want to copy the data from the 4 sheets is WbkA to both the WbkB sheets, I'd dot it like
Code:
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
 
Upvote 0
If you want to copy the data from the 4 sheets is WbkA to both the WbkB sheets, I'd dot it like
Code:
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:

Code:
   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!!
 
Upvote 0
How about
Code:
   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
 
Upvote 0
How about
Code:
   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!
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top