Effecient method of copying and pasting required

[Khurram]

Hi all,
I'm automating the copying of some numbers from one worksheet to
another. The values being copied have formulae behind them but I only
want the value hence the use of PasteSpecial in the below code. Below
code works for capturing one value but I need to capture several and
am sure there are more effecient ways then simply going back and forth
between the worksheets. I've tried to assign the values to "Long" or
"Double" but seem to capture the numbers in decimal format. Please
help I've no idea what else to do.


Thank you kindly
Khurram


Workbooks.Open Filename:="C:\Feb Batch Metrics.xls"

Workbooks("Feb Batch Metrics.xls").Sheets("Batch
Metrics").Range("I47").Select
ActiveCell.Copy
Windows(2).Activate
Sheets("SD Summary").Select
Cells(1, 1).Select
Selection.PasteSpecial xlPasteValues

Windows(2).Activate
Workbooks("Feb Batch Metrics.xls").Sheets("Batch
Metrics").Range("F47").Select
ActiveCell.Copy
Windows(2).Activate
Sheets("SD Summary").Select
Cells(2, 1).Select
Selection.PasteSpecial xlPasteValues

Workbooks("Feb Batch Metrics.xls").Close SaveChanges:=False

 

[NickHK]

Khurram,
It's not clear which workbook is referenced by Windows(2).Activate, but you
can amend the code below to suit:

Private Sub CommandButton1_Click()
Dim SourceWB As Workbook
Dim DestinationWS As Worksheet

Set DestinationWS = ThisWorkbook.Sheets("SD Summary")
Set SourceWB = Workbooks.Open(Filename:="C:\Feb Batch Metrics.xls")

With SourceWB.Sheets("Batch Metrics")
DestinationWS.Range("A1").Value = .Range("I47").Text
DestinationWS.Range("A2").Value = .Range("F47").Text
End With

End Sub

NickHK

 

[Guest]

This code is better for going back and forth between sheets

Cancel = True

Workbooks.Open Filename:="C:\Feb Batch Metrics.xls"

Workbooks("Feb Batch Metrics.xls"). _
Worksheets("Batch Metrics"). _
Range("I47").Copy
Workbooks("Feb Batch Metrics.xls"). _
Worksheets("SD Summary").Range("A1").PasteSpecial xlPasteValues

Workbooks("Feb Batch Metrics.xls"). _
Worksheets("Batch Metrics"). _
Range("F47").Copy
Workbooks("Feb Batch Metrics.xls"). _
Worksheets("SD Summary").Range("A2").PasteSpecial xlPasteValues


Workbooks("Feb Batch Metrics.xls").Close SaveChanges:=False