Adding Worksheets to an Array

[simora]

I'm trying to add 2 worksheets to an array.
I know the first sheetname is "Sheet1" however, the other sheetname is derived from the current month.
My code to determine it is like this;



Dim MySht
Worksheets(MySht).Activate


The 2nd sheetname is the name of the current month

How do I put both sheets into an array and copy them to a new Workbook and save it ?

[Paul_Hossler]

Try this in the original WB with the 2 worksheets





Sub Test()

Worksheets(Array("Sheet1", Format(Now, "mmmm"))).Copy

ActiveWorkbook.SaveAs Filename:= _
Environ("userprofile") & "\Documents\Sheet1AndMonth.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End Sub

[simora]

Thanks Paul_Hossler (http://www.vbaexpress.com/forum/member.php?9803-Paul_Hossler)

Finally figured it out.

Here's my code:



Dim MySht
Dim strSaveName As String
Dim b As String
MySht = Format(Date, "MMMM") ' Month
strSaveName = Worksheets("Sheet1").Range("D1").Value
Sheets("Sheet1").Select
Sheets(MySht).Select
b = MySht
Sheets(Array("Sheet1", b)).Copy
ActiveWorkbook.SaveAs strSaveName


Thanks for the code.

[mdmackillop]

Reduces to

Sheets(Array("Sheet1", Format(Date, "MMMM"))).Copy
ActiveWorkbook.SaveAs Worksheets("Sheet1").Range("D1").Value

[simora]

Hi mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop) Thanks :

Now this has me stumped.
The code worked perfectly, until I added formatting and data to sheet1.
Is there any reason why this workbook now DOES NOT WORK?
I get an error " Method 'Copy' of object 'Sheets' failed . Any ideas why this error ?

[Paul_Hossler]

Using D1 generated an illegal file name

4/30/2017 2:36:36 AM.xls


19050

[mdmackillop]

Something like

Sheets(Array("Sheet1", Format(Date, "MMMM"))).Copy
ActiveWorkbook.SaveAs Format(Worksheets("Sheet1").Range("D1").Value, "dd_mmmm_yyyy") & ".xls"

[simora]

Hi Guys.

My problem is that when I run the code on newly created blank sheets, the code works, however, I'm trying to determine what is it that stops the code from working on my sheet1.
I've removed all the macros and data from my Sheet1, but still I get an error. There's something about my Sheet1 that causes the error.

Thanks guys.

[simora]

I've isolated the problem to one in which some of the formatting can actually cause the error.
I get an error " Method 'Copy' of object '_WorkSheet' failed is created.
I'm using Office 2007 and trying to save an .xls file, but somehow VBA doesn't allow this without a work around.

I get the error even when I try this simple code:



Application.DisplayAlerts = False
ActiveWorkbook.Sheets("Sheet1").Copy


Any suggestions for a Workaround.

[mdmackillop]

Both these work for me on your sample workbook

Sub test()
Application.DisplayAlerts = False
ActiveWorkbook.Sheets("Sheet1").Copy
ActiveWorkbook.SaveAs Format(Worksheets("Sheet1").Range("D1").Value, "dd_mmmm_yyyy") & ".xls"
End Sub


Sub vbcopy()
Sheets(Array("Sheet1", Format(Date, "MMMM"))).Copy
ActiveWorkbook.SaveAs Format(Worksheets("Sheet1").Range("D1").Value, "dd_mmmm_yyyy") & ".xls"
End Sub

[Paul_Hossler]

I've isolated the problem to one in which some of the formatting can actually cause the error.
I get an error " Method 'Copy' of object '_WorkSheet' failed is created.
I'm using Office 2007 and trying to save an .xls file, but somehow VBA doesn't allow this without a work around.

I get the error even when I try this simple code:

Any suggestions for a Workaround.



The 2 lines work using Excel 2016, Win 10 for me

[Paul_Hossler]

Go back to the original that works and add one bit of formatting at a time to see if you can figure out what formatting causes it to fail

[simora]

Hi:

Are you guys using the ABook1.xls (http://www.vbaexpress.com/forum/attachment.php?attachmentid=19049&d=1493518299) workbook. That is the one that is NOT working for me.
When I run the code, line by line, I can see the new Book1 created in my Taskbar.
I cant ever click on it to get it to open, and Excel is showing "Saving" continuously.
Excel is locked up at that point, and I have to use the Task Manager to get out of it.

I wish that I could go back to re-doo the formatting, but this sheet has worked in the past and is replicated too many times on different systems.
Any other ideas ?

[Paul_Hossler]

By any chance do you use Malwarebytes?

With Creator's Update there was some unforeseen interaction with Excel that had similar symptoms

[simora]

Yes ! I m using Malwarebytes. Will check on it later as I have to run out for a while.

THANKS for the input. This is starting to drive me crazier.

[Paul_Hossler]

Settings, Protection, Manage Protected Applications

Unselected Excel (plus others if need be)

19052

19053

They'll probably fix it soon (I hope)

[Aussiebear]

I see you are getting better (neater) with your circle work Paul. :devil2:

[Paul_Hossler]

I see you are getting better (neater) with your circle work Paul. :devil2:

They're round -- your eyes are crooked

[simora]

Paul_Hossler

Aussiebear (http://www.vbaexpress.com/forum/member.php?9803-Paul_Hossler)

mdmackillop (http://www.vbaexpress.com/forum/member.php?87-mdmackillop)



Just wanted to say thanks to all who chimed in. Had to take a break for a little health issue. Ok now.
I deleted MalwareBytes and re-installed Office 2007.
Things now work as expected except for a CDO issue with Win 8.1