a copy and paste between Excel workbooks depending on user input

[pendle666]

Hello everyone

I have an Excel report generated from our database which includes, amongst other things

name of person
date of timesheet
employee ID
manager
approval date
cost centre


It's for all the workers. I need to copy/paste some of the workers' details into another workbook.

What I'd like is a dialog box asking for the worker name, which will then copy all lines with that worker name and paste it into the new workbook.

This report usually have several thousands lines, so it's not really practical to do it manually as it can be prone to error on part of the user.

It all sounds very simple, but I'm thinking perhaps it's not?

Many thanks in advance.

Pendle

thank you for helping

____________
Pendle

 

[PHV]

Add a filter to the name of person column.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pendle666]

I had wondered about that, but it's not practical. It's also possible that not all the details will be copied in one go - that's why I wnated to give the option of just tapping in the name.

thank you for helping

____________
Pendle

 

[SkipVought]

Hi,

Use MS Query as a PARAMETER query. It could be driven from a Data > Validation -- LIST of employees.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

Hello - thanks for this. I don't think I've got acess to MS Query.

The spreadsheet is sent to me automatically, I don't have the access to the data itself in the database, just as a generic user.

The filter option does work, but there over over 5000 lines in the report this week and over 100 people to be selected from that list.

It's done on an ad hoc basis which is why I thought the option of a dialog box and typing in the name to select the data would be preferable. I know that the Find/Replace box could be used, but I'd need it to stop whilst the person typed in the name required and then resume after hitting enter.

thanks
Pendle

thank you for helping

____________
Pendle

 

[SkipVought]

I don't think I've got acess to MS Query.

I'd be surprised if you did not.

What version of Excel?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[pendle666]

Hello
I'm using Office 2010.

It's on a citrix server, so I don't think anything extra can be added in......?



thank you for helping

____________
Pendle

 

[SkipVought]

Data > Get External Data > From other sources > From Microsoft Query...

In the Choose Data Source window...

Uncheck Use Query Wisard to...
select Excel Files* in the Databases tab -- OK

In the Select Workbook window, drill down to your workbook -- OK

In the Add Tables window...

Hit the Options button and check all boxes -- OK
Select your sheet/named range -- ADD

Structure your query in the GUI (similar to MS Access but lots crappier!)

File > Return data to Microsoft Office Excel

In the Import Data window

Select the Sheet/Cell where you want the data to begin
Hit the Parameters button and select the parameter option (the cell when your parameter will appear, I'd suggest option 3 with refresh)



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}