Copy between workbooks

[Risky Dave]

Hi,

I've managed to cobble together the following:

'Metrics_data_import Macro

SourceBook = Range("e9")
'This is called 071203 PRG Risk log v 2.00.xls and contains the full network
path
SourceBook2 = Range("e6")
'This is called 071203 PRG Risk log v 2.00.xls
TargetBook = Range("e4")
'This is called PRG Metrics v 1.00.xls and is in a different folder on the
network

Workbooks.Open Filename:=SourceBook
Sheets("Navigation").Select
Sheets("Sandpit").Visible = True
Sheets("Sandpit").Select
Cells.Select
Selection.Copy
Windows(TargetBook).Activate
Sheets("Data").Visible = True
Sheets("Data").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Sheets("Navigation").Select
Windows(SourceBook2).Activate
Sheets("Sandpit").Select
ActiveWindow.SelectedSheets.Visible = False
Application.CutCopyMode = False
Workbooks(SourceBook2).Close SaveChanges = False
Sheets("Data").Visible = False

This runs from a file called PRG Metrics v 1.00 ("TargetBook") and does the
following:
1) Open a workbook elsewhere on the network ("SourceBook");
2) Unhide a sheet on SourceBook ("Sandpit");
3) Copy the contents of Sandpit;
4) Unhide a sheet on TargetBook called "Data"
5) Copy the clipboard (ie Sandpit) to Data;
6) Hide sandpit
7) Close SourceBook without making any changes
8) Hide Data

This works if SourceBook and TargetBook are in the same folder on the
network, but when they are different, the Windows.(TargetBook).Activate line
throws up:

Run-time error "9":

Subscript out of range


Any suggestions on how to fix this would be appreciated.

TIA

Dave

 

[Dave Peterson]

I'd put the full name (including the path) into those cells.

But I am confused about what sourcebook2 is doing.

Dim ActSheet as worksheet
dim SourceWkbk as workbook
dim TargetWkbk as workbook

with actSheet
set sourcewkbk = workbooks.open(Filename:=.range("e9").value)
set targetwkbk = workbooks.open(Filename:=.range("e4").value)
end with

sourcewkbk.worksheets("sandpit").cells.copy _
destination:=targetwkbk.worksheets("Data").range("a1")

sourcewkbk.close savechanges:=false

targetwkbk.close savechanges:=true 'did you want to close and save this?

========
Untested, uncompiled. Watch for typos!

 

[Risky Dave]

Dave,

Thanks for the help.
I'm just trying your code now, but XL doesn't seem to like the expression

destination:=TargetWkBk.Worksheets("Data").Range("A1")

It is producing a compile error

Expected:expression

Apologies if this is something simple - I don't claim to be a programmer

Dave

 

[Risky Dave]

SourceBoko2 is the same as SourceBook but does not contain the network path.
I was having problems getting XL to close the sopurce workbook and this
seemed to work.

Not very elegant, I admit :-(

 

[Dave Peterson]

That portion is a continuation of the previous line:

sourcewkbk.worksheets("sandpit").cells.copy _
destination:=targetwkbk.worksheets("Data").range("a1")

If this doesn't help, post the current code you're using.