Solved Consolidating Data from Multiple Worksheets Based on a Date

Microsoft Excel 2016 suites
February 5, 2016 at 14:43:02
Specs: Windows 7
I have a shared workbook that I created with 8 different worksheets that all have the same format. I have a "Summary" worksheet with the same format created and a cell (C7) to enter a date. I created a Command Button to click to refresh the data after a date has been entered into C7.

I'm having trouble in VBA getting this button to pull ALL entries from the date specified in c7 from all of the worksheets and consolidate it underneath.

Everyday my employees enter data into this workbook on different worksheets. At the end of the day, I'd like to enter a specific date into cell "C7," click the command button, and populate all entries made on this date. Can anyone help? I am completely lost in VBA, and have no code attached to the command button at the moment.


See More: Consolidating Data from Multiple Worksheets Based on a Date

Report •

✔ Best Answer
February 8, 2016 at 20:00:46
The code offered below assumes that the Summary sheet is the 9th sheet in the workbook and that you are copying data from Sheets 1-8.

BTW...since you said that your are "having trouble in VBA" you might want to review this tutorial. These debugging techniques will help you troubleshoot "broken" code as well as understand code that is found in forums such as this one. Using these debugging techniques can help you write/fix/learn VBA code.

http://www.computing.net/howtos/sho...

Sub Copy_By_Date()
'Clear Summary Sheet Row 10 through End
   Sheets("Summary").Range("A10:A" & Rows.Count).EntireRow.ClearContents
'Set myDate variable to value in C7
   myDate = Sheets("Summary").Range("C7")
'Set initial Paste Row
   nxtRw = 10
'Loop through Sheets 1 - 8
  For shtNum = 1 To 8
'Search Column b for date(s)
     With Sheets(shtNum).Columns(2)
      Set d = .Find(myDate)
        If Not d Is Nothing Then
          firstAddress = d.Address
            Do
'Copy each Row where date is found to next empty Row on Summary sheet
                d.EntireRow.Copy Sheets("Summary").Range("A" & nxtRw)
                nxtRw = nxtRw + 1
                Set d = .FindNext(d)
            Loop While Not d Is Nothing And d.Address <> firstAddress
        End If
     End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 5, 2016 at 15:47:53
First you say you are: "having trouble in VBA getting this button to pull ALL entries from the date specified in c7..."

Then you say you: "have no code attached to the command button at the moment."

I'm confused.

Is the code written and working but just not attached to the button?

Is the code written but not working?

Is the code not even written?

Please clarify.

If you have attempted to write the code but it is not working, please click on the following line and read the instructions on how to post VBA code in this forum so that we can see what you have so far.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 5, 2016 at 15:51:20
I have no code attached to the command button at the moment.

Report •

#3
February 5, 2016 at 21:12:30
You did not answer my questions. I know that you do not have any code attached to the command button.

My question was: Do you have any code written?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 8, 2016 at 10:24:48
Sorry, I have no code written. The button is currently inoperable.

Report •

#5
February 8, 2016 at 12:31:05
OK, so we are starting from scratch. No problem, but I have a couple of questions first.

Please keep in mind that even though you said "I created with 8 different worksheets that all have the same format", we can't see your workbook from where we are sitting and therefore have no idea what that "format" looks like.

If the sheets are laid out so that the date you are looking for is in a specific
column (e.g. A) and you want to copy the entire row each time the date is found in that column, then the code will be very straight forward: Search Sheet1!Column A for the date in Summary!C7 and copy the entire row when found. Then search again and copy again if found. Once Sheet1 has been exhausted, move on to Sheet2 and do it all again.

A loop within a loop is all that is needed.

However, if the the sheet is laid out such the date could be anywhere or if multiple rows need to be copied for each date then things get more complicated.

Can you tell me something about the layout the sheets?

Another question: Is the goal to clear the Summary sheet and populate it with new data each time the button is clicked or is the new data being appended to bottom of the existing data? Both of those actions a pretty easy. However, if you tell me that you want to replace existing data on an individual basis, here and there throughout the sheet, the level of complexity goes up considerably.

Tell us more about what you are trying to do and we'll see if we can help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 8, 2016 at 13:05:55
Sure, and thanks for helping with this! The dates are all in column B on all worksheets, and start on row 10, if that matters. The general idea of the function I'm trying to create is that at the end of the day, a specific, single date can be entered into the search field (cell C7 on Summary worksheet) and all rows of data containing that date are populated below it. Since this is something that will be done everyday, it would be good for the previous data that was searched to be erased. That way if I want to search for entries made into the spreadsheet on "2/8/2016" I will only see that data and no other days.

Just let me know if I need to be more specific in any area; I'm trying my hardest to convey my thoughts to you as clearly as possible.


Report •

#7
February 8, 2016 at 14:01:17
Just to clarify, only the data on the Summary sheet that is "copied" from the other worksheets needs to be erased/replaced. The data that is in the other worksheets needs to remain unchanged... I only want to use the summary sheet to "copy" rows of data that have the specified date in them from the other worksheets and consolidate them into a list. If a new date is entered and the command button is clicked, the old list of data is to be replaced with the new list of data that includes the date that was just searched by the user, if possible.

Report •

#8
February 8, 2016 at 14:04:46
...And if that makes the coding significantly more complicated, then I can just manually erase the old data before preforming another search. Just let me know. Thanks again

Report •

#9
February 8, 2016 at 20:00:46
✔ Best Answer
The code offered below assumes that the Summary sheet is the 9th sheet in the workbook and that you are copying data from Sheets 1-8.

BTW...since you said that your are "having trouble in VBA" you might want to review this tutorial. These debugging techniques will help you troubleshoot "broken" code as well as understand code that is found in forums such as this one. Using these debugging techniques can help you write/fix/learn VBA code.

http://www.computing.net/howtos/sho...

Sub Copy_By_Date()
'Clear Summary Sheet Row 10 through End
   Sheets("Summary").Range("A10:A" & Rows.Count).EntireRow.ClearContents
'Set myDate variable to value in C7
   myDate = Sheets("Summary").Range("C7")
'Set initial Paste Row
   nxtRw = 10
'Loop through Sheets 1 - 8
  For shtNum = 1 To 8
'Search Column b for date(s)
     With Sheets(shtNum).Columns(2)
      Set d = .Find(myDate)
        If Not d Is Nothing Then
          firstAddress = d.Address
            Do
'Copy each Row where date is found to next empty Row on Summary sheet
                d.EntireRow.Copy Sheets("Summary").Range("A" & nxtRw)
                nxtRw = nxtRw + 1
                Set d = .FindNext(d)
            Loop While Not d Is Nothing And d.Address <> firstAddress
        End If
     End With
  Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
February 9, 2016 at 12:14:35
Awesome, thanks so much man! I live in New Orleans, so we're off today for Mardi Gras, but I will try this tomorrow and post a reply to let you know if I run into any issues. Thanks a ton for the info too! If you ever find yourself down here, I owe you a beer!

Report •

#11
February 10, 2016 at 08:28:51
Haven't had much time to fool with it, but upon first test, the command button doesn't run when it's clicked, but I can manually run the macro and it works! Is there anyway to attach it to the command button to run the process when the button is clicked? TIA.

Report •

#12
February 10, 2016 at 10:54:32
I got it! Thanks so much for the help! I'm going to read up a bit and use this as a learning tool. Thanks again! Have a good one.

Report •

Ask Question