Copy Rows from Multiple Sheets and sum values

Question

I have a worksheet for every Month from Jan to Dec and a worksheet called Report where the copied data goes

In the Months sheet I have the following Data

ID     NAME     # DAYS OF VACATION
1      GEORGE   3
2      MARY     5

Every Month sheet has the same names but the names are not bound to the same ID What i want to do in Summary sheet is

ID     NAME     # DAYS OF VACATION     MONTH
1      GEORGE   3                      JAN
2      GEORGE   2                      FEB
SUM    GEORGE   5                      YEAR

What I have managed to do is to copy from one Month sheet to Report but I can't copy multiple from all the Month sheets and I don't know how to do the SUM part. Any ideas?

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer

    On Error GoTo Err_Execute

    'Start search in row 2
    LSearchRow = 2
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 2

    fname = InputBox("Enter Name", "Enter Data")
    If fname = "" Then
        While fname = ""
            MsgBox ("Enter Name")
            fname = InputBox("Enter Name", "Enter Data")
        Wend
    End If

    While Len(Range("A" & CStr(LSearchRow)).Value) > 0

        'If value in column E = "Mail Box", copy entire row to Sheet2
        If Sheets("JAN").Range("B" & CStr(LSearchRow)).Value = fname Then

            'Select row in Sheet1 to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy

            'Paste row into Sheet2 in next row
            Sheets("REPORT").Select

            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
            'AddWatermark ("JAN")

             'Move counter to next row
             LCopyToRow = LCopyToRow + 1
        End If

        'Go back to Sheet1 to continue searching
        Sheets("REPORT").Select

         LSearchRow = LSearchRow + 1
    Wend

    'Position on cell A3
    Application.CutCopyMode = False
    Range("A3").Select

    MsgBox "COPY DONE"

Exit Sub
Err_Execute:
   MsgBox "ERROR"
End Sub

The code fails because I don't know how to copy from multiple Sheets and how to add them row after row, plus in the end how to count the # DAYS OF VACATION. Any link or snippet would be awesome help. — Sonamor
– Sonamor, Commented Sep 4, 2013 at 10:51

Community · Accepted Answer · 2017-05-23 10:29:01Z

0

What you'll need to do is loop through each of your month sheets, and while on each sheet, loop through all the data to find values on that sheet that match your name indicated name (fName in your code). Two loops total. Also, check out this link as a starter, but you should also avoid selecting and copying when writing VBA code.

The basic code you'd be looking for (with some assumptions made) would be the following

'Assuming Jan is the first sheet and Dec is the 12th sheet in the workbook

lCopyToRow = 2
for i = 1 to 12
    lSearchRow = 2
    do while Len(Sheets(i).Cells(lSearchRow,1).value) > 0
        If  Sheets(i).Cells(lSearchRow,2).value = fName then
            Sheets("Summary").range(Sheets("Summary").Cells(lCopyToRow,1), _
                    Sheets("Summary").Cells(lCopyToRow,3)) = _
                           Range(Sheets(i).Cells(lSearchRow,1), _
                                       Sheets(i).Cells(lSearchRow,3)).value
            lCopyToRow = lCopyToRow + 1
        End If
        lSearchRow = lSearchRow + 1
    Loop
Next i

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Sep 4, 2013 at 19:49

Jaycal

2,0871 gold badge13 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sonamor Over a year ago

Thanks for the info Jaycal, I am using your code to copy but it only copies from the first sheet, I used a msgbox to see the value of the i and the i goes up to 12 as it should...I also used an error handler to present the error if any and it doesn't got triggered. Any thoughts?

Jaycal Over a year ago

try adding the following code immediately before the Do While loop...MsgBox Sheets(i).Cells(lSearchRow,1).value...this will at least show you the first value on each of your month sheets. If the length of this is not greater than 0, then you know it won't look at any data on that sheet

Sonamor · Accepted Answer · 2013-09-15 21:27:55Z

It took my a while buty I ended up with a new code that works faster and does exactly the job I needed.

I have the following Worksheets DATA,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC,SUMMARY

The following code search for a certain text, and copies the row from every worksheet except DATA and SUMMARY, to SUMMARY worksheet. In the end I sum up some integers to get the result I want. Thanks to everyone spend some time in my question.

   Sheets("SUMMARY").Cells.Clear 
Dim rFind As Range, fname As String, sAddr As String, ws As Worksheet 

fname = InputBox("Input Name", "Input Name") 
If fname = "" Then 
    While fname = "" 
        MsgBox ("Fatal Error") 
        fname = InputBox("Input Name", "Input Name") 
    Wend 
End If 


For Each ws In Worksheets 
    If (ws.Name <> "SUMMARY") And (ws.Name <> "DATA") Then 

        With ws.UsedRange.Range("B2", "AK36") 

            Set rFind = .Find(What:=fname, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False) 
            If Not rFind Is Nothing Then 
                sAddr = rFind.Address 
                Do 
                    rFind.EntireRow.Copy Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp)(2) 
                    Set rFind = .FindNext(rFind) 
                Loop While rFind.Address <> sAddr 
                sAddr = "" 
            End If 
        End With 
    End If 
Next ws 

Dim LR As Long 
With Sheets("SUMMARY") 
    LR = .Range("G" & Rows.Count).End(xlUp).Row 
    .Range("G" & LR + 1).Value = WorksheetFunction.Sum(.Range("G1:G" & LR))

Collectives™ on Stack Overflow

Copy Rows from Multiple Sheets and sum values

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related