Report

Exporting data [Solved]

Ask a question chidazy 4Posts Wednesday August 24, 2016Registration date August 26, 2016 Last seen - Latest answer on Aug 26, 2016 08:19PM
Hi am so excited to join this forum!!!
Pls I need help.
I have 14 sheets on a workbook (for 14 of the company's locations in Canada). The data contains employee names, job title, audiometric test due dates and respiratory test due dates. Both category of due dates are normally within 731 days from the dates the test is done. Here is the clause, in most cases like for new hires, the due date is 365 days
I need to create a formulae of some sort to lookup and export the names of all the employees with due dates of 365 days to a new sheet. with this new sheet updating automatically each time a new employee with the criteria (due date = 365 days) is entered on any sheet in the workbook.
Am not sure if this explanation is detailed, I could explain further or post a sample of the excel spreadsheet for clarification. Kindly assist.
See more 
Helpful
+1
moins plus
Hello Chidazy,

I think I follow what you would like to do so the following VBA code, placed in a standard module, should do the trick for you:-

Sub Transfer()

    Dim ws As Worksheet

Application.ScreenUpdating = False

Sheet3.UsedRange.Offset(2).ClearContents

For Each ws In Worksheets
    If ws.Name <> "Main" Then
With ws.Range("G5", ws.Range("G" & ws.Rows.Count).End(xlUp))
        .AutoFilter 1, 365, xlOr, 366
        On Error Resume Next
        .Offset(1).EntireRow.Copy
        Sheet3.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        Sheet3.Columns.AutoFit
        Sheet3.Range("A1").Select
        End With
        ws.AutoFilterMode = False
        End If
Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True
  
End Sub


Following is the link to my test work book (based on the sample that you supplied):-

https://www.dropbox.com/s/gvduypwwumhr5dr/Chidazy%28multi%20shts%20to%20master%20sht%29.xlsm?dl=0

You'll see in the sample that I have created a "Main" sheet (sheet3) where all the required data will be consolidated.

The code filters Column G in all sheets (except the "Main" sheet) for the values 365 or 366 and transfer the relevant rows of data to the "Main" sheet. The "Main" sheet is refreshed each time that the code is executed so that you will not have duplication.

Click on the "RUN" button to see it work.

In all the source sheets, you will need to format Column G to "General" as at present there is a variety of formats which could interfere with the proper execution of the code.

I hope that this helps.

Cheerio,
vcoolio.
chidazy 4Posts Wednesday August 24, 2016Registration date August 26, 2016 Last seen - Aug 26, 2016 12:14PM
OMG vcoolio.
I haven't stopped crying for joy. You are amazing. Thank u sooooooooooooooooooo much. This is unbelievable, Thanks, thanks and thanks again.!!!!!! Yeepeeeeeeeeeeeeeeeeeeeeeeee
Reply
Add comment
Helpful
+0
moins plus
Hello Chidazy,

Yes, please upload a sample of your work book to a free file sharing site such as DropBbox, ge.tt or SpeedyShare then post the link to your file back here. Please use dummy data.

We can then try to sort it out for you. Please include any coding or formulae that you may have tried.

Thank you.

Cheerio,
vcoolio.
Add comment
Helpful
+0
moins plus
chidazy 4Posts Wednesday August 24, 2016Registration date August 26, 2016 Last seen - Aug 25, 2016 12:58PM
Thanks for offering to help vcoolio
Reply
Add comment
Helpful
+0
moins plus
Hello Chidazy,

You're welcome. Glad that I could help out.

Good luck!

Cheerio,
vcoolio.
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!