VBA Copy and Paste on Multiple Ranges

davec8723

New Member
Joined
Jul 29, 2011
Messages
45
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

BobUmlas

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
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

New Member
Joined
Jul 29, 2011
Messages
45
BobUmlas

Thanks for your reply!

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

Code:
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

Code:
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

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
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

New Member
Joined
Jul 29, 2011
Messages
45

ADVERTISEMENT

Ahh! My mistake.. Thanks!
 

Nikosan73

New Member
Joined
Apr 13, 2017
Messages
6
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

New Member
Joined
Apr 13, 2017
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,101
Members
416,161
Latest member
David1966Lewis

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
Top