Copy and paste range depending on cell value

Luke777

New Member
Joined
Aug 10, 2020
Messages
24
Office Version
  1. 2007
Platform
  1. Windows
Hi all

Need a little help with this one.

I have an unspecified number of rows of data (2 is the first row - range B:M). The end of each row (N2) is either a "Y" or a "N".

I would like my code to check N2:N. Where it finds a "Y", do nothing. Where it finds a "N", I would like it to copy that row (Bx:Mx), paste it to another workbook (next blank row but I already know how to code the find next blank row part), change the "N" to a "Y" (for Yes its been copied) and then carry on checking for "N" in column N.

To explain a little further, I'm taking data from a series of single user workbooks and compiling the information into a central database - the code *must* run on the database workbook - I'm fine with getting the other users' workbook to open and copy over information from that workbook to the next blank row on my database workbook, but I don't know exactly how to stop it copying *everything* each time the code runs instead of just the new, previously uncopied data - the above is the 'solution' I've come up with (check Y/N value and copy&paste based on that result), however, I recognize that as the single-user workbooks data sets increase, the database is going to perform this check slower and slower as it has more Y/N checks to perform - but I'm no where near the coding level required to get around that as far as I can tell.

I had a thought that copying everything over each time wouldn't be so bad (not sure how much load that would be compared to performing y/n checks - but unfortunately, I cannot guarantee that once copied to the database workbook, information will not be added or edited in some way, meaning the simple copy and paste all solution isn't viable.

Anyway, any help will be more appreciated than you can possibly understand and *please*, if I've done a bad job of explaining anything, let me know and I'll do my best to explain (and quickly) any question you may have - though it is 20:35 here so I may be asleep by the time I get any replies.
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

ExcelGzh

Board Regular
Joined
Mar 29, 2020
Messages
130
Office Version
  1. 365
Platform
  1. Windows
Use the Find method to locate the first "Y" in column N, copy the row, change the "Y" to an "N", and then go back and Find again. When Find returns Nothing there are no more Y's. This method is very quick because it doesn't require the code to sequentially examine the contents of every cell in column N.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,364
Messages
5,595,722
Members
414,013
Latest member
tnobbs

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top