Copy paste a static range and dynamic range excluding blanks into database

Hi JobsSteve

Do you want an update-record created if either column E or column F is non-blank???
Or must they BOTH be non-blank (for the same row)???

zeddy

Hi JobsSteve

You could use the Advanced Filter method to extract the required records for the update-to-database file.

I can post a demo of this using your file, if you don't mind me unlocking the vba project so that I can add my vba code.

zeddy

Hi JobSteve

OK, I'm assuming your answer is Yes. This means I can post my version for you to look at.

I have attached two files. One is a sample database file for checking that updates get posted.
The other file is the unlocked file with my code.

Copy these attached files to a folder of your choice for testing.

I simplified things by adding an 'extract-records' sheet, which I named [zeddy] (you can adapt this to suit if required).
A record will be created if either column E or column F is non-blank.
You can adjust the filter-criteria-range if you actually require BOTH of these to be non-blank for the same row.

Click your button [Upload to Database Button] to copy the relevant records to the database file.

If this does what you need, please let me know.
If it doesn't, please let me know anyway.

zeddy
Excel Opificer

Hi zeddy,
Thanks so much, it works perfectly! Apologies for taking so long to get back to you, I appreciate the efforts you put in!

I'm wondering is there a way to work it so there is no need to have the second worksheet titled "zeddy"? I'm looking at it from a "lowest common denominator" here and don't trust the users to not meddle with that second worksheet! I know I could hide it, but again, just don't want people interfering!

Also, any chance you'd like to educate me on how to unlock that workbook? I didn't mean to upload a locked one, it just slipped my mind... But I'm interested to know how you unlocked it?

Thanks again!

Hi Steve

No apologies required - always good to be appreciated.

Yes, of course it is possible to do without "zeddy"

It's just that sometimes it helps to see what the 'export records' look like first etc etc etc.

In the attached v2a file, there is no extra sheet used.

I've tweaked the completion message - just to show what can be done for your future use.
And I've added lots of comments to the updated code, to make it easy to follow.

Now, about that unlocking. I did check that there wasn't anything too sensitive first.

The ADMIN people on this site would probably ban me if I showed how to do this.
And they would probably delete that post as well anyway.
And probably all my other posts too.
I would be personna non gratin or some other cheesy unwanted Forum Contibutor.

zeddy