Copy and paste from several Workbooks

[bpotter]

I am trying to write a macro to copy the contents of a cell in one
workbook to a cell in another workbook. It will be the same cell every
time I run the macro. I have used the recorder to create a macro but I
was wondering if I could clean it up a little. Does anyone have a
suggestion. Here is part of the code.



Windows("Rt 109 Downtime Report.xls").Activate
Sheets("Today's ").Select
Application.Run "'Rt 109 Downtime Report.xls'!problem"
Sheets("Problem Well Sort").Activate
Range("M70").Select
Selection.Copy
Windows("NRF_Randy C-Aug-06.xls").Activate
Range("D3").Select
Selection.PasteSpecial Paste:=xlValues
Windows("Rt 109 Downtime Report.xls").Activate
Range("M69").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NRF_Randy C-Aug-06.xls").Activate
Range("E3").Select
Selection.PasteSpecial Paste:=xlValues
Windows("Rt 109 Downtime Report.xls").Activate
Range("B69").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NRF_Randy C-Aug-06.xls").Activate
Range("G3").Select
Selection.PasteSpecial Paste:=xlValues
Windows("Rt 109 Downtime Report.xls").Activate
Range("B70").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NRF_Randy C-Aug-06.xls").Activate
Range("H3").Select
Selection.PasteSpecial Paste:=xlValues

 

[Guest]

Dim sh as Worksheet
Dim sh1 as Worksheet
Windows("Rt 109 Downtime Report.xls").Activate
Sheets("Today's ").Select
Application.Run "'Rt 109 Downtime Report.xls'!problem"
set sh = Workbooks("Rt 109 Downtime Report.xls") _
.Worksheets("Problem Well Sort").
Set sh1 = Workbooks("NRF_Randy C-Aug-06.xls").worksheets(1)
sh1.Range("D3").Value = sh.Range("M70").value
sh1.Range("E3").Value = sh.Range("M69").Value
sh1.Range("G3").Value = sh.Range("B69").Value
sh1.Range("H3").Value = sh.Range("B70").Value


change the 1 in worksheets(1) to reflect the name of the sheet you want to
copy to (unless it will be the first sheet in the tab order - then you can
leave it)

 

[bpotter]

Thanks That works great!!!