Copy data from Excel sheet to different files

Question

I have an excel sheet that contains huge data. The data is organized as follows: A set of 7 columns and n rows; as in the table, and 1000 of these tables are placed horizontally with an empty column for separation. Screenshot below ..

...

I just want the data of each table to be stored in a different file. Manually it would be needed! So, is there a macro or something with which I could automate this task. I'm not very good at writing macros or any VBA stuff.

Thanks,

Answer 1

Tony has a valid moment when he speaks

If a table starting with C1 ends at line 21, does the next table begin with C23? If a table starting with K1 ends at line 15, does the next table begin with K17 or K23?

So, here is the code that will work in any conditions. Data is set horizontally or vertically.

DATA SNAPSHOT

CODE

'~~> Change this to the relevant Output folder Const FilePath As String = "C:\Temp\" Dim FileNumb As Long Sub Sample() Dim Rng As Range Dim AddrToCopy() As String Dim i As Long On Error GoTo Whoa Application.ScreenUpdating = False Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlTextValues) If Not Rng Is Nothing Then AddrToCopy = Split(Rng.Address, ",") FileNumb = 1 For i = LBound(AddrToCopy) To UBound(AddrToCopy) ExportToSheet (AddrToCopy(i)) Next i End If MsgBox "Export Done Successfully" LetsContinue: Application.ScreenUpdating = True Exit Sub Whoa: MsgBox Err.Description Resume LetsContinue End Sub Sub ExportToSheet(rngAddr As String) Range(rngAddr).Copy Workbooks.Add ActiveSheet.Paste ActiveWorkbook.SaveAs Filename:= _ FilePath & "Output" & FileNumb & ".csv" _ , FileFormat:=xlCSV, CreateBackup:=False Application.DisplayAlerts = False ActiveWorkbook.Close Application.DisplayAlerts = True FileNumb = FileNumb + 1 End Sub 

NOTE The above code will only work for cells with text values . For cells with numeric values you should use

 Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants, xlNumbers) 

And for alpha-numeric values (as in your question above), use this

 Set Rng = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants) 

NTN

Sid

Answer 2

As long as there is an empty row and an empty column around any datasets, this will use the AREAS () method to put them in separate books.

As in the previous example, it saves the CSV, but of course you can save it however you want.

 Option Explicit Sub ExportDataGroups() Dim fPATH As String, Grp As Long, DataRNG As Range fPATH = "C:\Path\Where\I\Want\My\Files\Saved\" 'remember the final \ Application.ScreenUpdating = False Set DataRNG = ActiveSheet.UsedRange For Grp = 1 To DataRNG.Areas.Count DataRNG.Areas(Grp).Copy Sheets.Add Range("A1").PasteSpecial ActiveSheet.Move ActiveWorkbook.SaveAs Filename:=fPATH & "-" & Format(Grp, "0000") & ".csv", _ FileFormat:=xlCSV, CreateBackup:=False ActiveWorkbook.Close Next Grp MsgBox "A total of " & Grp & " files were created" Application.ScreenUpdating = True End Sub 

Answer 3

In response to my comment, you indicate: "File name, I never thought about it. At the moment, there can be anything." From bitter experience, I can tell you that dealing with thousands of files with system names is a nightmare. Now you need to fix the problem with the name.

I am also nervous about AddrToCopy = Split(Rng.Address, ",") . Rng.Address will look like this: "$ C $ 1: $ I $ 16, $ K $ 1: $ Q $ 16, $ S $ 1: $ Y $ 16, $ C18 $ I $ 33, $ K $ 18: $ Q $ 33, $ S $ 18 : $ Y $ 33, ... ". If you search on the Internet, you will find sites that tell you that Rng.Address has a maximum length of 253 characters. I do not believe that is right. In my experience, Rng.Address truncated to its full subrange. My experiments were with Excel 2003, but I found that I noticed on the Internet that this limitation was fixed in later versions of Excel. You check Rng.Address lot Rng.Address your version of Excel! I am not familiar with Jerry Bocker, although he offers an interesting solution. Sid Rout always produces great code. If there is a problem, I am sure they can fix it.

However, the real purpose of this “answer” is to say that I would divide this problem into three. This has many advantages and no disadvantages that I know of.

Step 1. Create a new sheet, TableSpec , with the following columns:

 A Worksheet name. (If tables are spread over more than worksheet) B Range. For example: C1:I16, K1:Q16 C - I Headings from table. For example, AAPL, Open, High, Low, Close, Volume, AdjClose 

Step 2. Check the TableSpec ; for example, are all tables listed? Think about the file name and add column H to contain it. I read one of your comments so that you could "AAPL" as the file name for the first table, in which case you could set H2 to "= C2". Is "AAPL" unique? You may have a serial number. There are many options you can think of before creating any files.

Step 3. The worksheet TableSpec now provides all the information needed to create your files. You can remove most of the content and test the file creation code on a couple of lines.

I hope you see the benefits of this stepwise approach, in part if your VBA is weak. Good luck.