Hello dear community,
I am totally a newbie with macros on excel but would really appreciate your support and genius minds.
The task is not the easiest (for me definitely not), what I would need this beautiful Macro to do is:
First Save a new version of the excel as an excel (xlsm i guess)
Open that excel
Perform a couple of actions such as copy paste values and delete tabs.
I'm sure that it's much easier than i've done here so please feel free to completely re-adjust, I'm sure 80% of it is wrong as I copied it from a Excel to PDF macro. The only thing that really works right now is the green text as i manually recorded it.
Sub Macro5()
'
' Macro5 Macro
'
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "dd.mm.yyyy")
'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"
'create default name for savng file
strFile = Replace(ActiveWorkbook.Name, ".xlsm", "") & strTime
strPathFile = strPath & strFile
'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename & ".xlsm", FileFormat:=52
Range("B3:K10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("M:AB").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("5) Client Summary").Select
Range("B6:E6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(Array("1) General", "2) Suppliers", "3) RFP", "4) RFP Control", _
"7) Budget Control", "Links to Master")).Select
Sheets("2) Suppliers").Activate
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
exitHandler:
Exit Sub
End Sub
Thank you so very much in advance,
Kallia
I am totally a newbie with macros on excel but would really appreciate your support and genius minds.
The task is not the easiest (for me definitely not), what I would need this beautiful Macro to do is:
First Save a new version of the excel as an excel (xlsm i guess)
Open that excel
Perform a couple of actions such as copy paste values and delete tabs.
I'm sure that it's much easier than i've done here so please feel free to completely re-adjust, I'm sure 80% of it is wrong as I copied it from a Excel to PDF macro. The only thing that really works right now is the green text as i manually recorded it.
Sub Macro5()
'
' Macro5 Macro
'
Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "dd.mm.yyyy")
'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"
'create default name for savng file
strFile = Replace(ActiveWorkbook.Name, ".xlsm", "") & strTime
strPathFile = strPath & strFile
'use can enter name and
' select folder for file
myFile = Application.GetSaveAsFilename
ActiveWorkbook.SaveAs Filename & ".xlsm", FileFormat:=52
Range("B3:K10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("M:AB").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("5) Client Summary").Select
Range("B6:E6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(Array("1) General", "2) Suppliers", "3) RFP", "4) RFP Control", _
"7) Budget Control", "Links to Master")).Select
Sheets("2) Suppliers").Activate
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
exitHandler:
Exit Sub
End Sub
Thank you so very much in advance,
Kallia