Here it is... thanks Ron.
Sub aUpdate_Daily()
Dim FilePathName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet
Dim TheActiveWindow As Window
Dim TempWindow As Window
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set Sourcewb = ActiveWorkbook
'Copy the sheets to a new workbook
'We add a temporary Window to avoid the Copy problem
'if there is a List or Table in one of the sheets and
'if the sheets are grouped
With Sourcewb
Set TheActiveWindow = ActiveWindow
Set TempWindow = .NewWindow
.Sheets(Array("Sheet10", "Sheet13")).Copy
End With
'Close temporary Window
TempWindow.Close
Set Destwb = ActiveWorkbook
' 'Change all cells in the worksheets to values if you want
For Each sh In Destwb.Worksheets
sh.Select
With sh.UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
Destwb.Worksheets(1).Select
Next sh
'Save the new workbook/Mail it/Delete it
FilePathName = Sheets("Data").Range("I23") & "\" &
Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$
& ".xls"
With Destwb
.SaveAs FilePathName
.Close SaveChanges:=False
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
"Ron de Bruin" wrote:
> Show me the code Mike that you use now
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
>
>
> "Mike R." <(E-Mail Removed)> wrote in message news:BDFD190D-A2D5-4A95-BC0E-(E-Mail Removed)...
> > Hi Ron,
> > For some reason, I am getting a "Subscript out of range" error on the Array
> > line of code. I did change the Sheets to my actual sheets I want to copy.
> >
> > Help...
> > Mike
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi mike
> >>
> >> See how i do it in this mail macro with a array
> >> http://www.rondebruin.nl/mail/folder1/mail3.htm
> >>
> >> I also create a temporary window to avoid a bug
> >> It is up to you if you want to use it
> >>
> >>
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >>
> >>
> >> "Mike R." <(E-Mail Removed)> wrote in message news:46C4A27F-BC16-4D42-B441-(E-Mail Removed)...
> >> > Hello,
> >> > I would like to copy two worksheets (formatting and all...) from one
> >> > workbook and create a new workbook with just those two sheets. Here is the
> >> > code I used to copy one worksheet, but I can not figure out how to do two. I
> >> > am not opposed to all new coding:
> >> >
> >> > ' save worksheet with this file name
> >> > FilePathName = Sheets("Data").Range("I23") & "\" &
> >> > Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$
> >> > & ".xls"
> >> > newsht = ActiveSheet.Copy
> >> > Set Wkb = ActiveWorkbook
> >> > Wkb.Sheets("Current Openings").Name = "Current Openings"
> >> > With ActiveWorkbook
> >> > .SaveAs Filename:=FilePathName
> >> > .Close False
> >> > End With
> >> >
> >> > Thank you in advance for your help!
> >>
>
Similar Threads
