none
sheet save with suggested name RRS feed

  • Question

  • 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

    Friday, December 30, 2016 8:31 AM

Answers

  • Hi Nabeel Gondal,

    just put the inputbox outside the loop. so it will not ask the sheet name every time.

    below is edited code

    Sub Test()
      Const Size = 190
     
      Dim i As Long, j As Long
      Dim Wb As Workbook
      Dim Source As Range, Header As Range
      Dim strMySheetName As String
     strMySheetName = InputBox("Enter Sheet Name:")
      '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")
        
        If strMySheetName <> "" Then Wb.Sheets(1).Name = strMySheetName
        '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
    

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Nabeel Gondal Tuesday, January 3, 2017 5:53 AM
    Tuesday, January 3, 2017 5:28 AM
    Moderator

All replies

  • Hi Nabeel -

    If I understand your meaning, you want the following:

    (1) to have a numbered file such as "1.xls" in the path "C:\Users\nabeelak\Desktop\New folder (2)\".
    This is working as you want.

    (2) in this new workbook (named 1.xls, for example), to have a worksheet with the tab named something you entered instead of the default "Sheet 1".
    -> This is not working and what you want help with.

    If (2) is what is needed, here are changes I might insert just before your    'Counter line:

    strMySheetName = Inputbox("Enter the Worksheet Name")
    If strMySheetName <>"" then Wb.Sheets(1).Name =  strMySheetName
    'Counter


    -MainSleuth

    Friday, December 30, 2016 5:48 PM
  • Hi Nabeel Gondal,

    I think that suggestion given by MainSleuth can solve your issue.

    please check the suggestion given by him and if you think that it solves your issue then mark his suggestion as an answer.

    if you have any further question regarding above mentioned issue then let us know about that.

    we will try further to solve your issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, January 1, 2017 11:37 PM
    Moderator
  • Hi MainSleuth

    thanks for help, may you got my point in pullet (2), when i enter your given VBA before "counter" and run VBA, error shown "variable not defined"

    i just make name as below:

    strMySheetName = Inputbox("Email")
    If strMySheetName <>"" then Wb.Sheets(1).Name =  strMySheetName

    'Counter

    Nabeel Gondal

    Monday, January 2, 2017 6:10 AM
  • Hi deepak

    thanks for follow up, there is some still issue may be resolve by MainSleuth

    Nabeel Gondal


    Monday, January 2, 2017 6:53 AM
  • Hi Nabeel Gondal,

    it's looks like you just copy and paste the code and forgot to declare variable.

    you need to declare "strMySheetName " as string.

    please add the below line in your code at the starting of the sub where you had declare other variables.

    dim strMySheetName as string

    it will solve the error.

    if you find that your issue is solved then mark the appropriate suggestion as an answer.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 3, 2017 3:00 AM
    Moderator
  • Hi Deepak

    i made changes as you said, its working but this is asking for sheet name for each sheet(sheet name window reappear for next sheet name), i want when i give one name all sheet should create with that name  not need to ask again..pl look into

    Regards

    Nabeel 

    Tuesday, January 3, 2017 5:08 AM
  • Hi Nabeel Gondal,

    just put the inputbox outside the loop. so it will not ask the sheet name every time.

    below is edited code

    Sub Test()
      Const Size = 190
     
      Dim i As Long, j As Long
      Dim Wb As Workbook
      Dim Source As Range, Header As Range
      Dim strMySheetName As String
     strMySheetName = InputBox("Enter Sheet Name:")
      '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")
        
        If strMySheetName <> "" Then Wb.Sheets(1).Name = strMySheetName
        '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
    

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Nabeel Gondal Tuesday, January 3, 2017 5:53 AM
    Tuesday, January 3, 2017 5:28 AM
    Moderator
  • Hi Deepak

    this is great now and working as per my need, thnx for help & much appropriated help

    Nabeel Gondal

    Tuesday, January 3, 2017 5:53 AM
  • Glad this worked; Deepak, thanks for the followup to close questions as I was away.

    -J


    -MainSleuth

    Tuesday, January 3, 2017 6:15 AM