Macro to find a particular cell's data in a different workbook

**philwilliams80** · 11-23-2011, 11:14 AM

Hello,

I've been creating a macro to poulate a database based on information sent to me in a second workbook.
The part I've become stuck on is if the form is sent to me by Chris, with his name in Cell D5 and some data in cell D11, I want the macro to look for "Chris" in my separate database then move to the next empty cell in the row to the right, pasting the data from cell D11.

Is this easy to code?

Many thanks

**6StringJazzer** · 11-23-2011, 12:24 PM

Yes.

Does your code already have the database workbook open with and assigned to a Workbook variable?

Where is the data located in the database workbook that needs to be searched for the user name?

You want to use the Find method for that range. Using it in a different workbook is exactly the same as using in the workbook containing the code, once you qualify the range reference.

More detailed question gets more detailed answer

**philwilliams80** · 11-23-2011, 12:44 PM

Hi 6String... many thanks for your response.

I guess it may be best if I post the two workbooks and you can see what I have already - although you need to promise not to mock my attempts so far!!

Basically we have a database logging how many days in a month people are off sick, and we have a template that's sent from the manager recording each incidence.

The macro currently updates the database with the number of days with no problem. I acknowledge I've probably gone around this in a very longwinded way... but it works.

The problem is that I also want the reason to come across too, and that this populates the table on the right of the database as appropriate, in the next empty cell (so the second incidence needs to not overwrite the first).

So to answer your questions - yes, the database is going to be open when the macro is run. Hopefully you can see in the database where the reason description needs to be pasted - i.e. starting in column AP and moving right with each incidence.

Code used so far to complete most of the work is:

Sub Populate()
'
' Populate Macro
'

'
    Sheets("Template").Select
    Sheets("2011").Visible = True
    Windows("Sickess Dbase 2011.xls").Activate
    Columns("D:AP").Select
    Selection.EntireColumn.Hidden = False
    
    Windows("Staff sickness Template.xls").Activate
    Sheets("2011").Select
    Range("E4:E80").Select
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("F4:F80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("I4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("G4:G80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("L4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("H4:H80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("O4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("I4:I80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("R4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("J4:J80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("U4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("K4:K80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("X4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("L4:L80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AA4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("M4:M80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AD4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("N4:N80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AG4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("O4:O80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AJ4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P4:P80").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AM4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Range("G4:G40").Select
    
    Selection.Copy
    Range("E4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J4:J40").Select
    
    Selection.Copy
    Range("H4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M4:M40").Select
    
    Selection.Copy
    Range("K4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P4:P40").Select
    
    Selection.Copy
    Range("N4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S4:S40").Select
    
    Selection.Copy
    Range("Q4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("V4:V40").Select
    
    Selection.Copy
    Range("T4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Y4:Y40").Select
    
    Selection.Copy
    Range("W4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AB4:AB40").Select
    
    Selection.Copy
    Range("Z4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AE4:AE40").Select
    
    Selection.Copy
    Range("AC4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AH4:AH40").Select
    
    Selection.Copy
    Range("AF4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AK4:AK40").Select
    
    Selection.Copy
    Range("AI4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AN4:AN40").Select
    
    Selection.Copy
    Range("AL4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False



    Range("F4:F40").Select
    Selection.ClearContents
    Range("I4:I40").Select
    Selection.ClearContents
    Range("L4:L40").Select
    Selection.ClearContents
    Range("O4:O40").Select
    Selection.ClearContents
    Range("R4:R40").Select
    Selection.ClearContents
    Range("U4:U40").Select
    Selection.ClearContents
    Range("X4:X40").Select
    Selection.ClearContents
    Range("AA4:AA40").Select
    Selection.ClearContents
    Range("AD4:AD40").Select
    Selection.ClearContents
    Range("AG4:AG40").Select
    Selection.ClearContents
    Range("AJ4:AJ40").Select
    Selection.ClearContents
    Range("AM4:AM40").Select
    Selection.ClearContents
    Columns("AM:AN").Select
    Selection.EntireColumn.Hidden = True
    Columns("AJ:AK").Select
    Selection.EntireColumn.Hidden = True
    Columns("AG:AH").Select
    Selection.EntireColumn.Hidden = True
    Columns("AD:AE").Select
    Selection.EntireColumn.Hidden = True
    Columns("AA:AB").Select
    Selection.EntireColumn.Hidden = True
    Columns("X:Y").Select
    Selection.EntireColumn.Hidden = True
    Columns("U:V").Select
    Selection.EntireColumn.Hidden = True
    Columns("R:S").Select
    Selection.EntireColumn.Hidden = True
    Columns("O:P").Select
    Selection.EntireColumn.Hidden = True
    Columns("L:M").Select
    Selection.EntireColumn.Hidden = True
    Columns("I:J").Select
    Selection.EntireColumn.Hidden = True
    Columns("F:G").Select
    Selection.EntireColumn.Hidden = True
    Range("A1").Select

Windows("Staff sickness Template.xls").Activate
Sheets("2011").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("D5:H5").Select

End Sub

Hope this is enough info to help... please let me know if not.

**arlu1201** · 11-23-2011, 03:13 PM

Sub Populate()
'
' Populate Macro
'

'
    Sheets("Template").Select
    Sheets("2011").Visible = True
    Windows("Sickess Dbase 2011.xls").Activate
    Columns("D:AP").entirecolumn.hidden=false
    
    Windows("Staff sickness Template.xls").Activate
    Sheets("2011").range("E4:E80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("F4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("F4:F80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("I4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("G4:G80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("L4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("H4:H80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("O4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("I4:I80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("R4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("J4:J80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("U4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("K4:K80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("X4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("L4:L80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AA4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("M4:M80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AD4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("N4:N80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AG4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("Staff sickness Template.xls").Activate
    Range("O4:O80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AJ4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P4:P80").copy
    Windows("Sickess Dbase 2011.xls").Activate
    Range("AM4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    Range("G4:G40").copy
    Range("E4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J4:J40").copy
    Range("H4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("M4:M40").copy
    Range("K4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("P4:P40").copy
    Range("N4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("S4:S40").copy
    Range("Q4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("V4:V40").copy
    Range("T4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("Y4:Y40").copy
    Range("W4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AB4:AB40").copy
    Range("Z4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AE4:AE40").copy
    Range("AC4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AH4:AH40").copy
    Range("AF4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AK4:AK40").copy
    Range("AI4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AN4:AN40").copy
    Range("AL4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Range("F4:F40").clearcontents
    Range("I4:I40").clearcontents
    Range("L4:L40").clearcontents
    Range("O4:O40").clearcontents
    Range("R4:R40").clearcontents
    Range("U4:U40").clearcontents
    Range("X4:X40").clearcontents
    Range("AA4:AA40").clearcontents
    Range("AD4:AD40").clearcontents
    Range("AG4:AG40").clearcontents
    Range("AJ4:AJ40").clearcontents
    Range("AM4:AM40").clearcontents
    Columns("AM:AN").entirecolumn.hidden=true

    Columns("AJ:AK").entirecolumn.hidden=true
    Columns("AG:AH").entirecolumn.hidden=true
    Columns("AD:AE").entirecolumn.hidden=true
    Columns("AA:AB").entirecolumn.hidden=true
    Columns("X:Y").entirecolumn.hidden=true
    Columns("U:V").entirecolumn.hidden=true
    Columns("R:S").entirecolumn.hidden=true
    Columns("O:P").entirecolumn.hidden=true
    Columns("L:M").entirecolumn.hidden=true
    Columns("I:J").entirecolumn.hidden=true
    Columns("F:G").entirecolumn.hidden=true

Windows("Staff sickness Template.xls").Activate
Sheets("2011").Select
    ActiveWindow.SelectedSheets.Visible = False

End Sub

Your code could be reduced much more, but i have just quickly assisted you in removing all the select & activate statements (as much as i could). This will speed up your code and also take less space. It will be faster to debug.

**philwilliams80** · 11-24-2011, 05:13 AM

Many thanks for your assistance with the existing code Arlette.

Are there any suggestions for the additional bit of code needed as per te description in my second post?

Thanks
Phil

**6StringJazzer** · 11-26-2011, 11:16 AM

After spending some time trying to understand your problem I think this needs a complete overhaul. My assumption is that the Template workbook is used to report a single absence, and that single absence is added into the database.

I have redesigned your template workbook. The data entry sheet Template is the same so there is no change as far as the user is concerned. But I have moved the validation lists to another hidden sheet. This required creating named ranges for those lists.

The Template workbook has the 2011 sheet with a grid for all staff and all months, but the formulas only populate one cell at a time based on what's on the Template sheet. You can just read data directly from the Template sheet and you don't need sheet 2011 at all.

I have left the date selection alone but it could be improved by using a calendar control, rather than picking the day, month, and year separately. In addition, the user could select invalid dates such as November 31. You are not using anything but the month, however, so this may not be a problem.

I have added data validation for the number of days so that it is greater than 0. You may want to add a maximum.

When the Populate button is clicked, the database file is opened automatically, if it is not already open. You will find that this runs much faster than the original. In fact, you may not realize it worked; make sure to check your database data after clicking the button.

I have left the typo in for the name of the database file

There is a user interface problem. The user can enter start date, end date, and number of days. There is nothing that says that the user will enter them to be consistent, that is, he could enter absence starting 1/1/2012 for 1 day ending 6/1/2012. I didn't try to fix that but I could if I knew your holiday schedule. Also, there is no validation of any of the data entered by the user. This version requires all the data to be entered.

I hope you don't mind that I did more than just answer your question but I think you'll be happier with this result. If I have misinterpreted what you're trying to do please let me know and I'll stay on it.

**philwilliams80** · 12-01-2011, 08:19 AM

6StringJazzer:

You really have gone above and beyond the call of duty here. It certainly solves my problem, and fixes may of the lengthy workarounds that I had been using.

Obviously I know the whole system isn't perfect (as a result of how I set it up in the first place) with regards to the date issue, but I think due to the variable nature of holidays and weekends etc it is best to leave the 'Number of Days' as a field to be entered by the user rather than an automatic calculation.

Once again, many thanks for your help with this. It's really appreciated

Phil

**6StringJazzer** · 12-01-2011, 09:53 AM

I was willing to spend some extra time on your problem because obviously you have made a sincere and strong effort to solve it yourself, and are willing to do some of your own research. I always enjoy working with those types of people. (I never answer a question like, "I have this complex problem, and I would like you to design and build a spreadsheet for me to solve it, although I have done absolutely nothing myself to figure it out.")

Plus I found it somewhat interesting but not a huge task. Glad to help.