Copying worksheet data based on drop down list

[diannk]

Good evening,

I am trying to complete a small project and have run into a bit of an issue. I have one sheet that contains 7 columns and 100 rows. In this sheet I have employee name, job site, and hours for M-F. I have another sheet in the same workbook that allows the user to select the job site - what I would like to do is have the data in sheet 1 populate in sheet 2 if the job site in sheet 1 is the same as the selection in sheet 2.

Thank you in advance,
Diann

 

[Peter_SSs]

Diann

Welcome to the MrExcel board!

I believe this could be done with a macro or with formulas. Do you care which method?

In Sheet1 is the data in columns A:G? If not, where?

In Sheet2:
a) what cell is the selection of site in?
b) what columns is the data to go into?

 

[diannk]

Thanks Peter,

Makes no difference to me which one, either macro or formula. In sheet 1 the data is in columns A:G. In sheet 2 the selection is in cell A1, the data should start in A6:G6.

Thanks again,
Diann

 

[Peter_SSs]

Thanks Peter,

Makes no difference to me which one, either macro or formula. In sheet 1 the data is in columns A:G. In sheet 2 the selection is in cell A1, the data should start in A6:G6.

Thanks again,
Diann

Try this macro then. Modifications may be needed if, for instance, the code is to be run multiple times and the amount of data to be copied across could reduce. In this case some code to clear the data from Sheet2 (row 6 down) before the new data is copied across may need to be added. Anyway, give it a go and post back if you need further help.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopySiteData()
    <SPAN style="color:#00007F">Dim</SPAN> ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet
    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> Site <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> ws1 = Sheets("Sheet1")
    <SPAN style="color:#00007F">Set</SPAN> ws2 = Sheets("Sheet2")
    lr = ws1.Range("B" & Rows.Count).End(xlUp).Row
    Site = ws2.Range("A1").Value
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">With</SPAN> ws1.Range("A1:G" & lr)
        .AutoFilter Field:=2, Criteria1:="=" & Site
        .Copy Destination:=ws2.Range("A6")
        .AutoFilter
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[diannk]

Thank you Peter,

I'll give it a try now.

Diann