Copy from worksheets into one based on date

Topic

New Reply

Hi,

I need a help regarding a macro.

We use to enter some data in a workbook that is shared and is placed on a network drive. It contains multiple validations and worksheet tabs with the respective names on it.

Now, during the entire day, every person opens his worksheet(Tab), and enter his data in his respective worksheet for a particular date.

There is one more sheet in the same workbook that is “Consolidated” and at the end of the day, we collate all the data into it from the different worksheets based on the date and it takes so much of time to copy this data as there are so many worksheets.

I am looking for a macro that simply on running, first of all ask for a date and then based on the date, copy rows from selected worksheets of that date into the Consolidated Worksheet.

I have also attached the sample workbook as well.

Thanks,
Ankit

Reply | Quote

Replies

I am not a fan of keeping 2 copies of the same data in a workbook. I recommend everybody just adding the data to the consolidated workbook in the first place. If you add an autofilter on this sheet, anyone can filter on the appropriate person’s name and view what you have now for individual sheets without the managing issue.

Also I would stop the sharing of the workbook and only allow one person at a time to have access. Sharing Excel workbook will often (almost inevitably) lead to a corrupt workbook as Excel can get confused with all the changes being made and it tries to figure out how to combine the various versions.

[If you want “sharing” (multiple entries all the time) in a database use something like Access which does not have multiple copies but opens and updates the stored file continuously.]

Steve

Reply | Quote

Thanks for your prompt reply Steve. Yes, its right to place only one workbook and ask the persons to filter with their name and pour in their data. But this time our requirement is only a macro that can just do the required job.

I liked your suggestion of not keeping one workbook for two set of data. So, I would really appreciate if anybody can help me in a macro that I can put in a workbook on my desktop and that can pick data from a workbook on a shared path based on dates.

Ankit

Reply | Quote

You can collate data by putting a link in one worksheet that points to another worksheet. To collect date dependent data you have a date at the top which you either change manually or set to “=now()”, then the link can perform a vlookup with the date as the criteria.

cheers, Paul

Reply | Quote

Can u please elaborate a bit more. I tried doing so but not succeeded…

Ankit

Reply | Quote

For My query, somehow, I have managed to build a macro but I m stuck at few things…

Here is the code…..

Sub Mycollation()

Dim mysht As Worksheet
Set mysht = Worksheets.Add
Dim ShtLoop
Dim Looper As Long, offsetvar As Long
For Each ShtLoop In ActiveWorkbook.Worksheets
If ShtLoop.Name mysht.Name Then
For Looper = 1 To 152
If Len(mysht.Range(“a1”).Value) = 0 Then
ShtLoop.Range(“1:3”).Copy Destination:=mysht.Range(“a2”)
End If
If ShtLoop.Cells(Looper, 1).Value = “1/14/2010” Then
ShtLoop.Range(Looper & “:” & Looper).Copy Destination:=mysht.Range(“a2”).Offset(offsetvar, 0)
offsetvar = offsetvar + 1

End If
Next
End If

Next
offsetvar = offsetvar + 3
For Looper = 1 To mysht.Cells.SpecialCells(xlCellTypeLastCell).Column
mysht.Cells(offsetvar, Looper).FormulaR1C1 = “=count(R[-” & offsetvar – 3 & “]C:R[-1]C)”

Next

End Sub

1. I want the macro to ask for the date every time i run it.

2. Its not pulling up the data from the sheet I m running. Its leaving that sheet data.

3. In the end, I placed a formula for counting the values in a column. I want it such that after pasting the data, below the pasted data, there should be a count for every person in front of it and below that, sum of all counts.

Is it possible??

Thanks,
Ankit

Reply | Quote

How about this?

Steve

[codebox]Option Explicit
Sub CopySheets()
Dim wCons As Worksheet
Dim wks As Worksheet
Dim rCopy As Range
Dim bFilterMode As Boolean
Dim iCols As Integer
Dim lLastRow As Long
Dim lDestRow As Long
Dim vDate As Variant

‘Change as desired
Set wCons = Worksheets(“Consolidated”)
iCols = 6 ‘Number of columns to copy

On Error GoTo ErrHandler
Application.ScreenUpdating = False

‘Choose date
Do
vDate = InputBox(“Enter date as ‘mm/dd/yyyy'”, “Get Date”, Date)
Loop Until IsDate(vDate) Or vDate = “”
If vDate = “” Then
‘Exit if no date chosen
MsgBox “No Date was chosen”
GoTo ExitHandler
End If

‘loop through all sheets
For Each wks In ActiveWorkbook.Worksheets
With wks
‘make sure worksheet is not consolidated
If UCase(.Name) UCase(wCons.Name) Then
‘Store current status of autofilter
bFilterMode = .AutoFilterMode
‘turn on if not already on
If .AutoFilterMode Then
‘if on, show all data
If .FilterMode Then .ShowAllData
Else
‘if not on, turn on
.Range(“A1”).AutoFilter
End If
‘get lastrow of data, define datarange
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rCopy = .Range(.Range(“A2”), .Cells(lLastRow, iCols))
‘get first available destination row
lDestRow = wCons.Cells(.Rows.Count, 1).End(xlUp).Row + 1
‘filter worksheet on date
wks.Range(“A1”).AutoFilter Field:=1, Criteria1:=vDate
‘Copy filtered data to consolidation sheet
rCopy.Copy wCons.Cells(lDestRow, 1)
‘Reset AutoFilter
If .FilterMode Then .ShowAllData
If Not bFilterMode Then
.AutoFilterMode = False
End If
End If
End With
Next wks

MsgBox “done”

ExitHandler:
Set rCopy = Nothing
Set wCons = Nothing
Set wks = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub[/codebox]

Reply | Quote

Hi Steve,

I don’t seem to be able to get the correct result from your code using the OP’s workbook. There seems to be an issue with the line:

wks.Range(“A1”).AutoFilter Field:=1, Criteria1:=vDate

which appears to not bring back the correct vDate.

Am I doing something wrong? I’ve fiddled with it without success.

ADDED LATER:

In the attached, I have commented out the “Reset Autofilter” section. If you run the macro, and enter the date as 14/01/2010, I get all of the data for 14/01/2010 and 15/01/2010 returned to the Consolidated sheet, but if you look at the individual people’s sheets, all of the filters return null. ??

Reply | Quote

I had no problem with the test file. Where is the issue? Is the vDate not the date you entered? Is it filtering the sheets wrong?

I suspect it may be an issue with international dates and either vDate is being entered wrong, being entered right and being converted wrong, or that the filter is not getting interpreting the date correctly since it works on a Date transformed into “Text”. It may be a simple matter of formatting the date when entered into the filter to match the display date.

But I would need to know exactly what the issue is (on your end) to suggest a solution…

Steve

Reply | Quote

Hi Steve, please see my edit. I think we were typing in tandem.

Reply | Quote

I *think* there is an issue with the formatting of the date. Your msgbox reads mm/dd/yyyy, but regardless of if I try it that way or dd/mm/yyyy, I get the same results. All sheets filters return null, yet all is copied to the consolidated sheet.

Reply | Quote

Hi All,

Code by Steve and Nath both is working absolutely perfect….kudos…..

But one thing they have forgotten to add in the code is the Count of every person below the pasted data and then count of overall numbers for a particular date.

Thanks in advance for this additinal help.

Regards,
Ankit

Reply | Quote

Hi Ankit,

Please could you post a small sample of how you would like the summary to look.

Also, the code was provided by and appears to work for Steve, and I can see that it works for you also. It does not work correctly for me in the UK, and I presume that it is something to do with regional dates. Where are you and what date format do you work on?

Thanks
Nathan

Reply | Quote

Hi Nathan,

I want the date format to be like ” mm/dd/yyyy”

and also I’ve attached the desired format of the workbook. “Consolidated” worksheet should look like the attached one.

Thanks,
Ankit

Reply | Quote

That was not in your original. If you want summaries of individual dates, it gives even more reason to compile all the sheets into 1 and then use a pivot table to change and summarize on the fly…

Steve

Reply | Quote

But you don’t mention where the issue is. Which date is wrong and what is it doing exactly as it works through the code?

Steve

Reply | Quote

But you don’t mention where the issue is. Which date is wrong and what is it doing exactly as it works through the code?

Steve

Hi Steve,
I am hoping that another lounger may comment that uses the UK date format dd/mm/yyyy. I spent a fair bit of time yesterday playing with this and it mostly works, by that I mean the code steps through and works as expected. However, the filter does not generate the correct result.

The individual sheets all have 4 rows of data, 2 rows for 14/01/2010 and 2 rows for 15/01/2010. Therefore if I enter the date as 14/01/2010, I would expect each sheet to be filtered to 14/10/2010 each showing 2 rows, and therefore 10 rows of data being copied to the consolidated sheet.

The result that I get is each filter shows nothing, but all 20 rows are copied to the consolidated sheet. In my previous attachment, I commented out the “reset filter section” so that when the code is completed I can see the filter result. Clicking the dropdown arrow shows that 14/10/2009 is selected, even though no data is visible. Re-selecting 14/10/2010 manually at this point shows the correct result, with 2 data rows visible.

How all data is copied to the consolidated sheet when there is no data visible from the filters…….. No idea?

I am convinced that this is a regional date issue, because the code appears to be correct.

Google turned up the following – “Using dates in AutoFilter can be tricky if not using the US date format”, but I am no nearer a fix.

http://www.ozgrid.com/VBA/autofilter-vba-dates.htm

I’m hoping this makes a little more sense…

Thanks,
Nathan

Reply | Quote

Hi,

Actually, at Nathan’s side , your code was not working. Also, he asked me about the date format I want to use… Moreover, there are few things that I mentioned above i need in the code…….when I put in my code….there was a count formula in it….but I wasn’t able to make it for every person as I wanted in the last spreadsheet attached…..

May be Nathan or Steve can help me out in this …

Thanks,
Ankit

Reply | Quote

Based on the original sample, it seemed you wanted a means to add new dates to an existing consolidation sheet and have a duplicate “complete” version of all the sheets along with the individual users.

The last example seems to be a “snapshot” for a given date and the code would be to get the date, then delete the entire contents (except the headers) of the consolidation sheet, then loop through the other sheets copying the particular entries by date and adding them consolidation. Afterwards a summary table is created at the bottom of the list.

Is this what you now want: the single date summary extract, wiping out any other extracts? If not, please detail exactly what you do want…

Steve

Reply | Quote

Hi Ankit,

I’m not currently in a position to assist with the 2nd part of your requirement until I can get the 1st part working properly. If you answer Steve’s last post then hopefully we will be able to move on from there.

Nathan

Reply | Quote

Hi Steve,

Thanks for giving this post so much of time ….

Well, Yes I want exactly the same as u mentioned …but is it possible in one code without deleting the contents except the headers….?? If not , then its ok with your suggestion…

One big concern about your previous code….when I run your code, it asked for a date, I entered it …..but it copied the column labels(headers) from the sheets in which there is no data. Secondly, when there is no data of a particular date ( No date in date column), its still copying the data of other dates…

I think I m pretty clear this time,….?????

Thanks,
Ankit

Reply | Quote

???

Reply | Quote

I fixed the copying of dates when no data is found. When I run the code the headers are not copied. Are your sheets setup like the example? The code presumes that the header row is 1 and the data starts in row2 on each sheet…

Steve

[codebox]
Option Explicit
Sub CopyDateInformation()
Dim wCons As Worksheet
Dim wks As Worksheet
Dim rCopy As Range
Dim bFilterMode As Boolean
Dim iCols As Integer
Dim lLastRow As Long
Dim lDestRow As Long
Dim vDate As Variant
Dim vArray() As Variant
Dim i As Integer
Dim x As Long
Dim lTotal As Long

‘Change as desired
Set wCons = Worksheets(“Consolidated”)
iCols = 6 ‘Number of columns to copy

On Error GoTo ErrHandler
Application.ScreenUpdating = False

‘Choose date
Do
vDate = InputBox(“Enter date as ‘mm/dd/yyyy'”, “Get Date”, Date)
Loop Until IsDate(vDate) Or vDate = “”
If vDate = “” Then
‘Exit if no date chosen
MsgBox “No Date was chosen”
GoTo ExitHandler
End If
‘Dimension Array for Totals and set subtotal
ReDim vArray(1 To Worksheets.Count – 1, 1)
i = 1
lTotal = 0
‘Clear consolidated sheet
wCons.Cells.Clear
‘loop through all sheets
For Each wks In ActiveWorkbook.Worksheets
With wks
‘make sure worksheet is not consolidated
If UCase(.Name) UCase(wCons.Name) Then
‘Store current status of autofilter
bFilterMode = .AutoFilterMode
‘turn on if not already on
If .AutoFilterMode Then
‘if on, show all data
If .FilterMode Then .ShowAllData
Else
‘if not on, turn on
.Range(“A1”).AutoFilter
End If
‘get lastrow of data, define datarange
lLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
Set rCopy = .Range(.Range(“A2”), .Cells(lLastRow, iCols))
‘filter worksheet on date
.Range(“A1”).AutoFilter Field:=1, Criteria1:=vDate

‘Add total to Array and subtotal
x = Application.WorksheetFunction.Subtotal(2, _
.Range(.Range(“A2”), .Cells(lLastRow, 1)))
vArray(i, 0) = wks.Name
vArray(i, 1) = x
lTotal = lTotal + x
‘Copy filtered data to consolidation sheet
‘copy header row if 1st sheet
If i = 1 Then
.Range(.Range(“A1”), .Cells(1, iCols)).Copy _
wCons.Range(“a1”)
End If
‘get first available destination row
lDestRow = wCons.Cells(.Rows.Count, 1).End(xlUp).Row + 1
i = i + 1
If x > 0 Then ‘there are rows to copy
rCopy.Copy wCons.Cells(lDestRow, 1)
End If
‘Reset AutoFilter
If .FilterMode Then .ShowAllData
If Not bFilterMode Then
.AutoFilterMode = False
End If
End If
End With
Next wks
‘Add totals
‘get first available destination row and go down 1 row
With wCons
lDestRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 2
.Range(.Cells(lDestRow, 1), _
.Cells(lDestRow + i – 2, 2)).Value = vArray
‘Total
lDestRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 2

.Cells(lDestRow, 1) = “Days Production”
.Cells(lDestRow, 2) = lTotal
End With

MsgBox “done”

ExitHandler:
Set rCopy = Nothing
Set wCons = Nothing
Set wks = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub[/codebox]

Reply | Quote

Hi Steve,

Format you just provided is perfect except that the fact Its not copying and pasting any data from the sheets except the name.

Thanks,
Ankit

Reply | Quote

I don’t understand.

Here is the sample you gave with the code and ran for 1/15/2010. It seems to work properly. Is your data set different than the sample?

Could you describe what the problem is in more detail and perhaps attach a file that is not working and the results you get from the code?
Steve

Reply | Quote

Hi,
Steve’s latest workbook sample works for me if I enter 1/14/2010 or 1/15/2010, but not if I enter 1/14/2010 or 1/15/2010. What I fail to understand is that the date’s in the worksheets are formatted as dd/mm/yyyy, or is that just how I see it on my pc due to my windows date settings?

I cannot get it to work with dd/mm/yyyy at all.

Sorry for hijacking the thread, it’s just that I was interested to see this working.

Nathan

Reply | Quote

To fix the “0” issue, try replacing the line (this may also help the other issue as well)
.Range(“A1”).AutoFilter Field:=1, Criteria1:=vDate

with the line:
.Range(“A1”).AutoFilter Field:=1, Criteria1:=DateValue(vDate)

If that does not resolve the the issue is with the dd/mm/yyyy, you will have to let us know what is not being accepted correctly: is it in InputBox or is it not filtering correctly on the individual sheets?

Steve

Reply | Quote

Whoooa….

Wicked Steve, that did fix both issues, Thanks.

I have learned from this and can use this code structure in future. Thanks very much!

Reply | Quote

Hi Steve,

Here I am attaching the actual workbook on which I am trying to pull the data.

The workbook you just provided named as”Consolidate worksheets2″ was working absolutely fine and it has exactly all the desired things.. But when I run the same code on my workbook “Team Data” ( Attached) , its not working. Please see where is the problem.

Also, I changed the numbers of columns to copy from 6 to 12. Its still pulling up no results.

Thanks,
Ankit

Reply | Quote

???

Reply | Quote

Anybody pls. help in the above mentioned query…

Reply | Quote

Try changing the line to:
.Range(“A1″).AutoFilter Field:=1, Criteria1:=Format(vDate, “m/d/yy”)

Steve[/color]

Reply | Quote

Thanks a ton Steve…….U r simply brilliant….

Reply | Quote