Copy specific cells in a row from one workbook to another based on Cell value

**prvnchdry** · 05-11-2017, 05:14 AM

Hi All

I am new to VBA coding and in urgent need of a code that can copy few cells in a row from one workbook to another based on cell value.

To explain my requirement further I have a workbook named "temp" with one primary cell value which when changed to "Accepted" only specific cells (including "Accepted") should copy to another workbook named "perm". Upon pasting the row in "perm", at the beginning i.e. in the first column, a name lets say "Robert" should also be placed in.

Any help at this coding will be much appreciated.

Regards,
prvnchdry

**sintek** · 05-11-2017, 05:51 AM

Hi prvnchdry

Welcome to the forum....
Is it two different workbooks named [TEMP] and [PERM] or two different worksheets
If Workbooks, what are the sheet names within that we must copy from and copy to.

**prvnchdry** · 05-11-2017, 07:02 AM

Thank you for the reply.

Its two different workbooks. The sheet name in TERM is "old" and PERM is "new". Thanks for probing.

**sintek** · 05-11-2017, 07:14 AM

This code will give you an idea, however you need to give more information i.e.
In what column do we find the criteria "Accepted"
Must the entire row be copied or only certain columns
Where must it be pasted to on the new sheet etc etc etc

Or just upload a sample workbook depicting a before and after scenario where the workbook names are just sheet 1 and sheet2 names

Please Login or Register  to view this content.

**prvnchdry** · 05-11-2017, 11:47 AM

I have attached here both the sheets now.

In the "temp" sheet only the cells of my interest are visible. Upon choosing "Accepted" from the drop down in column AG, the row should copy and paste from Column B onwards in workbook "Perm".
And once this is pasted, the "name" should populate in the first column as depicted.

Thank you.

**sintek** · 05-11-2017, 12:06 PM

Right the amended code in Post 4 has been tested and works. Just one thing where can the code go and find the name to put in column A on sheets("New") Can't always be you I'm sure.

**prvnchdry** · 05-11-2017, 01:20 PM

Thanks a ton. Works like a charm. Exactly what I was looking for.

But one last thing, the whole row is getting copied to the "Perm" workbook. Since it has defined headers its important only the unhidden cells from "Temp" go and sit in this book.

And to your question, yes the name isnt always mine.

I am planning to tweak in here and handover to other individuals so that we all have these rows placed in Perm book on a sharepoint folder.

**sintek** · 05-11-2017, 01:27 PM

Which columns do you want to copy over and does the Temp sheet not contain the name that must go into Col A anywhere.

EDITED:

This solves the column issue....

Please Login or Register  to view this content.

Still would like to incorporate code to take the name from somewhere...Any ideas cause currently this is the code being used

Please Login or Register  to view this content.

**prvnchdry** · 05-11-2017, 01:32 PM

I want columns B, C, F, G, I, V, X, AG only to be copied. Yes the temp sheet doesnt have the Writer name in it and needs to be added in Perm once these specific columns gets copied.

Also if there are multiple rows in the temp sheet - all accepted, can this code help copying all these rows in one go?

**sintek** · 05-11-2017, 01:49 PM

Yes the temp sheet doesnt have the Writer name in it

What column contains this name

can this code help copying all these rows in one go?

Yes the code has been tested an copies all with "Accepted"

**prvnchdry** · 05-11-2017, 02:00 PM

Yesss the specified columns are copied. (y) But now there is one last thing troubling me, the code when run just copies the last row and not all the rows with accepted option.

**sintek** · 05-11-2017, 02:01 PM

See amended code in post 8

**prvnchdry** · 05-11-2017, 02:02 PM

Sorry missed to reply this....well can a range be validated with some names in it e.g prvnchdry, robert, dave, andrew?

**sintek** · 05-11-2017, 02:04 PM

What column in the Temp book would you like the Data Validation that the user chooses name

**prvnchdry** · 05-11-2017, 02:09 PM

Column AH should do.

**sintek** · 05-11-2017, 02:24 PM

K see updated code and Data validation in new worksheet...Think this one is solved....you agree.

**prvnchdry** · 05-11-2017, 11:36 PM

Thanks a ton friend! You exceeded my expectation with the last code of data validation...kudos (y)

**sintek** · 05-12-2017, 12:40 AM

It's a pleasure...Glad I could help. Thanks for the rep point.

**prvnchdry** · 05-12-2017, 02:27 AM

Hi again, the code has been highly appreciated in my team today. We have eliminated the manual efforts of copy pasting that consumed our daily work routine by 30%. So many thanks!

Also I have received one suggestion if there is a way to color code the names, either after pasting in Col A or before copying the cells - with the color predefined using conditional formatting. So a green fill for robert, red for dave, orange for prvnchdry and so on. This would help us differentiate in a single glance.

Thank you for looking into this.

**sintek** · 05-12-2017, 03:18 AM

Have a look at attached sample file. I put conditional formatting in Column A as per image
ColourFormatting.png

**prvnchdry** · 05-12-2017, 03:26 AM

I am sorry but is there a code within the perm book? I cant see it

**prvnchdry** · 05-12-2017, 03:34 AM

Originally Posted by prvnchdry

I am sorry but is there a code within the perm book? I cant see it

No worries I figured this out and its an excellent idea. Thank you very much.

**sintek** · 05-12-2017, 03:44 AM

Glad you got it working

Copy specific cells in a row from one workbook to another based on Cell value

LinkBack

Thread Tools

Rate This Thread

Display

Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Re: Copy specific cells in a row from one workbook to another based on Cell value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA code to copy specific cells based on cell value

[SOLVED] Copy filename of source workbook to specific cell in specific tab in destination workbook.

[SOLVED] Macro to copy specific data from one WB to specific cells in another WB based on specific

Copy 2 cells from each Row (into another Workbook) Based on Criteria of a third cell?

[SOLVED] Copy specific cell in various workbooks to another workbook, but in consequtive cells

[SOLVED] Copy specific cells based on one word in the cell, to a different worksheet

Excel 2007 : Copy data from cells in one workbook to another based on the cell headings

Bookmarks

Bookmarks

Posting Permissions