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

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

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