Copy and paste values

**no3ntry** · 10-09-2011, 08:04 PM

I need to get a macro that will copy data into specified cells within a row.

SSA will have a value in C4 that will match one of the values in column A in SSB, When the macro runs I would like it to replace the data in various cells from SSA and pplace them in the row where C4 matches the cell in column A, then I want it to select the entire row and copy and paste values as the row has some formulas in that I would no longer want in there.

Hopefully that made sense and you guys will be able to help

**no3ntry** · 10-10-2011, 07:33 PM

anyone help me with this one ?

**arlu1201** · 10-11-2011, 06:17 AM

Please attach a sample workbook as its not easy to understand your problem without it.

**no3ntry** · 10-11-2011, 08:58 AM

in the attached, sheet 1 would be the job tracker and sheet 2 would be the individual job sheet.

IRL these are in seperate workbooks but for ease of purpose here they are in the same file.
Some of the blank columns in my real tracker sheet have vlookups in them to the individual job sheets, some are just blank.

I want a macro to fill in the blank columns based on the booking reference and then copy the whole row and paste values (to get rid of the vlookups)

The macro would be stored in the individual sheet and each individual sheet has a different file name so swapping sheets must use a reference such as activate.thisworkbook, rather than an absolute filename

Does this make any more sense ?

NOTE: the information in my real job sheet is not in row format as it appears here it is in various different cells and columns.

**no3ntry** · 10-11-2011, 04:53 PM

still noone ?

**royUK** · 10-12-2011, 02:03 AM

Why would you need a macro? What's wrong with VLOOKUP? Just set up a query sheet that populates when required

Adding individual sheets is a bad idea, you will just end up with so many sheets that you won;t be able to track them

**no3ntry** · 10-12-2011, 08:50 AM

I want to do it this way as i am probably going to end up with 1000s of job queries with 4 or 5 vlookups per row, If I have to open a spreadsheet up with 10,000 vlookups in it, it will just lock excel up.

I also need this macro to copy and paste values to get rid of the vlookups.
I will end up with 1000s of seperate sheets but I am quite happy with this as they will all by hyperlinked from the main job tracker.

Tahnks
James

**arlu1201** · 10-12-2011, 12:57 PM

Will each job sheet be a separate workbook? Isnt that too many workbooks to handle? It will be easier if all the job sheet details are in one file, isnt it? If one sheet runs out, you can use the other.

**royUK** · 10-12-2011, 01:38 PM

You are creating a monster that will be difficult to work with. A databse in Excel or Access would be the best way to do this

**no3ntry** · 10-12-2011, 05:46 PM

essentialy a database IS what I am creating.

Yes, each job will have a seperate workbook and linked from the main(database) spreadsheet via a hyperlink.

Column A contains the booking reference which is how I would refer to jobs(click on the hyperlink opens up the workbook for that job) whilst the job is still live a lot of the cells in that row would have vlookups in them etc to get the job status and various information off the seperate job workbooks. Then once the job is closed off I run a macro from the seperate worksheet that does a copy and paste values, and the way it will know which row to update would be the booking reference which is also stored in the seperate workbook as well as column A of the "database"

If I left the vlookups *live* after a few hundred jobs have been completed i would imagine this "database" workbook would be horrendous to work with.

Ta
James