Hi Everyone
I am using a vba code to automate the process.. In which at the end I copy the last worksheet into new workbook and close the original workbook.. Now in this code I want to add one more step i.e. saving the new workbook at the same location of original workbook with the file name as "Batch 1 (current date dd-mm-yyyy stamp)" and if the new sheet is processed on same date then the batch number will be incremental and start from batch 1 on the next date i.e.
Batch 1 07-06-2017, Batch 2 07-06-2017, Batch 3 07-06-2017
Batch 1 08-06-2017, Batch 2 08-06-2017, Batch 3 08-06-2017
Like wise..
New workbook can be saved as normal excel file there will be no macros in new workbook. and original workbook will be closed without saving.
Regards
Mandar
My VBA code is as below
Sub GetData()
'
' GetData Macro
'
Application.ScreenUpdating = False
Sheets("Text Format").Visible = True
Sheets("INDEX").Visible = True
Sheets("2 ID per page").Visible = True
Sheets("INDEX").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("2 ID per page").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("2 ID per page").Select
Application.CutCopyMode = False
Sheets("2 ID per page").Copy
Range("A2").Select
Windows("tripti dental 2.xlsb").Activate
Selection.ClearContents
Range("A2").Select
Sheets("INDEX").Select
Range("A10").Select
Sheets("Data").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("N7").Select
Sheets("Text Format").Select
Range("L1").Select
Sheets("2 ID per page").Visible = False
Sheets("INDEX").Visible = False
Sheets("Text Format").Visible = False
Application.ScreenUpdating = True
ActiveWorkbook.Close savechanges:=False
End Sub
▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !
I am using a vba code to automate the process.. In which at the end I copy the last worksheet into new workbook and close the original workbook.. Now in this code I want to add one more step i.e. saving the new workbook at the same location of original workbook with the file name as "Batch 1 (current date dd-mm-yyyy stamp)" and if the new sheet is processed on same date then the batch number will be incremental and start from batch 1 on the next date i.e.
Batch 1 07-06-2017, Batch 2 07-06-2017, Batch 3 07-06-2017
Batch 1 08-06-2017, Batch 2 08-06-2017, Batch 3 08-06-2017
Like wise..
New workbook can be saved as normal excel file there will be no macros in new workbook. and original workbook will be closed without saving.
Regards
Mandar
My VBA code is as below
Sub GetData()
'
' GetData Macro
'
Application.ScreenUpdating = False
Sheets("Text Format").Visible = True
Sheets("INDEX").Visible = True
Sheets("2 ID per page").Visible = True
Sheets("INDEX").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("2 ID per page").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("2 ID per page").Select
Application.CutCopyMode = False
Sheets("2 ID per page").Copy
Range("A2").Select
Windows("tripti dental 2.xlsb").Activate
Selection.ClearContents
Range("A2").Select
Sheets("INDEX").Select
Range("A10").Select
Sheets("Data").Select
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents
Range("N7").Select
Sheets("Text Format").Select
Range("L1").Select
Sheets("2 ID per page").Visible = False
Sheets("INDEX").Visible = False
Sheets("Text Format").Visible = False
Application.ScreenUpdating = True
ActiveWorkbook.Close savechanges:=False
End Sub
▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !