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
|