VBA Copy & Paste

[VBAMePlease]

Need to create a loop to perform the following:

P26 to E19.

E27 to P29.
D30:D41 to P30:P41.

Q26 to E19.

E27 to Q29.
D30:D41 to Q30:Q41

R26 to E19.

E27 to R29.
D30:D41 to R30:R41

...

AN27 to E19.

E27 to AN29.
D30:D41 to AN30:AN41


Column AN is where the loop would end.

 

[Roxxien]

Hello,

Here is what you asked

Sub LoopCopyPaste()
Dim c As Currency


c = 15
Do Until c = 40
    'Change column by 1
    c = c + 1
    'P26 to E19
    Cells(26, c).Copy Cells(19, 5)
    'E27 to P19
    Cells(27, 5).Copy Cells(29, c)
    'D30:41 to P30:41
    Range(Cells(30, 4), Cells(41, 4)).Copy Cells(30, c)
Loop
End Sub

 

[VBAMePlease]

If it makes a difference, E19 is a named range ("MODEL_CALC_YEAR").

Every time E19 changes it drives a model in a different sheet that calculates monthly revenues in cells D30:D41. The values being put into E19 are years.

So, if P26 is 2017, the values in D30:D41 are unique to 2017. When Q26 is put into E19, the values in D30:D41 are unique to 2018, etc.

 

[Roxxien]

Did you try what I previously posted?

 

[VBAMePlease]

Hello,

Here is what you asked

Sub LoopCopyPaste()
Dim c As Currency


c = 15
Do Until c = 40
    'Change column by 1
    c = c + 1
    'P26 to E19
    Cells(26, c).Copy Cells(19, 5)
    'E27 to P19
    Cells(27, 5).Copy Cells(29, c)
    'D30:41 to P30:41
    Range(Cells(30, 4), Cells(41, 4)).Copy Cells(30, c)
Loop
End Sub

Giving me DIV/0's. For whatever reason in E19 it's using D26+1? As I watch it run it'll copy the year value but then converts it to D26+1. Going to adjust and have it post values only.

 

[Roxxien]

Ok so you get some division by 0, do you have all your cells filed from P26 to AN26?

This macro only copy/paste cell so your error of division by 0 shouln't be a problem from the macro but from one of your formula.

 

[VBAMePlease]

E19 = Value

D30= =Blended_Jan
D31= =Blended_Feb
D32= =Blended_Mar
D33= =Blended_Apr
D34= =Blended_May
D35= =Blended_Jun
D36= =Blended_Jul
D37= =Blended_Aug
D38= =Blended_Sep
D39= =Blended_Oct
D40= =Blended_Nov
D41= =Blended_Dec

P26= =Model_Start_Year
Q26= =P$26+1

P30:P41= Blank

The reason I'm getting DIV/0 is because the macro is copy and pasting the formulas. Anyway I can have it perform the entire thing only copying and pasting values?

 

[Roxxien]

If you want the value, we then need to do special paste as value.
Here is how we can do it

Sub LoopCopyPaste()
Dim c As Currency
Application.ScreenUpdating = False
c = 15
Do Until c = 40
    'Change column by 1
    c = c + 1
    'P26 to E19
    Cells(26, c).Copy
    Cells(19, 5).PasteSpecial Paste:=xlPasteValues
    'E27 to P19
    Cells(27, 5).Copy
    Cells(29, c).PasteSpecial Paste:=xlPasteValues
    'D30:41 to P30:41
    Range(Cells(30, 4), Cells(41, 4)).Copy
    Cells(30, c).PasteSpecial Paste:=xlPasteValues
Loop
Application.ScreenUpdating = True
End Sub

 

[VBAMePlease]

If you want the value, we then need to do special paste as value.
Here is how we can do it

Sub LoopCopyPaste()
Dim c As Currency
Application.ScreenUpdating = False
c = 15
Do Until c = 40
    'Change column by 1
    c = c + 1
    'P26 to E19
    Cells(26, c).Copy
    Cells(19, 5).PasteSpecial Paste:=xlPasteValues
    'E27 to P19
    Cells(27, 5).Copy
    Cells(29, c).PasteSpecial Paste:=xlPasteValues
    'D30:41 to P30:41
    Range(Cells(30, 4), Cells(41, 4)).Copy
    Cells(30, c).PasteSpecial Paste:=xlPasteValues
Loop
Application.ScreenUpdating = True
End Sub

Got it working. Awesome. Thanks for the help, much appreciated.