Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Problem w/code to copy worksheet to new workbook
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Problem w/code to copy worksheet to new workbook
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default Problem w/code to copy worksheet to new workbook
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Problem w/code to copy worksheet to new workbook
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




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Problem w/code to copy worksheet to new workbook
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







Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 29
Default Problem w/code to copy worksheet to new workbook
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









Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Problem w/code to copy worksheet to new workbook
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










Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 29
Default Problem w/code to copy worksheet to new workbook
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












Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 17
Default Problem w/code to copy worksheet to new workbook
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













Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 29
Default Problem w/code to copy worksheet to new workbook
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















Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 29
Default Problem w/code to copy worksheet to new workbook
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

















Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
copy and paste using code from workbook to workbook bigdaddy3 Excel Discussion (Misc queries) 2 September 14th 05 11:06 AM
VBA Code to copy a worksheet to another Excel Workbook Hummel Excel Programming 3 November 9th 04 12:54 AM
Copy sheet from one workbook to another workbook problem Ron de Bruin Excel Programming 3 August 5th 04 07:19 PM
Code to copy range vs Copy Entire Worksheet - can't figure it out Mike Taylor Excel Programming 1 April 15th 04 08:34 PM
Copy worksheet, code and all, into workbook? Ed[_9_] Excel Programming 2 September 22nd 03 03:11 PM


All times are GMT +1. The time now is 07:34 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement