Importing data

[Risky Dave]

Hi,

I have a piece of code that imports data from one workbook to another:

SourceBook = Range("e9")
'This is called 071203 PRG Risk log v 2.00.xls
TargetBook = Range("e4")
'This is called PRG Metrics.xls

Workbooks.Open Filename:=SourceBook
Sheets("Navigation").Select
Sheets("Sandpit").Visible = True
Sheets("Sandpit").Select
Cells.Select
Selection.Copy
Windows("PRG Metrics.xls").Activate
Sheets("Data").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Navigation").Select
Windows("071203 PRG Risk log v 2.00.xls").Activate
Sheets("Sandpit").Select
ActiveWindow.SelectedSheets.Visible = False
Workbooks("071203 PRG Risk log v 2.00.xls").Close SaveChanges = False

This code works as I want it to.

Can anyone tell me how I can replace the references to specific filenames
(eg. Windows("PRG Metrics.xls").Activate) with references to SourceBook and
TargetBook (so that it would look like Windows(TargetBoook).Activate)?

I'm working on this in Office 2007 but it will be running on Office 2003, if
that makes any difference.

Apologies if this is a simple thing, but I'm not much of a "softie".

TIA

Dave

 

[Joel]

When you open a new workbook it becomes the active workbook. It is always a
good idea immediately after your open the workbook to set a variable to the
workbook like I have done below

'This is called 071203 PRG Risk log v 2.00.xls
TargetBook = Range("e4")
'This is called PRG Metrics.xls

Workbooks.Open Filename:=SourceBook
set SBook = activeworkbook
Sheets("Navigation").Select
Sheets("Sandpit").Visible = True
Sheets("Sandpit").Select
Cells.Select
Selection.Copy
SBook.Activate
Sheets("Data").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Navigation").Select
Windows("071203 PRG Risk log v 2.00.xls").Activate
Sheets("Sandpit").Select
ActiveWindow.SelectedSheets.Visible = False
Workbooks("071203 PRG Risk log v 2.00.xls").Close SaveChanges = False


Here is a less comp;licated version of your code

Sub test()

'This is called 071203 PRG Risk log v 2.00.xls
TargetBook = Range("e4")
'This is called PRG Metrics.xls

Workbooks.Open Filename:=SourceBook
Set SBook = ActiveWorkbook
Sheets("Sandpit").Visible = True
With Sheets("Sandpit")
.sells.Copy
End With

With SBook.Sheets("Data")
.PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
End With

With Windows( _
"071203 PRG Risk log v 2.00.xls"). _
Sheets("Sandpit")

.SelectedSheets.Visible = False
End With

Workbooks("071203 PRG Risk log v 2.00.xls").Close SaveChanges = False
End Sub