VBA Copy and Paste on Multiple Ranges

[davec8723]

Hi All,

I am a extreme novice user in VBA and am trying to write a basic VBA code to effectively remove the formulas from a workbook to reduce the size. I want to save the formulas in one hidden row above the data and have the macro select this row, copy the formulas down to the data, calculate the sheet, and then copy and paste the new calculated info and paste as values

Issues making this more difficult:
1) The number of rows of data is not constant, therefore I believe I need to make vba count the rows of data and therefore know how many rows to paste

2) The formulas are not in every column (E.g. A:C, E:R, AA:AD have formulas)

Here is a basic example:

A B C D E
1 FORMULAS (Hidden)
2
3 [Inv. Typ] [Material #] [Batch] [Qty] [$]
4 [FG] [545] [555A] [5000] [$250000]
5 [WIP] [984] [659A] [200] [$650000]

In this example I would like the macro to:
1) Copy the hidden formulas in (A1:B1, D1:E1)
2) Paste these formulas into the range (A4:B5, D4:E5) (*The height of this range is not constant)
3) Calculate the Worksheet
4) Copy and paste values to the range (A4:B5, D4:E5) (*The height of this range is not constant)

* I would like column C to be left alone

If anyone can help me with anything regarding this I would greatly appreciate it. If I can provide any more information please ask.

Thank You,
Dave

 

[BobUmlas]

Sub Answer()
lrow = Range("A60000").End(xlUp).Row
For Each piece In Array("A1:B1", "D1:E1")
Range(piece).Copy
With Range(piece).Offset(3).Resize(lrow - 3)
.PasteSpecial
Calculate
.Copy
.PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub

 

[davec8723]

BobUmlas

Thanks for your reply!

Here is the code I placed into my workbook, changing the ranges slightly to match my needs.

Sub RM_Calc()
lrow = Range("D60000").End(xlUp).Row
For Each piece In Array("A11:C11,E11:K11,P11:R11,T11:V11,AB11:AC11")
Range(piece).Copy
With Range(piece).Offset(3).Resize(lrow - 3)
.PasteSpecial
.Calculate
.Copy
.PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False
End Sub

At the line

With Range(piece).Offset(3).Resize(lrow - 3)

I am getting a Run-time Error '1004' "Application-defined or object-defined error"

Please help if possible...

Should I state what type of variable Irow is?

Could you explain what the line of code causing the error is too if possible?? I'm hoping to one day be able to write my own

Thanks,
Dave

 

[BobUmlas]

For Each piece In Array("A11:C11,E11:K11,P11:R11,T11:V11,AB11:AC11")
should be
For Each piece In Array("A11:C11","E11:K11","P11:R11","T11:V11","AB11:AC11")

 

[davec8723]

Ahh! My mistake.. Thanks!

 

[Nikosan73]

I have tried replacing the Array ("a1:a3", etc with a Named Ranged.
However I get a 1004 error when I run. How do I get vba to recognise the 'pieces' within a named range?

 

[Nikosan73]

For completeness my code reads:

For Each piece In Array("Debt")
Range(piece).Copy
With Range(piece).Offset(0,1).
.PasteSpecial
.Calculate
.Copy
.PasteSpecial xlPasteValues
End With
Next
Application.CutCopyMode = False