+ Reply to Thread
Results 1 to 5 of 5

Copy+Paste using 'Range=Range' not working

  1. #1
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    7
    Posts
    4

    Question Copy+Paste using 'Range=Range' not working

    I'm trying to copy a large number amount of data from a table in one workbook into a table on another workbook.

    For some reason the code works if I copy and then paste the values:

    Workbooks("CaveatorM copy").Sheets("CaveatLists").Range("Table49[[ListDate]:[Current Role]]").Copy
    Workbooks("Pre-releaseReports").Sheets("CaveatLists").Range("SavedCaveats[[Date Saved]:[Current Role]]").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    but nothing happens if I try the Range=Range method:

    Workbooks("Pre-releaseReports.xlsm").Sheets("CaveatLists").Range("SavedCaveats[[Date Saved]:[Current Role]]").Value = Workbooks("CaveatorM copy.xlsm").Sheets("CaveatLists").Range("Table49[[ListDate]:[Current Role]]").Value

    The process is really slow which is why I am trying to speed it up this way, grateful if anyone can spot what I'm doing wrong!

    Thanks in advance

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Copy+Paste using 'Range=Range' not working

    Have you checked the ranges are the same size?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    7
    Posts
    4

    Re: Copy+Paste using 'Range=Range' not working

    Oooh no they're not the same! The number of columns are the same but the destination table starts out empty (so only one row) and the number of rows in the first table will change each time I do it. Will that method not work then? I could work out some code to calculate the number of rows required and put them in but I reckon that would slow it down anyway..
    Cheers

  4. #4
    Registered User
    Join Date
    06-09-2016
    Location
    UK
    MS-Off Ver
    7
    Posts
    4

    Re: Copy+Paste using 'Range=Range' not working

    ps. thanks for the link to the code tags, was wondering how to do that..

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Copy+Paste using 'Range=Range' not working

    When using range = range the 2 ranges need to be the same size, and shape, to get the expected results.

    Have you considered using Resize?

+ 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. Loop through files in folder, copy range, Paste range to original workbook
    By knevil in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-09-2016, 05:33 PM
  2. [SOLVED] Help! - Copy Range (Sheet1,A10:F50), Paste Range (Sheet 2,A1:F41), Offset Range & Repeat
    By cjtimmer in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-30-2015, 06:27 PM
  3. Copy and paste not working in dynamic table range!
    By dhcwong in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-04-2015, 01:33 AM
  4. [SOLVED] Copy paste in Range not working
    By vaibhav2312 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-15-2015, 07:08 AM
  5. Replies: 4
    Last Post: 03-17-2013, 05:58 AM
  6. Change range of cells within VBA macro and copy and paste to fixed cell range
    By Mannyny in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 07-20-2012, 11:51 AM
  7. Copy dynamic range from previous sheet and paste after last used row in a range
    By Mistweaver in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-04-2010, 06:24 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