Pasting Values, not formulas as I want

[jim may]

In my Rng1 - Col A and Col F - I have formulas;
My Rng1.copy line is pasting these as Values into my destination sheet, not a formulas. What's Up? Can someone spot the problem?
Thanks,

Set rng = ActiveSheet.AutoFilter.Range
    rng.AutoFilter Field:=12, Criteria1:="<>"
    Set Rng1 = rng.Offset(1).SpecialCells(xlCellTypeVisible)
    Nr = Worksheets("Data-CompletedItems").Range("B" & Rows.Count).End(xlUp).Row + 1
    'Set destrng = Sheets("Data-CompletedItems").Range("A" & Nr)
    Rng1.Copy Destination:=Sheets("Data-CompletedItems").Range("A" & Nr)

 

[Dryver14]

Jim,

I am learning this myself but maybe this line (nothing to do with your macro) may push you in the right direction

Range("A").PasteSpecial (xlPasteFormulas)

 

[jim may]

Got R/T 1004 error with:

Rng1.Copy Destination:=Sheets("Data-CompletedItems").Range("A" & Nr).PasteSpecial(xlPasteFormulas)

 

[jim may]

I'm getting a R/Y 438 using:

Rng1.Copy Worksheets("Data-CompletedItems").Range("A" & Nr).Paste

 

[jim may]

From the immediate window
Maybe you can't paste broken up range references such as these.. Pastespecail.value
if the only option... ???


? rng1.Address
$A$7:$AJ$7,$A$12:$AJ$12,$A$17:$AJ$17,$A$254:$AJ$254

 

[Dryver14]

maybe try

Rng1.Copy
Sheets("Data-CompletedItems").Range("A" & Nr).PasteSpecial(xlPasteFormulas)

 

[mikerickson]

When working with discontinous ranges, one should use the .Areas property.

'Dim rng As Range, Rng1 As Range, Nr As Long
Dim oneArea As Range

    Set rng = ActiveSheet.AutoFilter.Range
    rng.AutoFilter Field:=12, Criteria1:="<>"
    Set Rng1 = rng.Offset(1).SpecialCells(xlCellTypeVisible)

For Each oneArea in Rng1.Areas
    Nr = Worksheets("Data-CompletedItems").Range("B" & Rows.Count).End(xlUp).Row

    oneArea.Copy Destination:=Sheets("Data-CompletedItems").Range("A" & Nr)
Next oneArea

I copied the code for the destination from the post above, you might want to add 1 to Nr before pasting over that last row.

 

[jim may]

Thanks Mike, You were Right on it!!
It all works fine NOW!!

Jim May