Just realized you'd need to adjust the SaveAs line. I moved it to the
bottom after closing sheets and I changed it to "ActiveWorkbook".
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\"
wbName = ActiveWorkbook.Name
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")
bk.Worksheets(1).Copy After:=Workbooks(wbName).Worksheets(1)
Workbooks(wbName).Worksheets(2).Name = "AAAA CC"
bk1.Worksheets(1).Copy After:=Workbooks(wbName).Worksheets(2)
Workbooks(wbName).Worksheets(3).Name = "BBBB CC"
bk2.Worksheets(1).Copy After:=Workbooks(wbName).Worksheets(3)
Workbooks(wbName).Worksheets(4).Name = "CCCC CK"
bk1.Close Savechanges:=False
bk2.Close Savechanges:=False
bk.Close Savechanges:=False
ActiveWorkbook.SaveAs sPathBalancing & "Payment Summary Report
Today.xls.xls"
End Sub
"PCLIVE" <pclive(remove wrote in message
...
The problem is not that the Summary worksheet is getting
deleted...instead, it is that the Summary worksheet does not exist on the
file in which you are saving as "Payment Summary Report Today.xls".
Essentially, what you are doing is copying a worksheet from BBBB... &
CCCC... to "AAAA CC Today.xls". If "AAAA CC Today.xls" does not contain a
"Summary" worksheet, then it still won't have it when you "SaveAs" to
"Payment Summary Report Today.xls".
I noticed in your code that you seem to be naming "bk.Worksheets(2)". So
you'll probably be missing the AAAA CC tab and the first tab will be CCCC
CK.
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"
It sounds like you are wanting these sheets to be copied to the "Payment
Summary Report.xls" and then SaveAs to "Payment Summary Report Today.xls".
If that is the case, then:
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\"
wbName = ActiveWorkbook.Name
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")
bk.Worksheets(1).Copy After:=Workbooks(wbName).Worksheets(1)
Workbooks(wbName).Worksheets(2).Name = "AAAA CC"
bk1.Worksheets(1).Copy After:=Workbooks(wbName).Worksheets(2)
Workbooks(wbName).Worksheets(3).Name = "BBBB CC"
bk2.Worksheets(1).Copy After:=Workbooks(wbName).Worksheets(3)
Workbooks(wbName).Worksheets(4).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
...
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