+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    02-26-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    29

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

    Hi All,

    So I'm trying to get a button on a spreadsheet to copy and paste data into a database. I have a static range that I need to copy and I have a range that is going to be changing (but will always start at E10 and F10) and will have blank spaces between the data too that I dont want. I want to be able to copy and paste the static range and loop that to include Ranges E10 and F10 down. But columns E and F will have blank lines between the data I dont want to copy. THere will however ALWAYS be data in column D that will be next to any entries into Columns E or F.


    Please Login or Register  to view this content.
    Going to upload a workbook to try and help explain what I mean... Hopefully it makes sense? I just cant seem to figure out the best way to do it. I tried a loop of the whole "With rws" bit and tried a dozen copy and pastes but nada!

    Any help would be much appreciated!
    Attached Files Attached Files
    Last edited by JobsSteve; 07-14-2020 at 10:31 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    402

    Re: 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

  3. #3
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    402

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

    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

  4. #4
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    402

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

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    02-26-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    29

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

    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!

  6. #6
    Valued Forum Contributor
    Join Date
    08-03-2012
    Location
    Newcastle
    MS-Off Ver
    Excel 2007, 2010, 2013, 2016, Office 365
    Posts
    402

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

    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
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy and paste a dynamic range and remove blanks
    By chrisellis250 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-10-2019, 06:09 AM
  2. [SOLVED] Dynamic Range From Another Sheet Excluding Blanks
    By fredfarmer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2019, 01:21 PM
  3. database: Copy data from range excluding blanks plus one cell on another column
    By danwoltrs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2016, 10:24 AM
  4. Copy static range and paste at new cell. Macro VBA
    By ECEUK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-02-2015, 07:47 AM
  5. Copy and Paste a range based on a cell value that is not static
    By JmundleBofA in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-10-2014, 03:25 PM
  6. paste static range into dynamic location with search
    By plague5050 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-03-2014, 09:12 PM
  7. copy data from dynamic range to static range
    By barkarlo in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-16-2009, 02:26 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1