Copy Past of sheets.

[vmjan02]

I have this code and is all perfect, then only think is it is taking time as Filling cells

Is there a way as to reduce the time take
As it creates a new workbook and then moves 2 sheets form old to new.

Sub SaveFileMN()
Dim sFilename As String

Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Activate
sFilename = Sheets("Email").Range("F5").Value & ".xlsx"

Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "ISFAtt" & "\" & sFilename
Application.Calculation = xlCalculationManual
Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("Summary_US").Copy Before:=Workbooks(sFilename).Sheets(1)
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("US Intraday Hourly - EOD ").Copy After:=Workbooks(sFilename).Sheets("Summary_US")
End sub

 

[johnnyL]

Try this:

Sub SaveFileMN()
Dim sFilename As String

Application.ScreenUpdating = False          '<---------

Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Activate
sFilename = Sheets("Email").Range("F5").Value & ".xlsx"

Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "ISFAtt" & "\" & sFilename
Application.Calculation = xlCalculationManual
Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("Summary_US").Copy Before:=Workbooks(sFilename).Sheets(1)
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("US Intraday Hourly - EOD ").Copy After:=Workbooks(sFilename).Sheets("Summary_US")

Application.ScreenUpdating = True          '<-------
End sub

 

[vmjan02]

Try this:

Sub SaveFileMN()
Dim sFilename As String

Application.ScreenUpdating = False          '<---------

Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Activate
sFilename = Sheets("Email").Range("F5").Value & ".xlsx"

Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "ISFAtt" & "\" & sFilename
Application.Calculation = xlCalculationManual
Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("Summary_US").Copy Before:=Workbooks(sFilename).Sheets(1)
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("US Intraday Hourly - EOD ").Copy After:=Workbooks(sFilename).Sheets("Summary_US")

Application.ScreenUpdating = True          '<-------
End sub

sure will give it a try, tomorrow. thanks

 

[vmjan02]

Try this:

Sub SaveFileMN()
Dim sFilename As String

Application.ScreenUpdating = False          '<---------

Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Activate
sFilename = Sheets("Email").Range("F5").Value & ".xlsx"

Workbooks.Add
'Saving the Workbook
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "ISFAtt" & "\" & sFilename
Application.Calculation = xlCalculationManual
Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("Summary_US").Copy Before:=Workbooks(sFilename).Sheets(1)
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value

Set w1 = Workbooks("Fiserv Intraday Generator Hourly V1.xlsb")
Set W2 = Workbooks(sFilename)
w1.Sheets("US Intraday Hourly - EOD ").Copy After:=Workbooks(sFilename).Sheets("Summary_US")

Application.ScreenUpdating = True          '<-------
End sub

I tried it, but still it is taking time, is there any other alternative..

 

[johnnyL]

Try This:

Sub SaveFileMN()
    Dim sFilename As String
'
    Application.ScreenUpdating = False
'
    Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Activate
    sFilename = Sheets("Email").Range("F5").Value & ".xlsx"
'
    Workbooks.Add
'
'   Saving the Workbook
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "ISFAtt" & "\" & sFilename
'
    Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Worksheets(Array("Summary_US", "US Intraday Hourly - EOD ")).Copy Before:=Workbooks(sFilename).Sheets(1)
'
    Application.ScreenUpdating = True
End Sub

 

[vmjan02]

it is f

Try This:

Sub SaveFileMN()
    Dim sFilename As String
'
    Application.ScreenUpdating = False
'
    Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Activate
    sFilename = Sheets("Email").Range("F5").Value & ".xlsx"
'
    Workbooks.Add
'
'   Saving the Workbook
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "ISFAtt" & "\" & sFilename
'
    Workbooks("Fiserv Intraday Generator Hourly V1.xlsb").Worksheets(Array("Summary_US", "US Intraday Hourly - EOD ")).Copy Before:=Workbooks(sFilename).Sheets(1)
'
    Application.ScreenUpdating = True
End Sub

It is much faster, but hw to convert it to values after copy. in this case we just need to convert sheet "Summary_US" to vales after copy to new file

 

[vmjan02]

hi I modified a bit and it is all perfect and faster. thanks for the support

Sub SaveFileA()
Dim sFilename As String

    
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
'
    Workbooks("Fiserv Intraday Generator Hourly.xlsb").Activate
    sFilename = Sheets("Email").Range("C5").Value & ".xlsx"
'
    Workbooks.Add
'
'   Saving the Workbook
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "ISFAtt" & "\" & sFilename
    Application.DisplayAlerts = True
'
    Workbooks("Fiserv Intraday Generator Hourly.xlsb").Worksheets(Array("Summary_", "Fiserv US Intraday Hourly - EOD")).Copy Before:=Workbooks(sFilename).Sheets(1)
    Workbooks(sFilename).Activate
    Sheets("Summary_").Select
    Cells.Select
    Range("E13").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("E13").Select

    Application.ScreenUpdating = True

ActiveWorkbook.Save
ActiveWorkbook.Close
Application.Calculation = xlCalculationAutomatic
Call FilePathA
Sheets("Report").Select

 

[johnnyL]

Happy to hear that it is working faster for you now.