Previous Topic Next Topic

Copy rows to total page

[Solved/Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,
I have an Excel spreadsheet with 13 worksheets. The first 12 worksheets are January through December. The 13th worksheet is a total. Each worksheet has identical columns and rows.

In all worksheets, there are about 10 columns like Date, Name, Address, Account Number, Department, Employee Name, etc...

What I am trying to do is enter the information in each monthly worksheet and have the data I'm entering automatically copied to the total worksheet. The total worksheet would contain all the data entered. I'd have a monthly breakdown, but the total page contains all transactions.

I have all the worksheets set up, but can't come up with the code to do the copy. Any suggestions, please?

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
With macro it can be done. But why have 13 sheets ? why not have one sheet from the start where you enter all your transactions, have a column there for month (if not already there) and then if you want to see some monthly transaction, just filter on that month ?
Report

Because that's the way the boss wants it.

Different people will get the monthly forms that have the titles January, February, etc... and the manager gets the total page.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768 > Tom
Try this. Of course make sure you make a backup of original file before testing
Assumptions.

1. The sheets are names Jan, Feb, ....
2. The Master sheet is called Master
3. The column 1 does not have blank value (it is used to find the max number of rows)
4. There are no more than 11 columns
5. Master sheet already have header row. 

Sub copyData()

    Dim maxRows As Long
    Dim maxCols As Integer
    Dim conSheet As String 'consolidated sheet name
    Dim lConRow As Long 
    Dim maxRowCol As Integer 'used to find max number of rows
    
    maxCols = 11
    months = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    
    maxRowCol = 1
    conSheet = "Master"
    
    
    Sheets(conSheet).Select
    
    Range("A2").Select
    Cells(65536, 256).Select
    Selection.End(xlDown).Select
    
    maxRows = Selection.Row
    
    Range("A2", Selection).Select
    Selection.ClearContents
    
    lConRow = 2
    
    For x = 0 To Sheets.Count - 2

        Sheets(months(x)).Select

        If ActiveSheet.AutoFilterMode Then
            Cells.Select
            Selection.AutoFilter
        End If

        Cells.Select
        
        Dim lastRow As Long
        
        lastRow = Cells(maxRows, maxRowCol).End(xlUp).Row

        If (lastRow > 1) Then

            Range(Cells(2, 1), Cells(lastRow, maxCols)).Select
            Selection.Copy

            Sheets(conSheet).Select
            Cells(lConRow, 1).Select
            Selection.PasteSpecial

            lConRow = Cells(maxRows, maxRowCol).End(xlUp).Row
            lConRow = lSummaryRow + 1

        End If


        If ActiveSheet.Name = "Dec" Then Exit Sub

    Next
    
End Sub
Ask a question