Problem w/code to copy worksheet to new workbook

G

Guest

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
 
P

PCLIVE

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
 
G

Guest

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
 
G

Guest

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
 
P

PCLIVE

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
 
G

Guest

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
 
P

PCLIVE

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
 
G

Guest

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.
 
P

PCLIVE

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.


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
 
P

PCLIVE

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining several workbooks into one file 4
Create Workbooks and Copy Template Worksheet to those Workbooks 12
I can't figure out the correct syntax for referencing a workbook. 8
Copy contents to a selected sheet in another workbook 1
Vaiable Help in VBA 5
If -Then in VBA Code 4
Export specific cell values to an external workbook 1
Macro copy values to a different workbook 4

Top