Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 977 Times in 907 Posts
    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

  5. #5
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Can u please elaborate a bit more. I tried doing so but not succeeded...




    Ankit

  6. #6
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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).Colum n
    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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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]



  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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. ??

    [attachment=87802:sdckapr 2.xls]
    Attached Files Attached Files

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  10. #10
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve, please see my edit. I think we were typing in tandem.

  11. #11
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  12. #12
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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

  13. #13
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  14. #14
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •