VBA Code For Copying Data From Table

[shinobi]

Hi

I am trying to find the most efficient way to write some VBA code that will achieve the following:

1) I have a large table of data on one sheet (four columns, but perhaps 2,000 rows).
2) Each row of data has a label in the first column that categorises the data in the rest of the row.
3) I have a separate sheet for each category.
4) I'd like to extract the data from the main table into the respective sheets (by label).
5) I'd end up with the category specific sheets with a table of data in each one.

Any ideas how best to do this? Not sure if looping through each row from top to bottom to identify category then copy/paste into relevant sheet is best. Or if there is a more efficient method (maybe using a pivot table; or using the data filter etc).

Thanks!

 

[Herakles]

Hi

I am trying to find the most efficient way to write some VBA code that will achieve the following:

1) I have a large table of data on one sheet (four columns, but perhaps 2,000 rows).
2) Each row of data has a label in the first column that categorises the data in the rest of the row.
3) I have a separate sheet for each category.
4) I'd like to extract the data from the main table into the respective sheets (by label).
5) I'd end up with the category specific sheets with a table of data in each one.

Any ideas how best to do this? Not sure if looping through each row from top to bottom to identify category then copy/paste into relevant sheet is best. Or if there is a more efficient method (maybe using a pivot table; or using the data filter etc).

Thanks!

What version of Excel are you using?

Is the data actually in a table?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[shinobi]

Hi

Am using Microsoft 365 (Excel version 2307). Am trying the XL2BB below....

Thanks!

Example.xlsx
	A	B	C	D	E
1	Account	Date	Description	Amount	Tag
2	Orange	01-Jan-23	xxxx	100.00	Sunday
3	Green	01-Jan-23	xxxx	75.00	Sunday
4	Blue	01-Jan-23	xxxx	56.25	Sunday
5	Blue	01-Jan-23	xxxx	42.19	Monday
6	Orange	01-Jan-23	xxxx	31.64	Monday
7	Red	02-Jan-23	xxxx	49.36	Monday
8	Black	02-Jan-23	xxxx	77.00	Friday
9	White	02-Jan-23	xxxx	120.12	Friday
10	White	02-Jan-23	xxxx	187.39	Friday
11	Red	02-Jan-23	xxxx	292.33	Friday
Transactions

 

[Herakles]

Hi

Am using Microsoft 365 (Excel version 2307). Am trying the XL2BB below....

Thanks!

Example.xlsx
	A	B	C	D	E
1	Account	Date	Description	Amount	Tag
2	Orange	01-Jan-23	xxxx	100.00	Sunday
3	Green	01-Jan-23	xxxx	75.00	Sunday
4	Blue	01-Jan-23	xxxx	56.25	Sunday
5	Blue	01-Jan-23	xxxx	42.19	Monday
6	Orange	01-Jan-23	xxxx	31.64	Monday
7	Red	02-Jan-23	xxxx	49.36	Monday
8	Black	02-Jan-23	xxxx	77.00	Friday
9	White	02-Jan-23	xxxx	120.12	Friday
10	White	02-Jan-23	xxxx	187.39	Friday
11	Red	02-Jan-23	xxxx	292.33	Friday
Transactions

Try this on a copy of your data.

It names the new tables 'tbl' concatenated with the worksheet name.

Can you please change your profile to state the version of Excel that you are using.

Private Sub subCopyData()
Dim arr() As Variant
Dim WsTransactions As Worksheet
Dim i As Integer
Dim tbl As ListObject
Dim Ws As Worksheet
Dim blnExists As Boolean
Dim WsDest As Worksheet
Dim strMsg As String

    ActiveWorkbook.Save
    
    Set WsTransactions = Worksheets("Transactions")
    
    WsTransactions.Activate
    
    Set tbl = WsTransactions.ListObjects(1)
    
    arr = Application.WorksheetFunction.Unique(tbl.ListColumns(1).DataBodyRange)

    If tbl.ListColumns(1).DataBodyRange.Rows.Count <> tbl.ListColumns(1).DataBodyRange.Rows.SpecialCells(xlCellTypeVisible).Count Then
        tbl.DataBodyRange.Select
        Selection.AutoFilter
        WsTransactions.Cells(1).Select
    End If

    For i = LBound(arr) To UBound(arr)
    
        strMsg = strMsg & vbCrLf & arr(i, 1)
    
        blnExists = False
    
        For Each Ws In Worksheets
            If Ws.Name = arr(i, 1) Then
                blnExists = True
                Exit For
            End If
        Next Ws
        
        If Not blnExists Then
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = arr(i, 1)
        End If
        
        Set WsDest = Worksheets(arr(i, 1))
        
        WsDest.Activate
        
        WsDest.Cells.ClearContents
        
        WsTransactions.ListObjects(1).Range.AutoFilter _
        Field:=1, _
        Criteria1:=arr(i, 1)
        
        tbl.Range.SpecialCells(xlCellTypeVisible).Copy
                
        With WsDest
            
            .Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats
            
            Application.CutCopyMode = 0
        
            .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "tbl" & arr(i, 1)
        
            With .ListObjects(1).Range
                .RowHeight = 30
                .IndentLevel = 1
                .EntireColumn.AutoFit
                .VerticalAlignment = xlCenter
            End With
        
            .Cells(1, 1).Select
            
        End With
        
        WsTransactions.Activate
 
        tbl.DataBodyRange.Select
        
        Selection.AutoFilter
        
    Next i
    
    If WsTransactions.AutoFilterMode Then
        WsTransactions.AutoFilter.ShowAllData
    End If
    
    WsTransactions.Cells(1).Select
    
    MsgBox UBound(arr) & " tables copied." & vbCrLf & _
        strMsg, vbOKOnly, "Confirmation"
     
End Sub

 

[kevin9999]

Similar to post #4, slightly less code:

Option Explicit
Sub shinobi()
    Application.ScreenUpdating = False
    Dim ws1 As Worksheet
    Set ws1 = Worksheets("Transactions")
    Dim a, i As Long, j As Long, exists As Boolean
    a = Application.Unique(ws1.Range("A2", ws1.Cells(Rows.Count, "A").End(xlUp)))
    
    For i = 1 To UBound(a, 1)
        For j = 1 To Worksheets.Count
            If Worksheets(j).Name = a(i, 1) Then exists = 1
        Next j
        If Not exists Then
            Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = a(i, 1)
        Else
            Worksheets(a(i, 1)).Cells.Clear
        End If
        exists = False
    Next i
    
    For i = 1 To UBound(a, 1)
        With ws1.Cells(1, 1).CurrentRegion
            .AutoFilter 1, a(i, 1)
            .SpecialCells(xlCellTypeVisible).Copy Worksheets(a(i, 1)).Cells(1, 1)
            With Worksheets(a(i, 1))
                .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "tbl" & a(i, 1)
            End With
        End With
    Next i
    ws1.ListObjects(1).AutoFilter.ShowAllData
    Application.ScreenUpdating = True
End Sub

 

[shinobi]

Thanks Both - this is super helpful. I had a few questions on your proposals (and thanks again for your help with this!):

1) It looks like the first part of this routine is checking that there are worksheets that match the names of the categories. If it exists, it clears the worksheet; if it does not, it adds a new one. Is that correct?

2) The second part of the routine seems to cycle through each category, apply the filter, and then copy the data to the relevant sheet. I can see the cycling; filtering; and copying element - but I don't understand how the 'paste bit' into the relevant sheet works.

Now for some of the complexities:

3) I should order the full table on the Transactions sheet by date (oldest to newest) before any further filtering.

4) When I tried to run this code, it seemed to run into a problem understanding how the table in the Transactions sheet was defined (when using 'ListObject'). Haven't tried this yet using 'CurrentRegion'. Do I need to define a range on the Transactions tab that represents the 'table' of data? This data set will get longer through the year, so I need for it to be dynamic. Note also that there is a sum calc at the bottom of the data set (I've updated the XL2BB file below) to show this more accurately.

Example.xlsx
	A	B	C	D	E
1	Account	Date	Description	Amount	Tag
2	Orange	01-Jan-23	xxxx	100.00	Sunday
3	Green	01-Jan-23	xxxx	75.00	Sunday
4	Blue	01-Jan-23	xxxx	56.25	Sunday
5	Blue	01-Jan-23	xxxx	42.19	Monday
6	Orange	01-Jan-23	xxxx	31.64	Monday
7	Red	02-Jan-23	xxxx	49.36	Monday
8	Black	02-Jan-23	xxxx	77.00	Friday
9	White	02-Jan-23	xxxx	120.12	Friday
10	White	02-Jan-23	xxxx	187.39	Friday
11	Red	02-Jan-23	xxxx	292.33	Friday
12
13				1,031.27
Transactions

Cell Formulas
Range	Formula
D13	D13	=SUM(D2:D12)

5) The category tabs I have set up are number - for example, the one for Orange is not called 'Orange' - it is called '1. Orange'. I used something like this code to adjust the tab name - but somehow need to incorporate this into the loop:

ActiveSheetName = Sheets(i).Name
Dot = InStr(1, ActiveSheetName, ".") 'finds position of "."
AccountFilter = Mid(ActiveSheetName, Dot + 2) 'Sets the right filter variable name

6) The destination sheet looks the same as the Transactions sheet in terms of the table (note also with the sum calc at the bottom). However, it also has an table to the bottom right of the data that is added (see XL2BB file below). Will the use of 'ClearContents' delete everything on this tab before adding new data?

Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Account	Date	Description	Amount	Tag
2	Orange	01-Jan-23	xxxx	100.00	Sunday
3	Orange	01-Jan-23	xxxx	31.64	Monday
4
5				131.64
6
7
8							31-Dec-22	31-Jan-23	28-Feb-23
9						Opening Balance	100.00	100.00	131.64
10						Net Movements		31.64	-
11						Closing Balance	100.00	131.64	131.64
1. Orange

Cell Formulas
Range	Formula
D5	D5	=SUM(D2:D4)
H8:I8	H8	=EOMONTH(G8,1)
H9:I9	H9	=G11
H10:I10	H10	=SUMIFS($D$3:$D$3,$B$3:$B$3,">"&G8,$B$3:$B$3,"<="&H8)
G9	G9	=D$2
G11:I11	G11	=SUM(G9:G10)

 

[Herakles]

Thanks Both - this is super helpful. I had a few questions on your proposals (and thanks again for your help with this!):

1) It looks like the first part of this routine is checking that there are worksheets that match the names of the categories. If it exists, it clears the worksheet; if it does not, it adds a new one. Is that correct?

2) The second part of the routine seems to cycle through each category, apply the filter, and then copy the data to the relevant sheet. I can see the cycling; filtering; and copying element - but I don't understand how the 'paste bit' into the relevant sheet works.

Now for some of the complexities:

3) I should order the full table on the Transactions sheet by date (oldest to newest) before any further filtering.

4) When I tried to run this code, it seemed to run into a problem understanding how the table in the Transactions sheet was defined (when using 'ListObject'). Haven't tried this yet using 'CurrentRegion'. Do I need to define a range on the Transactions tab that represents the 'table' of data? This data set will get longer through the year, so I need for it to be dynamic. Note also that there is a sum calc at the bottom of the data set (I've updated the XL2BB file below) to show this more accurately.

Example.xlsx
	A	B	C	D	E
1	Account	Date	Description	Amount	Tag
2	Orange	01-Jan-23	xxxx	100.00	Sunday
3	Green	01-Jan-23	xxxx	75.00	Sunday
4	Blue	01-Jan-23	xxxx	56.25	Sunday
5	Blue	01-Jan-23	xxxx	42.19	Monday
6	Orange	01-Jan-23	xxxx	31.64	Monday
7	Red	02-Jan-23	xxxx	49.36	Monday
8	Black	02-Jan-23	xxxx	77.00	Friday
9	White	02-Jan-23	xxxx	120.12	Friday
10	White	02-Jan-23	xxxx	187.39	Friday
11	Red	02-Jan-23	xxxx	292.33	Friday
12
13				1,031.27
Transactions

Cell Formulas
Range	Formula
D13	D13	=SUM(D2:D12)

5) The category tabs I have set up are number - for example, the one for Orange is not called 'Orange' - it is called '1. Orange'. I used something like this code to adjust the tab name - but somehow need to incorporate this into the loop:

ActiveSheetName = Sheets(i).Name
Dot = InStr(1, ActiveSheetName, ".") 'finds position of "."
AccountFilter = Mid(ActiveSheetName, Dot + 2) 'Sets the right filter variable name

6) The destination sheet looks the same as the Transactions sheet in terms of the table (note also with the sum calc at the bottom). However, it also has an table to the bottom right of the data that is added (see XL2BB file below). Will the use of 'ClearContents' delete everything on this tab before adding new data?

Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Account	Date	Description	Amount	Tag
2	Orange	01-Jan-23	xxxx	100.00	Sunday
3	Orange	01-Jan-23	xxxx	31.64	Monday
4
5				131.64
6
7
8							31-Dec-22	31-Jan-23	28-Feb-23
9						Opening Balance	100.00	100.00	131.64
10						Net Movements		31.64	-
11						Closing Balance	100.00	131.64	131.64
1. Orange

Cell Formulas
Range	Formula
D5	D5	=SUM(D2:D4)
H8:I8	H8	=EOMONTH(G8,1)
H9:I9	H9	=G11
H10:I10	H10	=SUMIFS($D$3:$D$3,$B$3:$B$3,">"&G8,$B$3:$B$3,"<="&H8)
G9	G9	=D$2
G11:I11	G11	=SUM(G9:G10)

A bit of project creep here I sense but that is OK. Only additions, no code has been wasted.

1. Existing worksheets are cleared of contents first. We did not know of the other summary tabel.
The worksheet is created if it does not already exist.

2. This line copies all visible cells / rows in the table including the header row.
tbl.Range.SpecialCells(xlCellTypeVisible).Copy

This line pastes the copied data into the colour worksheet at cell A1.
.Cells(1, 1).PasteSpecial xlPasteValuesAndNumberFormats

3. Do you want this code to order the Transactions table by date (oldest to newest) before the copying is carried out?

4. You mentioned in your original post in item 1 that you had a table. If this is not the case then select cell A1, select Insert, Table and when asked to
confirm the range, select the ‘My table has headers’ checkbox. The table will be created but it will have a default name.
On the ribbon at the far left you will be able to give the table a name ‘tblTransactions’.
How do you add data to this range of data / table?
A table will be dynamic when new rows are added.
Sum – Do you really need this sum for the Transactions table?

5. Adding a number prefix is easy. How do I know which number goes with which colour?
Do these worksheet tabs need to be in numerical order?

6. The destination sheet does resemble the Transactions table.
Does the summary table to the right always start at cell F8?
Do you want this code to create / enhance this table for you as months will need to be added?
What date range is likely to exist in the Transactions table?
Surely, the opening balance in the first month represented will always be zero.
Would it be better to have the dates as rows and the data titles as columns?

 

[shinobi]

Thanks for your reply. It was only when looking at the solution (to a simplified problem) that I got a sense for where some of the limitations might be - so a bit of project creep as I try to apply it (sorry about that!). Missed the 'pastespecial' in your code, and couldn't see anything like that in Kevin9999's code either.

I answer to your questions:

3) I'd like to order the table in Transactions by date first - that way, any subsequent filtered searches in that table are in date order too.

4) I start with a table of data in the Transactions tab - but I meant that in purely laymans terms / the sense that it is a bunch of data in a spreadsheet (didn't appreciate that that was an actual conceptual thing in VBA terms - which it clearly is!). As things currently stand, the data is not technically a 'table' (I'll name it as you have suggested). I add data to this table by inserting new rows (between the last entry and the sum calc there is an empty row - I will always insert from there, or above, perhaps). I'd like to keep the sum if possible, as this will allow me to cross-check/error-check elsewhere.

5) The Category tabs are already set up and numbered (they don't need to be created and inserted into the workbook from scratch). There won't be any cases where a row in the data table doesn't have a Category tab already set up for that row (so if easier, we can remove the functionality of adding a new tab if one doesn't exist already). The routine here is basically:

- Looping through each of Category tabs (which are already set up between a tab called 'Start' and a tab called 'End) - i.e. the tabs '1. Orange' and '2. Red' etc sit between tabs called 'Start>>>' and '<<<End'.
- Finding the Category name so we have right term to filter with (i.e. 'Orange' not '1. Orange) - this is basically taking the string after '1. ' or '2. ' etc
- Filtering the data table on the Transactions sheet for the relevant Category tab
- Copying that data into the relevant Category tab sheet
- Ideally, the macro would delete any existing data rows in each Category tab (except the very first row 2, which is the opening balance) before starting and then insert as many new rows as it needs to to accommodate the data - that way, the summary table remains in tact (and any references to that summary table also remain in tact)

6) The Summary table doesn't always start in cell F8 (it moves about, depending on how much data is in the table). That said, it doesn't need to - the main thing is that it references all the data in the table - which will change in size as more data is added over time.

That summary table is a monthly account balance - it will only ever have 12 month-ends, and only has an open balance / net movement / close balance for each month. I don't mind if this is set up horizontally or vertically. As the tabs are already set up, this summary table is set up for the year - it just populates as more data is added. The date range in the Transactions table will only ever be current calendar year. The opening balance may not be zero, as accounts may open from the prior year with non-zero balances.

Am going to pause and ask a question: what I am describing above is working within an existing framework. You might say it is much simpler to just generate new Category tabs for the Transactions data each time I run the macro - it sets up a new tab, and adds a new summary table providing the monthly summaries (this could always start in the same place for each tab (e.g. cell H1) so it's easy to maintain/rebuild references from other sheets.

If I step back, I have a big set of data on the Transactions tab (this is all the transactions from multiple bank accounts in the current calendar year). I need to find a way to put all that data onto individual bank account sheets (Categories). The sums are mainly for data validation - can do this another way if needs be. I could also do away with the numerical ordering of the accounts if it simplifies things. The monthly summary balances are useful for each account - but are pretty simple, so I guess can be set up each time too. Seems strange to delete and recreate the tabs each time I run the macro (over 20 accounts...) - but maybe that is more efficient than trying to work within an existing framework (where I need to be very careful about deleting/inserting rows).

Appreciate this is becoming complicated - no worries if this no longer makes sense for you to spend any time on (and thank you so much thus far - already given me food for thought!).

 

[Herakles]

Thanks for your reply. It was only when looking at the solution (to a simplified problem) that I got a sense for where some of the limitations might be - so a bit of project creep as I try to apply it (sorry about that!). Missed the 'pastespecial' in your code, and couldn't see anything like that in Kevin9999's code either.

I answer to your questions:

3) I'd like to order the table in Transactions by date first - that way, any subsequent filtered searches in that table are in date order too.

4) I start with a table of data in the Transactions tab - but I meant that in purely laymans terms / the sense that it is a bunch of data in a spreadsheet (didn't appreciate that that was an actual conceptual thing in VBA terms - which it clearly is!). As things currently stand, the data is not technically a 'table' (I'll name it as you have suggested). I add data to this table by inserting new rows (between the last entry and the sum calc there is an empty row - I will always insert from there, or above, perhaps). I'd like to keep the sum if possible, as this will allow me to cross-check/error-check elsewhere.

5) The Category tabs are already set up and numbered (they don't need to be created and inserted into the workbook from scratch). There won't be any cases where a row in the data table doesn't have a Category tab already set up for that row (so if easier, we can remove the functionality of adding a new tab if one doesn't exist already). The routine here is basically:

- Looping through each of Category tabs (which are already set up between a tab called 'Start' and a tab called 'End) - i.e. the tabs '1. Orange' and '2. Red' etc sit between tabs called 'Start>>>' and '<<<End'.
- Finding the Category name so we have right term to filter with (i.e. 'Orange' not '1. Orange) - this is basically taking the string after '1. ' or '2. ' etc
- Filtering the data table on the Transactions sheet for the relevant Category tab
- Copying that data into the relevant Category tab sheet
- Ideally, the macro would delete any existing data rows in each Category tab (except the very first row 2, which is the opening balance) before starting and then insert as many new rows as it needs to to accommodate the data - that way, the summary table remains in tact (and any references to that summary table also remain in tact)

6) The Summary table doesn't always start in cell F8 (it moves about, depending on how much data is in the table). That said, it doesn't need to - the main thing is that it references all the data in the table - which will change in size as more data is added over time.

That summary table is a monthly account balance - it will only ever have 12 month-ends, and only has an open balance / net movement / close balance for each month. I don't mind if this is set up horizontally or vertically. As the tabs are already set up, this summary table is set up for the year - it just populates as more data is added. The date range in the Transactions table will only ever be current calendar year. The opening balance may not be zero, as accounts may open from the prior year with non-zero balances.

Am going to pause and ask a question: what I am describing above is working within an existing framework. You might say it is much simpler to just generate new Category tabs for the Transactions data each time I run the macro - it sets up a new tab, and adds a new summary table providing the monthly summaries (this could always start in the same place for each tab (e.g. cell H1) so it's easy to maintain/rebuild references from other sheets.

If I step back, I have a big set of data on the Transactions tab (this is all the transactions from multiple bank accounts in the current calendar year). I need to find a way to put all that data onto individual bank account sheets (Categories). The sums are mainly for data validation - can do this another way if needs be. I could also do away with the numerical ordering of the accounts if it simplifies things. The monthly summary balances are useful for each account - but are pretty simple, so I guess can be set up each time too. Seems strange to delete and recreate the tabs each time I run the macro (over 20 accounts...) - but maybe that is more efficient than trying to work within an existing framework (where I need to be very careful about deleting/inserting rows).

Appreciate this is becoming complicated - no worries if this no longer makes sense for you to spend any time on (and thank you so much thus far - already given me food for thought!).

I spend a lot of time in bed for health resaons so spending time on this, and other posts, distracts me.

I'll go through this later but just for now:

Consider having a seperate sheet / table containing the opening balance for each Category. The summary sheet can look this up.

 

[kevin9999]

It looks like @Herakles is all over this issue now so I'll step back. One suggestion, where you say:

the data is not technically a 'table'

the quick way to tell whether it's strictly a 'table' is to select a cell anywhere inside your table, if the menu option "Table Design" appears on your menu options at the top of the screen - then it's a table - otherwise it's just a range