PDA

View Full Version : Adding Worksheets to an Array



simora
04-23-2017, 05:30 PM
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
04-23-2017, 05:39 PM
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
04-23-2017, 06:15 PM
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
04-24-2017, 02:38 AM
Reduces to

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

simora
04-29-2017, 07:12 PM
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
04-29-2017, 11:40 PM
Using D1 generated an illegal file name

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


19050

mdmackillop
04-30-2017, 03:19 AM
Something like

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

simora
04-30-2017, 04:30 AM
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
04-30-2017, 05:02 AM
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
04-30-2017, 05:43 AM
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
04-30-2017, 05:53 AM
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
04-30-2017, 01:38 PM
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
04-30-2017, 03:06 PM
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
04-30-2017, 03:25 PM
By any chance do you use Malwarebytes?

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

simora
04-30-2017, 03:46 PM
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
04-30-2017, 04:35 PM
Settings, Protection, Manage Protected Applications

Unselected Excel (plus others if need be)

19052

19053

They'll probably fix it soon (I hope)

Aussiebear
04-30-2017, 06:15 PM
I see you are getting better (neater) with your circle work Paul. :devil2:

Paul_Hossler
04-30-2017, 07:42 PM
I see you are getting better (neater) with your circle work Paul. :devil2:

They're round -- your eyes are crooked

simora
05-13-2017, 11:23 AM
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