Tom,

Thank you for revising the code to help me out! I tried it, and it
improved the issue a bit. It no longer copied the Project (2) data to
the Project worksheet of the new workbook. However, it still didn't
run the macro and create new sheets as necessary. But, guess what? I
was able to manipulate your code to make it work! Here's what ended up
working:

Sub CopyData()
'
' CopyData Macro
'

Dim Bk1 As Workbook
Dim Bk2 As Workbook
Dim Sh1 As Worksheet, Sh2 As Worksheet
Dim s As String
Dim ar As Range
Set Bk1 = Workbooks("Projects.xls")
Set Bk2 = Workbooks("Projects2.xls")


s = "A3:X8,A10:G14,A16:G16,H14:X19,A25:X92,A101:FY 101"
For Each Sh1 In Bk1.Worksheets
If Sh1.Visible = xlSheetVisible Then
On Error Resume Next
Set Sh2 = Bk2.Worksheets(Sh1.Name)
Else
Run "Projects2.xls!McrNewProject"
End If
For Each ar In Sh1.Range(s).Areas
Set Sh2 = Bk2.Worksheets(Sh1.Name)
ar.Copy Destination:=Sh2.Range(ar.Address)
Next
Next
End Sub

Tom, I can't thank you enough for your help! This is terrific!
Tina


Tom Ogilvy wrote:
Sub CopyData()
'
' CopyData Macro
'

Dim Bk1 As Workbook
Dim Bk2 As Workbook
Dim Sh1 As Worksheet, Sh2 As Worksheet
Dim s As String
Dim ar As Range
Set Bk1 = Workbooks("Projects.xls")
Set Bk2 = Workbooks("Projects2.xls")

s = "A3:X8,A10:G14,A16:G16,H14:X19,A25:X92,A101:FY 101"
For Each Sh1 In Bk1.Worksheets
if sh1.Visible = xlSheetVisible then
On Error Resume Next
Set Sh2 = Bk2.Worksheets(Sh1.Name)
On Error GoTo 0
If Not Sh2 Is Nothing Then
For Each ar In Sh1.Range(s).Areas
ar.Copy Destination:=Sh2.Range(ar.Address)
Next
Else
Run "Projects2.xls!McrNewProject"
set Sh2 = Activesheet
Sh2.name = Sh1.Name
For Each ar In Sh1.Range(s).Areas
ar.Copy Destination:=Sh2.Range(ar.Address)
Next

End If
End if
Next
End Sub


--
Regards,
Tom Ogilvy


wrote in message
ups.com...
Yes, there is a Project worksheet in the Projects.xls workbook, which
is hidden. There is no data in this worksheet, as it is used in the
macro to create new Project worksheets. Projects2.xls is basically a
copy of Projects.xls, with the exception of approximately 6 objects,
which have been deleted or changed from drop downs/checkboxes to option
buttons. Therefore, Projects2.xls also has this hidden worksheet -
Project - which is also empty (no data) because it's used in the macro
to create new Project worksheets. The code is the same as what you
provided last. I've tried some variations, but all resulting the same.

It's not a big deal. We'll just have to have the users run the
mcrNewProject macro in the new workbook prior to running the code to
copy the data. You helped tremendously already!

Thanks!