I have table of about 11k rows that are spread out over nearly 200 sheet tabs. I need to copy all of them into one sheet to create an upload file. How do I best go about this task without spending hours and hours of manually copying and pasting?
price list-short.xlsx (17.8 KB)
so far the OP has already a single .ods -file ? … but unfortunetly with the data spread over ~200sheets? cannot see the advantage to putting into (another) zip-container around.
How are the sheets named?
Do they all have the same column headers?
The sheets are just named “table 1”, “table 2” and so on. I have removed all headers from the top of each sheet.
Can you attach your doc shortened to three sheets each with ~20 Rows of Data (please no confidentional data, but with formulas, if there ) to your original Question
Hi, I’ve added a shortened version of the file to the OP.
Looking at your sample file it does not appear to contain any formulae or other dynamic information — forgive me if I missed it. If I were to tackle such a merger I would dump the tabs out as CSV files then concatenate them files together into a new CSV file and then open that new file. Of course this approach would itself become irksome if it were necessary to repeat the merger.
1 Like
price list-short.xlsx (32.0 KB)
Have a look at the sheet alltables. Extend the lines down, until you reach an error. I inserted four auxilliary columns in the beginning. For each sheet, a last empty line is inserted. If you want to remove then, filter for rowisempty==0
Good luck,
ms777
Hi ms777,
thanks for your input. I have copied your lines over to my file and extended the lines down.
I get the error once the end of one table is reached and I have to manually rename the first column to the next table number. That would mean I have to do that about 200 times till i have combined all tables into one.
Did I make a mistake here?
… the table number/name in column C should automatically increment, once an empty line is reached.
Do your sheets exactly follow the naming “Table $i”, with $i an integer number? This is required… If not, things get a bit more complicated.
I am away from my computer until mid next week, so I cannot help too much
What is the formula in A108?
…and the formula in A109
Hi there thanks to you asking this I realized that only a few of rows contained formulas in the first to columns. I went to insert them from your example and pulled down now it increments the table numbers. Looks like it should work now. Will let you know. Thanks for your help!!! It will safe a lot of time!
See also the user code provided with the attached example.
I would never try to store anything to xlsx if not I need to send the document to a poor man who can’t afford LibreOffice.
In this case I also assume, the included code would not survive.
mergeSheets.ods (43.4 KB)
@Lupp It seems to me that this time we can do without .select(), .getTransferable() and .insertTransferable()
Sub ColectAllSheetsToNewOne()
Dim oDoc As Variant, oSheets As Variant, oSheet As Variant, oCursor As Variant
Dim aCellAddress As New com.sun.star.table.CellAddress
Dim aRangeAddress As New com.sun.star.table.CellRangeAddress
Dim i As Long, nStartRow As Long
oDoc = ThisComponent
oSheets = oDoc.getSheets()
If oSheets.hasByName("_Result") Then oSheets.removeByName("_Result")
oSheets.insertNewByName("_Result", 0)
aCellAddress = oSheets.getByIndex(0).getCellByPosition(0, 0).getCellAddress()
nStartRow = 0
For i = 1 To oSheets.getCount()-1
Rem If no need first row from each sheet (only from first) then unrem next row
Rem If (i<>1) Then nStartRow = 1
oSheet = oSheets.getByIndex(i)
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
aRangeAddress = oCursor.getRangeAddress()
aRangeAddress.StartRow = nStartRow
oSheet.copyRange(aCellAddress, aRangeAddress)
aCellAddress.Row = aCellAddress.Row + aRangeAddress.EndRow + 1 - nStartRow
Next i
MsgBox "!" & (oSheets.getCount()-1) & " sheets (" & aCellAddress.Row & " rows of data) successfully copied to sheet _Result"
End Sub
Hi Lupp,
I have just opened your file but did not find that anything had been added to it.
You can copy the code to “My Macros” and use it with any kind of spreadsheet document.
However, I would prefer my own code which preserves the information about the former sheet name.