View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Paste value from another workbook and multiply by constant

It is unclear where you want to copy your values since your destination
never changes in the code you show. It also isn't clear what the sheet name
in Part List.xls is specified. Here is one possibility

Sub loop7()
Dim i As Integer
Const diameter = 25.4
Const metric = 1000
Dim rng as Range, cell as Range

With Workbooks("Part List.xls").Worksheets("Sheet1")
set rng = .Range(.Range("E2"), _
.Range("E2").End(xlup).offset(0,-3))
End With

With Workbooks("Test1.xls").Sheets("Sheet1").Range("B1" )

i = 0
For each cell in rng
.offset(i,0).Value = cell * diametet
.offset(i,1).Value = cell.offset(0,2) * metric
i = i + 1
Next
End sub

--
Regards,
Tom Ogilvy


"burl_rfc_h" wrote in message
ups.com...
I'm having issues with the following code, after the value is pasted
into the second workbook I'd like to multiply the value by a constant,
can someone please help.

Sub loop7()
Dim i As Integer

Workbooks("Part List.xls").Activate

Range("E2").Select

intRowCount = Range("E2").CurrentRegion.Rows.Count - 1

For i = 1 To intRowCount

ActiveCell.Copy

Const diameter = 25.4
Const metric = 1000

Workbooks("Test1.xls").Sheets("Sheet1").Range("B1" ).PasteSpecial
Paste:=xlValues * diameter

ActiveCell.Offset(0, 2).Copy
Workbooks("Test1.xls").Sheets("Sheet1").Range("B2" ).PasteSpecial
Paste:=xlValues * metric

Next i

End sub


Thanks