Excel 2010 VBA error try to copy a workseet from another workbook into same

[DougP]

I want to copy a worksheet from another workbook into the workbook where the VBA cod is running.
Getting and Error #9 Subscript out of range.

 Workbooks("Master.HC.file.Test.Macros.xlsx").Sheets("BASE").Copy Workbooks("PACE.Feb.2013.02.15.13 TEST.xls")

DougP

 

[DougP]

Sorry for posting then figuring it out on my own, anyway here is what I discovered.
The names of the workbooks are refered to by just the workbook names not their entire path.
The workbook being copied from WorkbookSource has to open in Excel.

    Dim WorkbookCurrent, WorkbookSource As String
    WorkbookCurrent = Application.ActiveWorkbook.name
    WorkbookSource = "soure workbook name"
    Dim sh As Worksheet, wb As Workbook
    Set wb = Workbooks(WorkbookCurrent)
    For Each sh In Workbooks(WorkbookSource).Worksheets
       sh.Copy After:=wb.Sheets(wb.Sheets.Count)
    Next sh
[code]

DougP

 

[SkipVought]

hi,

FromWorkbookObject.FromWorksheetObject.Copy _
  ToWorkbookObject[b].ToWorksheetObject[/b]

In other words, copy from a sheet ro another workbook Before or After an existing sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

into the workbook where the VBA cod is running
So, you may use ActiveWorkbook as source and ThisWorkbook as destination.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886