+ Reply to Thread
Results 1 to 8 of 8

Copy and paste dynamic range

  1. #1
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Copy and paste dynamic range


    Hi All, I'm working with the following code:
    Please Login or Register  to view this content.
    The purpose of it is to copy a worksheet of one workbook and paste to another workbook, and then sub-divide the data into individual worksheets, and lastly renaming the new file and closing it. There are two dynamic ranges in the original file where the code attempts to find the last row of the data and copy the 'legitimate' data. The data ranges, called in the code PartsLR and FaultLR, have a maximum data range of 21 rows 31 rows, respectively. The purpose of the export is for me to in turn import the data into an Access database. With this code, somehow the range for PartLR is working OK but the FaultLR is considered as having data, so all 31 rows are being imported, even though most of them don't have data. I can't find where this is breaking down. Can anyone help? Thanks in advance.
    Last edited by wpryan; 04-18-2010 at 08:06 PM. Reason: it's solved

  2. #2
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Copy and paste dynamic range

    I'm guessing the AN column has formulas in it that can result in a blank. A formula such as:
    =if A1<2,"small number,"")

    Using the End(xlUp) method will find the last cell with a formula/entry. You can use the find method to find the last non blank cell.

    Please Login or Register  to view this content.
    Last edited by mdbct; 04-15-2010 at 02:45 PM.

  3. #3
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Copy and paste dynamic range

    Thanks for your response. You're exactly right, there are underlying formulas that are in these cells. I changed the code as you suggested by replacing the line 'FaultLR = Sheets("ReportSummary").Range("AN" & Rows.Count).End(xlUp).Row' with the line you provided but am getting an error "Run-time error '448': Named argument not found. Any ideas?

  4. #4
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Copy and paste dynamic range

    I fat fingered the D in SearchDirection and made it SearchSirection when I was cleaning up the capitalization. I've updated my post to the correct spelling.

  5. #5
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Copy and paste dynamic range

    That did the trick. Thanks much for your help!

  6. #6
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Copy and paste dynamic range

    Sorry to bother again, however I just noticed another feature I didn't see before. The beginning part of the code
    Please Login or Register  to view this content.
    will copy the values from the first workbook to the second. This bit suffers from the same problem which caused me to start the thread... It is not getting the values only, it is getting the underlying formula as well and causing extra empty rows to be generated. I can't figure out how to get only the cells with legitimate data. Sorry again, I'm in the learning stage with VBA... For the rest it's working great.

  7. #7
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Copy and paste dynamic range

    Do the same thing for the PartsLR as you did with the FaultLR

    Please Login or Register  to view this content.
    The bit of code you posted will change the formulas to their values with this line
    Please Login or Register  to view this content.
    Blank formula results would result in blank cells. The problem is that you determine the last row before you convert the formulas to their values.

    I would suggest using a different method for converting the formulas to their values- It will speed up the macro's execution time. Currently you are affecting the entire sheet with the "Cells.Value = Cells.Value" line. Use this instead:
    Please Login or Register  to view this content.
    If you've already run the original macro on the sheet without saving and closing the book, my suggestion will be run against the entire sheet since the LastCell is the lower right corner of the spreadsheet due to if being acted upon by the " Cells.Value = Cells.Value"

  8. #8
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Copy and paste dynamic range

    Thanks for your quick reply. Let me summarize again what I'm trying to do (and my apologies if I'm becoming bothersome)...

    The code should 1/ take one worksheet from one workbook, copy the 'legitimate' values (no underlying formulas, which it seems to be doing), and paste into a new workbook. From there code should 2/ create eight new worksheets and 3/ sub-divide the data (copy and paste) from the imported worksheet into various worksheets. Parts 2 and 3 are doing exactly what I want - copying and pasting only the values. However, Part 1 is somehow still considering the underlying formulas to be data. I think I need to dim the data range in a similar way to the PartsLR and FaultLR, but I don't know how that can be done.
    By the way the other code you supplied to speed up the execution is working great!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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