locked
Split data as per row count with title row and save to reference folder format 1997-2003 RRS feed

  • Question

  • Hi there

    there is almost i have more than 2800 numbers in sheet 1, I want all 2800 numbers split into sheets per 190 each sheet and save at desktop folder (new folder) in EXCEL 97-2003 workbook format…sheets name should be like 1,2 ,3 4, so on….

    folder for save: C:\Desktop\New folder (4) 

    Nabeel Gondal




    Thursday, October 27, 2016 8:23 AM

Answers

  • dear deepak

    i am using office 2010..??

    any how thanks a lot for help deepak and Andreas both of you,  really appreciate you kind help and patience & time

    Nabeel 

    • Marked as answer by Nabeel Gondal Friday, October 28, 2016 9:21 AM
    Friday, October 28, 2016 9:21 AM

All replies

  • Sub Test()
      Const Size = 190
      
      Dim i As Long, j As Long
      Dim Wb As Workbook
      Dim Source As Range
      
      '1st number
      Set Source = Range("A1")
      'In steps down till last number
      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
        Source.Offset(j * Size).Resize(Size).EntireRow.Copy Wb.Sheets(1).Range("A1")
        'Counter
        j = j + 1
        'Save and close
        Wb.SaveAs "C:\Desktop\New folder (4)\WhatEver" & j & ".xlsx"
        Wb.Close
      Next
    End Sub


    Thursday, October 27, 2016 10:18 AM
  • dear Andreas

    thanks for helping me ...

    When i run this code showing error 400 and than stoped and i have data title to A1 to D1, pl help

    Nabeel Gondal


    Thursday, October 27, 2016 3:07 PM
  • When i run this code showing error 400 and than stoped and i have data title to A1 to D1, pl help

    Hi Nabeel,

    I've changed the code above to include the whole row, please try again.

    If the same error occurs I have to view your file. Please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    A macro to anonymize data in selected cells can be downloaded here:
    https://dl.dropboxusercontent.com/u/35239054/modAnonymize.bas

    Andreas.

    Thursday, October 27, 2016 3:59 PM
  • Dear Andreas

    i created this link first time for file, i do not know its works for you or not, pl confrim

    https://www.dropbox.com/s/dvd2tsp0vdskobr/ERD_APN%20%282%29.xlsx?dl=0

    Nabeel 

    Thursday, October 27, 2016 5:34 PM
  • i created this link first time for file, i do not know its works for you or not, pl confrim

    https://www.dropbox.com/s/dvd2tsp0vdskobr/ERD_APN%20%282%29.xlsx?dl=0

    Got the file, thank you. And I can run my macro without issues in that file...

    Anyway, you did not mention that you have headers and the code below saves the files in the same path as the source file. Try to run the code in your sample file, works perfectly on my PC.

    Andreas.

    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.SaveAs ThisWorkbook.Path & "\WhatEver" & j & ".xlsx"
        Wb.Close
      Next
    End Sub

    Thursday, October 27, 2016 7:48 PM
  • Dear Andreas

    yes you are right this is now working but all the sheet are saving in desktop instead of any folder i tried to change it with reference folder like below its shows again same error (400) and files are not in saving 1997-2003 format and also make little amend that auto content fit in column A with thnx..

    Nabeel

    Wb.SaveAs ThisWorkbook.Path & " C:\Desktop\New folder (4)\WhatEver" & j & ".xlsx"
    Thursday, October 27, 2016 8:28 PM
  • Hi Nabeel,

    you made a mistake in above mentioned line of code. ThisWorkbook.path will give you the whole path.

    then also you add the path from the root.

    also it will not save the file in 1997-2003 format.

    so you need to remove the Thisworkbook.path and add your own path and also need to specify the file format to be saved in 1997-2003. also you need to change the extension of file as .xls

    also you need to disable the check compatibility.

    I update the code posted by Andreas Killer.

    you can see it .

    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
    
        Wb.SaveAs "C:\Users\v-padee\Desktop\demoexcelfiles\" & 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.


    Friday, October 28, 2016 4:34 AM
  • Good day Deepak

    when i run your mention code same error occur (400) and i have  change the path with C:\Users\nabeelak\Desktop

    can this is possible that code create itself folder at desktop to avoid such problem & save the files in it...

    Nabeel 



    Friday, October 28, 2016 4:55 AM
  • Hi Nabeel,

    it looks like you are writing a incorrect path and because of this reason you get this error.

    you had mentioned that you change path mentioned by me with yours.

    below is your path.

    C:\Users\nabeelak\Desktop


    below is my path

    C:\Users\v-padee\Desktop\demoexcelfiles\"

    in my path "demoexcelfiles" is a folder in which I want to save the file.

    I did not find the folder in your path.

    simply if you don't know how to write path then select the folder in which you want to save the file.

    Right click on that folder. click on the last option named "Properties"

    it will give you the Folder path you need to copy the path from there in the code. it will look like below.

    you had asked that it possible to create folder in this code.

    yes it is possible.

    below mentioned code use to create folder if folder is not already exist.

    Sub demo()
    If Len(Dir("C:\Users\v-padee\Desktop\xldemo", vbDirectory)) = 0 Then
       MkDir "C:\Users\v-padee\Desktop\xldemo"
       Else
       MsgBox ("Folder already exist..")
    End If
    End Sub
    

    in this code xldemo is the folder.

    so first it will check if that location has already this folder or not.

    if there is no any folder with this name it will create a new folder.

    if there is already folder exist on that location then it will display megbox that "Folder already exist..".

    then you just need to pass the same path to save as your excel files.

    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.

    Friday, October 28, 2016 5:18 AM
  • dear Deepak

    there was mistake from my side (i was checking the wrong folder) sorry for that, now that matter resolved

    now only problem is  on every new create file this is asking "compatibility checker window" pl resolve this

    

    Nabeel 





    Friday, October 28, 2016 5:53 AM
  • Hi Nabeel ,

    you had mentioned that you want to autofit content in all columns by VBA code because manual operation is lengthy and tedious work.

    you can use Range.AutoFit Method.

    Below is an example of that.

    Worksheets("Sheet1").Columns("A:I").AutoFit
    
    

    you can also set autofit on range like mentioned below.

    Worksheets("Sheet1").Range("A1:E1").Columns.AutoFit
    
    

    you can also try to implement this one line of code in above mentioned whole code to split data.

    just add this line before you try to save workbook and after pasting the data.

    you need to pass the range according to your data. like you have 1 row for Headers , 190 row for data and 4 columns. so you need to pass the range according to that i.e. "A1:D191".

    For more information regarding this method please visit the link mentioned below.

    Range.AutoFit Method (Excel)

    let me know that your issue is solved or not. if it solved then mark the suggestion as an answer which solved 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.

    Friday, October 28, 2016 7:23 AM
  • Dear Deepak

    now only problem is  on every new create file this is asking "compatibility checker " i click on continue than it is saving, pl resolve this , all other things working accordingly now

    Nabeel 

    Friday, October 28, 2016 7:33 AM
  • Hi Nabeel,

    I already added a line to ignore that popup and without showing that popup file will be saved.

    Following line is used for that.

    Wb.CheckCompatibility = False
    

    but it seems that it is not working as expected.

    please visit the link mentioned below to get more information regarding this issue.

    Why is .CheckCompatibility = False not working?

    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.

    Friday, October 28, 2016 8:17 AM
  • dear Deepak

    in mention link just told about the reason of compatibility checker neither that solution, thing which i cannot go with it because when i have data more then 10k i cannot click on every check to continue, pl find some way for me..!!

    Nabeel 

    Friday, October 28, 2016 8:34 AM
  • Hi nabeel,

    I try to find the alternative of Workbook.checkcompatibility but currently there is no alternative available for that.

    I can understand that you are using latest version of Excel and trying to save file in older version.

    so that you can access this file in that older version.

    if it is possible you can try to upgrade the version of Excel on that machine that have older version installed.

    you can provide your feedback regarding this issue to Excel User Voice.

    after getting enough suggestion they can try to correct this issue.

    Excel User Voice

    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.

    Friday, October 28, 2016 9:09 AM
  • dear deepak

    i am using office 2010..??

    any how thanks a lot for help deepak and Andreas both of you,  really appreciate you kind help and patience & time

    Nabeel 

    • Marked as answer by Nabeel Gondal Friday, October 28, 2016 9:21 AM
    Friday, October 28, 2016 9:21 AM