Passing a value instead of a formula

ChuckDrago

Active Member
Joined
Sep 7, 2007
Messages
470
Office Version
  1. 2010
Platform
  1. Windows
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
Code:
 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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Code:
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
 
Upvote 0
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
Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,620
Members
452,786
Latest member
k3calloway

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
Back
Top