copying sheets from 3 workbooks into 1 - Workbook_Open 2

[wotgoesup]

I need help:
3 staff each update their own "timesheet", which has a Workbook_Open macro that asks if it's a new week. If it is, it adds 7 days to a cell and uses that as part of the file name for the save. Works fine, each member of the team has their own wkbk, and the date is the same in each.
I want to copy those 3 into one workbook with a summary sheet. This has the same date cell, and the VBA builds the file name and opens the required workbook for the team members, but when it tries to copy into the summary workbook, I get subscript errors. I think it might be because the sheet name is the same in both source and target?
Sources:
a_mmmdd.xls sheet name A
b_mmmdd.xls sheet name B etc
Target:
summary_mmmdd.xls
sheet name Summary
need VBA to copy sheet A, Sheet B etc.

Also, is there a way to have the "new week" test NOT happen when the Summary VBA opens the individual workbooks?


THANKS,
Andy

 

[SkipVought]

Hi Andy,

Please post you code and indicate which statement gets the error.

Skip,
[sub]
[red][/red]
[/sub]

 

[wotgoesup]

Hi, Skip,
here's the code
Private Sub Workbook_Open()


a = MsgBox("Start a New Week?", vbYesNo)
If a = vbYes Then
Dim hammer As String
Dim enextdate As Date

Dim efiletxt As String
Range("E3").Select
ecurrdate = ActiveCell
enextdate = DateAdd("d", 7, ecurrdate)

Range("E3").Select
ActiveCell.FormulaR1C1 = enextdate

edatexls = Format(enextdate, "mmmdd") & ".xls"

efiletxt = "summary_" & edatexls

ActiveWorkbook.SaveAs filename:= _
("G:\time\" & efiletxt)
End If

Dim esummdate As String
Range("E3").Select
esummdate = ActiveCell
esummdate = Format(esummdate, "mmmdd")
Workbooks.Open filename:= _
"G:\time\Andy" & esummdate & ".xls"
Range("H2").Select
ActiveWorkbook.Worksheets("Andy").Copy _
Destination:=Workbooks("time_summary" & esummdate & ".xls").Worksheets("Andy.")

The blue statement stops with "Runtime error 9, subscript out of range."
esummdate contains "Feb05" which is correct for the files I'm opening. (reply to new week was "no")

 

[PHV]

Perhaps this ?
ActiveWorkbook.Worksheets("Andy").Copy _
Destination:=ThisWorkbook.Worksheets("Andy.")

BTW, are you sure of this ?
Worksheets("Andy[highlight].[/highlight]")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

ActiveWorkbook.Worksheets("Andy").Copy

copies the sheet object. Check out HELP on the Copy Method for Worksheets and Ranges.

Looks like you want to copy a RANGE

  ActiveWorkbook.Worksheets("Andy")[b].Range([i]SomeRangeReference[/i])[/b].Copy _
  Destination:=Workbooks("time_summary" & esummdate & ".xls").Worksheets("Andy.")[b].Cells([i]SomeRow, SomeColumn[/i])[/b]

Skip,
[sub]
[red][/red]
[/sub]

 

[wotgoesup]

Thanks, guys!
I now am able to copy the 3 time sheets into 1 summary wkbk.

Can anyone help me with the second question?
The open macro for each time sheet gets executed when the VBA in the summary wkbk is run.
i.e. the summary VBA triggers the "new week?" msgbox in each timesheet it opens.
How do I stop / bypass the msgbox when executing the summary VBA?
summary has Workbooks.Open filename:= .
which opens each timesheet, they have

"Private Sub Workbook_Open()"


a = MsgBox("Start a New Week?", vbYesNo)
If a = vbYes Then
...

ActiveWorkbook.SaveAs filename:= _
("G:\time\" & efiletxt)
End If

 

[SkipVought]

When I design workbook applications that use a worksheet form front-end, I always put the data in a TABLE on a separate sheet.

Then I use a query to get the data from that workbook's table without having to OPEN the workbook.

Skip,
[sub]
[red][/red]
[/sub]

 

[wotgoesup]

Mmm I wish I'd thought of that before I started!
It's only 4 people, so I'm not sure it's worth a rewrite.
Thanks again for your help