Passing a value instead of a formula

[ChuckDrago]

Hi everyone,

I need help on a syntax question. I am passing information between two workbooks. The fields in question are all values except one that contains a formula. The formula displays the resulting value and that number is what I need to pass. The code below shows the copy/destination method used and the field with a formula is the one passing the Order Qty (Range "Wm" to Range("Hz"). As usual, thanks in advance for your always valuable assistance.
Chuck

 Sheets("Kanban Data").Range("C" & m).Copy Destination:=Sheets("Transfer").Range("A" & z) 'Part Id
    Sheets("Kanban Data").Range("C" & m).Copy Destination:=Sheets("Transfer").Range("P" & z) 'Aux Part ID
    Sheets("Kanban Data").Range("D" & m).Copy Destination:=Sheets("Transfer").Range("B" & z) 'Descr
    Sheets("Kanban Data").Range("E" & m).Copy Destination:=Sheets("Transfer").Range("C" & z) 'W/House
    Sheets("Kanban Data").Range("F" & m).Copy Destination:=Sheets("Transfer").Range("D" & z) 'Location
    Sheets("Kanban Data").Range("G" & m).Copy Destination:=Sheets("Transfer").Range("E" & z) 'M/B/T
    Sheets("Kanban Data").Range("K" & m).Copy Destination:=Sheets("Transfer").Range("F" & z) 'Leadtime
    Sheets("Kanban Data").Range("W" & m).Copy Destination:=Sheets("Transfer").Range("H" & z) 'OrderQty <<<<< Affected line
    Sheets("Kanban Data").Range("Q" & m).Copy Destination:=Sheets("Transfer").Range("I" & z) 'Containment
    Sheets("Kanban Data").Range("H" & m).Copy Destination:=Sheets("Transfer").Range("J" & z) 'Buyer Init
    Sheets("Kanban Data").Range("B" & m).Copy Destination:=Sheets("Transfer").Range("L" & z) 'Total Cards

 

[ranman256]

sub CopyCells
 Sheets("Kanban Data").Range("C" & m).copy
 PasteRng  Sheets("Transfer").Range("P" & z)
 
 Sheets("Kanban Data").Range("D" & m).Copy
 PasteRng  Sheets("Transfer").Range("B" & z)


  'etc....


end sub


sub PasteRng(rng as range)
 rng.select
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
end sub

 

[ChuckDrago]

ranman256 Appreciate your help but... I adopted a portion of your code and modified mine to continue copying the way it was, adding the special paste bit at the end (looking like below). Then something absolutely beyond my comprehension happened: when parsing multiple records (the usual case) ONLY THE FIRST ONE displays the value of the formula field. The remaining records display nothing (?).
I went line by line and noticed that every record after the first jumps off the statement after the selection of the copy from field, that is it ignores the pasting of the value. If you can make sense of that I will really appreciate your input.
Chuck

Sheets("Kanban Data").Range("AA" & m).Copy Destination:=Sheets("Transfer").Range("N" & z) 'Color Name
    Sheets("Transfer").Range("G" & z).Value = Sheets("Kanban Data").Range("AC" & m).Value
    Sheets("Transfer").Range("K" & z) = 1
    [B]Sheets("Kanban Data").Range("W" & m).Select
    Selection.Copy
    Sheets("Transfer").Select
    Range("H" & z).Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False[/B]
    
End Sub