copy paste for each cell in an array

R

rpw

I'm trying to copy paste from one worksheet to another via VBA. My first
attempt resulted in an error as soon as a blank cell was encountered. So I
came up with the following code. The For...Next and If...Then work fine -
the problem is with the copy/paste lines. I'd appreciate the help (and a
little explaination so I don't gum it up again the next time). Here's the
<snipped> code:

myAddresses = Array("A12:A35,A37:C48,L12:O48,L49:M56,L58:L59")
For RangeCounter = LBound(myAddresses) To UBound(myAddresses)
Set BCRange = Nothing
On Error Resume Next
Set BCRange = wsh1.Range(myAddresses(RangeCounter))
For Each cell In BCRange.Cells
If cell.HasFormula = True Then
wsh1.Range(myAddresses(RangeCounter)).Range(cell).Copy
NewWbk.Worksheets("Basic Components Pricing").Activate
wsh2.Range(myAddresses(RangeCounter)).PasteSpecial _
xlPasteFormulas
ElseIf cell.Value > 0 Then
wsh1.Range(myAddresses(RangeCounter)).Range(cell).Copy
NewWbk.Worksheets("Basic Components Pricing").Activate
wsh2.Range(myAddresses(RangeCounter)).Range
_(cell).PasteSpecial xlPasteValues
Else
End If
Next cell
Next RangeCounter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell values to an array, then array transpose to another workbook 8
Arrays and scattered cell values or another way? 16
Tried and true copy code lines fail me here 7
Create if partial match then copy and paste data from one cell to another 1
paste transpose array of variables 3
Macro to copy paste values based on cell input ... 4
Marco to copy and paste values based on cell value (cont) 6
copy/paste: strange feature (I think) 1

Top