sheet save with suggested name

[adeel1]

hello all

i am using below VBA for extracting sheet, i need amendment in it that this is saving all sheets with sheet 1 (name) but i want my name which i want suggest i want when i run macros window open and ask for sheet name which i made name sheets should saves with that...!!!

thanks

Option Explicit

Sub Test()
  Const Size = 190
 
  Dim i As Long, j As Long
  Dim Wb As Workbook
  Dim Source As Range, Header As Range
 
  '1st number
  Set Header = Range("A1")
  Set Source = Range("A2")
  'In steps down till last number
  Application.DisplayAlerts = False
  For i = 1 To Source.Offset(Rows.Count - Source.Row).End(xlUp).Row - Source.Row + 1 Step Size
    'New file
    Set Wb = Workbooks.Add(xlWBATWorksheet)
    'Copy this part
    Header.EntireRow.Copy Wb.Sheets(1).Range("A1")
    Source.Offset(j * Size).Resize(Size).EntireRow.Copy Wb.Sheets(1).Range("A2")
    'Counter
    j = j + 1
    'Save and close
    Wb.CheckCompatibility = False
    Application.ActiveSheet.Columns.AutoFit

    Wb.SaveAs "C:\Users\nabeelak\Desktop\New folder (2)\" & j & ".xls", FileFormat:=xlExcel8
    Wb.Close
    Application.DisplayAlerts = True
  Next
End Sub

 

[adeel1]

is there is any help..???? pl

 

[Chihiro]

Search for "Application.GetSaveAsFilename" method.

Or just use inputbox to store user input as string variable and use that instead of "j".