Copying 2nd Sheet

[EMoe]

Hello,

I'm still having a problem copying sheet 2, in this code (named card
to the opened workbook.

Here's the code I'm working on. When I run it, I get "Subscript Out O
Range".
It runs then stops on the added worksheet, but on sheet 1.

Please, have a look:

Sub test()

Columns("A:I").Copy
Workbooks.Add
Columns("A:I").PasteSpecial Paste:=xlPasteFormats
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False

Workbooks(SolidWorksheet).Activate
Sheets("card").Select
Columns("A:I").Copy
Workbooks(Book13).Activate
Sheets("Sheet2").Select
Columns("A:I").PasteSpecial Paste:=xlPasteFormats
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False
Sheets("Sheets1").Select

ActiveWorkbook.SaveAs Filename:= _
"c:\Books\" & Format(Date, "mm-dd-yyyy") & " Sunday Worksheet.xls
' _
FileFormat:=xlNormal, Password:="", WriteResPassword:=""' _
ReadOnlyRecommend:=False, CreateBackup:=False
ActiveWorkbook.Close
returnvalue = MsgBox("Don't Forget To Put All Deposit Slips In Th
Book", 64, "Secretary's Message")
If returnvalue = 1 Then 'User chose OK

End If
End Sub

Thanks,
EMo

 

[anilsolipuram]

try this macro

Sub test()
dim new_file as variant
dim p_file as variant
p_file=activeworkbook.name
Columns("A:I").Copy
Workbooks.Add
new_file=activeworkbook.name
Columns("A:I").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False

Workbooks(p_file).Activate
Sheets("card").Select
Columns("A:I").Copy
Workbooks(new_file).Activate
Sheets("Sheet2").Select
Columns("A:I").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False
Sheets("Sheets1").Select

ActiveWorkbook.SaveAs Filename:= _
"c:\Books\" & Format(Date, "mm-dd-yyyy") & " Sunday Worksheet.xls" ' _
FileFormat:=xlNormal, Password:="", WriteResPassword:=""' _
ReadOnlyRecommend:=False, CreateBackup:=False
ActiveWorkbook.Close
returnvalue = MsgBox("Don't Forget To Put All Deposit Slips In The
Book", 64, "Secretary's Message")
If returnvalue = 1 Then 'User chose OK

End If
End Sub

 

[EMoe]

The error doesn't point to any particular place in the code. I only see
that the new workbook is opened, but the next step to copy sheet 2 of
the solidworksheet book, over to sheet 2 of the new workbook doesn't
happen.


Not sure what you mean by what is it set to. It's just a workbook that
I want to copy sheets 1 & 2 (columns A through I on both), over to a
new workbook. The rest of the codes names it with the current
date_Sunday Worksheets into a folder called books.

Thanks,
EMoe

 

[anilsolipuram]

paste this macro in module not "this workbook", There should b
sheetname called "card" in your workbook.

try this macro and let me know. If you get error again, let me kno
which line you are getting error.


Sub test()
Dim new_file As Variant
Dim p_file As Variant
p_file = ActiveWorkbook.Name
Columns("A:I").Copy
Workbooks.Add
new_file = ActiveWorkbook.Name
Columns("A:I").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone
_
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False

Workbooks(p_file).Activate
Sheets("card").Select
Columns("A:I").Copy
Workbooks(new_file).Activate
Sheets("Sheet2").Select
Columns("A:I").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone
_
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Range("D1").Select
Application.CutCopyMode = False
Sheets("Sheet1").Select

ActiveWorkbook.SaveAs Filename:= _
"c:\Books\" & Format(Date, "mm-dd-yyyy") & " Sunday Worksheet.xls" ' _
FileFormat:=xlNormal, Password:="", WriteResPassword:=""' _
ReadOnlyRecommend:=False, CreateBackup:=False
ActiveWorkbook.Close
returnvalue = MsgBox("Don't Forget To Put All Deposit Slips In Th
Book", 64, "Secretary's Message")
If returnvalue = 1 Then 'User chose OK

End If
End Su

 

[EMoe]

OK,

Sorry for so many replies.

It works!

After my last reply, (I finally began to think); I placed that code in
the module section, out of this workbook, and it worked fine.

Well I got an error at first, and it pointed me to
Sheets("Sheets1").Select. I just took the s out of the sheets1, and
bingo, it flew.

Thanks again for all of your support. 3

EMoe

 

[Norie]

Not sure what you mean by what is it set to

Well is it a variable?

Code
-------------------

Sub test()
Dim new_file As Workbook
Dim p_file As Workbook

Set p_file = ActiveWorkbook
Set new_file = Workbooks.Add

p_file.activsheet.Columns("A:I").Copy

new_file.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteFormats
new_file.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues

p_file.Sheets("card").Columns("A:I").Copy
new_file.Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteFormats
new_file.Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False


new_file.SaveAs Filename:= _
"c:\Books\" & Format(Date, "mm-dd-yyyy") & " Sunday Worksheet.xls" ' _
FileFormat:=xlNormal, Password:="", WriteResPassword:=""' _
ReadOnlyRecommend:=False, CreateBackup:=False
new_file.Close
MsgBox("Don't Forget To Put All Deposit Slips In The Book", 64, "Secretary's Message")

End Sub