+ Reply to Thread
Results 1 to 3 of 3

Problem with copy & paste complex context in multiple worksheets within same workbook

  1. #1
    Registered User
    Join Date
    07-02-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    3

    Unhappy Problem with copy & paste complex context in multiple worksheets within same workbook

    Hi All,

    I am new to vba coding.
    I have a workbook with many worksheets (tabs) and would like to create a macropage to copy & paste everything into one giant piece of worksheet.
    The following code works fine for one of my template, but failed in the others (showed bug in lastcol=... for sub copypaste)
    Since the worksheets are really complicated and had many merged cells, Here is what I have come up so far.
    Could you please have a look and help
    I also wish to copy and paste with exact functions (since many of the data contains referencing codes)
    Thanks!!!

    Brian

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Problem with copy & paste complex context in multiple worksheets within same workbook

    lastCol = ActiveSheet.Range("XFD1").End(xlToLeft).Column
    lastrow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
    You are using lastCol according to 2007+ vesrion of Excel.. but lastRow according to 2003..

    try to use
    However you are facing error in lastCol section..
    try this..

    Please Login or Register  to view this content.
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Registered User
    Join Date
    07-02-2013
    Location
    Hong Kong
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Problem with copy & paste complex context in multiple worksheets within same workbook

    Thanks a lot Roy!!!!!
    I think it will work on other templates.

    However, there is still a problem with pasting the copied values.
    I missed a line.
    I need to go back to the macro page.
    then select the first column that is empty to paste since ther eare multiple worksheets of content to paste
    (I think I should do something like select the furthest right cell and xlToLeft to select the next empty cell after the pasted content.

    Please Login or Register  to view this content.

+ 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