Effecient method of copying and pasting required

K

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
 
N

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
 
J

Joel

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top