create and rename new sheet using save as using macros

[Amit Modi]

[
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+l
'
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
Range("G6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Sheets("Sheet1").Name = Sheet4.Range("G6").Text
Range("B32").Select
Application.CutCopyMode = False
ChDir "C:\Users\moa7ahm\Desktop"
Range("G6").Select
ActiveCell.FormulaR1C1 = Sheet4.Range("G6").Text
Range("G10").Select
ActiveWorkbook.SaveAs Filename:="C:\Users\moa7ahm\Desktop\Worksheetname.xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range("B13").Select
End Sub ]

hi gys I am trying to use upper macros to create and save as the file but I want to use same sheet name for work book.but I can't,pease hep me if anyone has suitable macros or vba code to keep both name sheet and workbook same,,in my case my sheet name is in range G6 that's also my sheet's name now every time my G6 value is different so upper showing macros doesn't work if I change.so my problem is related to workbook name and I want to use name as in range("G6").please help

 

[Smallman]

Hi Amit

I can't see your workbook and this is untested but conceptually this should work for you.

Sub SaveMe()
    [a1].CurrentRegion.Copy
    Workbooks.Add
    [a1].PasteSpecial xlPasteAll
    ActiveSheet.Name = [g6].Value
    ActiveWorkbook.SaveAs "C:\Users\moa7ahm\Desktop\" & [g6] & ".xlsx"
    ActiveWorkbook.Close False
End Sub

Take care

Smallman

 

[Amit Modi]

thank you I will try and if your help required then I will come to you with my file on this platform thank you very much