Moving worksheets to a network Drive.

[cjpicc11]

Hi guys, I'm trying to move a worksheet to a network drive but I keep getting the subscript is out of range error. This is what I have so far:

Sheets("Sheet1").Select
Windows("My Version.xls").Activate
Sheets("Sheet1").Select
Application.WindowState = xlMinimized
Application.WindowState = xlMinimized
Windows("Book1.xls").Activate
Windows("My Version.xls").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Move Before:=Workbooks
("Book1.xls").Sheets(1)

Without putting the drive letter in front of book1.xls it works with no problems at all. When I add the syntax I receive the error. This file is located on the H drive so I dont know why im still receiving this error.

 

[SkipVought]

Hi,

Could if be that Book1.xls has never been SAVED on ANY drive?

I don't see where any of your workbook objects have been instantiated.

Skip,
[sub]
[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway! [/sub]

 

[cjpicc11]

It's saved both Locally, and on the Network Drive.

 

[SkipVought]

Logically, what are you trying to do, start to finish?

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[cjpicc11]

Run a Macro thats localy on my hardrive, it will then populate data into table I have on that sheet that the macro is stored in, then I would like to move that sheet on to another workbook thats stored on a network drive. Can this be done?

 

[SkipVought]

  Dim wbOther As Workbook, wsThis As Worksheet
  Set wsThis = ActiveSheet
  Set wbOther = Workbooks.Open("D:\My Documents\MyStuff\LO.xls")
  wsThis.Copy Before:=wbOther.Sheets(1)

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[cjpicc11]

Works like a charm, thank you. How would I be able to copy data within a sheet on to a another workbook on a network drive instead of just moving the worksheet.

 

[SkipVought]

ThisWorkbook.SheetCodeName.RangeReference.Copy _
  DestinationWorkbook.DestSheetCodeName.UpperLHCell

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[cjpicc11]

How would I finish this code if I were to copy this data onto a separate sheet that's stored on a network drive.

Private Sub CopyRange()
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
ActiveCell.Offset(10, 0).Range("A1").Select


End Sub

 

[SkipVought]

Turn on your macro recorder and perform the copy 'n' paste to the WORKBOOK on the network drive.

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]

 

[cjpicc11]

I've tried that and get a subscript out of range error at this line:
Windows("G:\Common\Common\dwlee\Reports\TPS.xls").Activate

 

[SkipVought]

When you OPEN this workbook set an object variable...

Dim wbCopyTo as workbook, wbCopyFrom as workbook
set wbCopyFrom =ActiveWorkbook

....

Set wbCopyTo = workbooks.open("G:\Common\Common\dwlee\Reports\TPS.xls")

...

wbCopyFrom.Activate
Activesheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy wsCopyTo.Sheets("WhatSheet???").Range("A1")

Skip,
[sub]
[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount! [/sub]