The "Payment Summary Report Today.xls" had a few protected cells. I
unprotected the worksheet and still go the "ACCESS DENIED" error. I
recreated the workbook with no protection and the code is working, however
there is one problem. The Payment Summary Report Today.xls has a worksheet
titled "Summary" and when I run this code, the Summary Sheet is deleted. How
can I stop that sheet from being deleted. Thanks Paul.

"PCLIVE" wrote:

I've attempted to recreate your situation. I created 3 .xls files named
"AAAA CC Today.xls", "BBBB CC Today.xls", and "CCCC CK Today.xls". These
files reside in "C:\Documents and Settings\Desktop\0-Production
File\Processing\".
I'm using the code below. After changing the "Set bk" lines to indicate,
"bk", "bk1", and "bk2", it seems to work. Where is it failing for you?

Sub Oct23CombinebooksforSummary()
Dim sPathProduction As String
Dim sPathBalancing As String
Dim bk As Workbook, bk1 As Workbook
Dim bk2 As Workbook

sPathProduction = "C:\Documents and Settings\Desktop\0-Production
File\Processing\Today\"
sPathBalancing = "C:\Documents and Settings\Desktop\0-Production
File\Balancing\Summary Reports\"

If Dir(sPathBalancing & "Payment Summary Report Today.xls") < "" Then
Kill sPathBalancing & "Payment Summary Report Today.xls"
End If
Set bk = Workbooks.Open(sPathProduction & "AAAA CC Today.xls")
Set bk1 = Workbooks.Open(sPathProduction & "BBBB CC Today.xls")
Set bk2 = Workbooks.Open(sPathProduction & "CCCC CK Today.xls")

bk1.Worksheets(1).Copy After:=bk.Worksheets(1)
bk.Worksheets(2).Name = "AAAA CC"
bk2.Worksheets(1).Copy After:=bk.Worksheets(2)
bk.Worksheets(2).Name = "BBBB CC"
bk.Worksheets(1).Name = "CCCC CK"
bk.SaveAs sPathBalancing & "Payment Summary Report Today.xls.xls"
bk1.Close Savechanges:=False
bk2.Close Savechanges:=False
bk.Close Savechanges:=False
End Sub
"ploddinggaltn" wrote in message
...
Hi Again Paul,

The three reports I'm trying to copy into the Payment Summary Report
Today.xls are in 3 separate workbooks. I have this code placed in the
Summary Tab (tab 1) module in the Payment Summary Report Today.xls. Is
that
the problem? Also I have the sheets already named in the Payment Summary
Report Today.xls, they are named AAAA CC (tab2), BBBB CC (tab3) and CCCC
CK
tab4). Thanks

"PCLIVE" wrote:

You're missing a double quote before "C:\" in the sPathProduction
statement.
sPathProduction = C:\Documents and Settings\Desktop\0-Production
File\Processing\Today\"

You're setting bk 3 times. Should this not be "bk", "bk1", and "bk2"?
Set bk = Workbooks.Open(sPathProduction & "AAAA CC Today.xls")
Set bk = Workbooks.Open(sPathProduction & "BBBB CC Today.xls")
Set bk = Workbooks.Open(sPathProduction & "CCCC CK Today.xls")

Correct and repost if problems persist.

Regards,
Paul

"ploddinggaltn" wrote in
message
...
Here is the code I'm using now and getting the error of "Variable or
With
Block Variable Not Set". The code opens all three reports but does not
copy
the sheets to the "Payment Summary Report Today.xls.xls report


Sub Oct23CombinebooksforSummary()
Dim sPathProduction As String
Dim sPathBalancing As String
Dim bk As Workbook, bk1 As Workbook
Dim bk2 As Workbook


sPathProduction = C:\Documents and Settings\Desktop\0-Production
File\Processing\Today\"
sPathBalancing = "C:\Documents and Settings\Desktop\0-Production File\
Balancing\Summary Reports\"

If Dir(sPathBalancing & "Payment Summary Report Today.xls") < "" Then
Kill sPathBalancing & "Payment Summary Report Today.xls"
End If

Set bk = Workbooks.Open(sPathProduction & "AAAA CC Today.xls")
Set bk = Workbooks.Open(sPathProduction & "BBBB CC Today.xls")
Set bk = Workbooks.Open(sPathProduction & "CCCC CK Today.xls")


bk1.Worksheets(1).Copy After:=bk.Worksheets(1)
bk.Worksheets(2).Name = "AAAA CC"
bk2.Worksheets(1).Copy After:=bk.Worksheets(2)
bk.Worksheets(2).Name = "BBBB CC"
bk.Worksheets(1).Name = "CCCC CK"
bk.SaveAs sPathBalancing & "Payment Summary Report Today.xls.xls"
bk1.Close Savechanges:=False
bk2.Close Savechanges:=False
bk.Close Savechanges:=False
End Sub



"PCLIVE" wrote:

Please repost your code and indicate where the error occurs.


"ploddinggaltn" wrote in
message
...
Hi Paul,

Thanks for your suggestions. The backslashed helped, now the code
opens
each of the three reports but it does not copy them to the new
workbook.
I've also clarified the sPath to sPathBalancing as shown below and
now
I
get
a message "Access Denied". Any additional help you can give me is
appreciated. Thanks

If Dir(sPathBalancing & "Payment Summary Report Today.xls") < ""
Then
Kill sPathBalancing & "Payment Summary Report Today.xls"
End If

"PCLIVE" wrote:

When setting your path string, try including a backslash at the
end.
sPathProduction = "C:\Documents and Settings\Desktop\0-Production
File\Processing\Today\"
sPathBalancing = "C:\Documents and Settings\Desktop\0-Production
File\Balancing\Summary Reports\"



You also have two references to "sPath". But I don't see that set
anywhere.
If Dir(sPath & "Payment Summary Report Today.xls") < "" Then
Kill sPath & "Payment Summary Report Today.xls"

HTH,
Paul


"ploddinggaltn" wrote in
message
...
Here is the code I'm using to copy the first page of 3 separate
reports
to
a
new workbook. The new workbook is titled, "Payment Summary
Report
Today".
The first page of the new workbook is titled, "Summary", the
second
is
titled, "AAAA CC", the 3rd is titled, "BBBB CC" and the 4th is
titled
"CCCC
CK". I'm launching this code with a control button on the
summary
report
and
it is not working. When I click the button, I get a Error Code
400
however
I've verified the pathing for "sPathProduction" and
"sPathBalancing"
are
correct. Any ideas what is wrong here. Thanks so much

Sub Oct23CombineWSforSummary()
Dim sPathProduction As String
Dim sPathBalancing As String
Dim bk As Workbook, bk1 As Workbook
Dim bk2 As Workbook


sPathProduction = "C:\Documents and Settings\Desktop\0-Production
File\Processing\Today"
sPathBalancing = "C:\Documents and Settings\Desktop\0-Production
File\Balancing\Summary Reports"

If Dir(sPath & "Payment Summary Report Today.xls") < "" Then
Kill sPath & "Payment Summary Report Today.xls"
End If

Set bk = Workbooks.Open(sPathProduction & "AAAA CC Today.xls")
Set bk = Workbooks.Open(sPathProduction & "BBBB CC Today.xls")
Set bk = Workbooks.Open(sPathProduction & "CCCC CK Today.xls")


bk1.Worksheets(1).Copy After:=bk.Worksheets(1)
bk.Worksheets(2).Name = "AAAA CC"
bk2.Worksheets(1).Copy After:=bk.Worksheets(2)
bk.Worksheets(2).Name = "BBBB CC"
bk.Worksheets(1).Name = "CCCC CK"
bk.SaveAs sPathBalancing & "Payment Summary Report Today.xls.xls"
bk1.Close Savechanges:=False
bk2.Close Savechanges:=False
bk.Close Savechanges:=False
End Sub