+ Reply to Thread
Results 1 to 6 of 6

Macro copy and paste not pasting exactly whats in cell

  1. #1
    Registered User
    Join Date
    08-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    10

    Macro copy and paste not pasting exactly whats in cell

    Hi, the code below looks at column A for the active sheet and then copy and pastes rows based on column A to other sheets.

    Please Login or Register  to view this content.
    problem: when I run it,I am getting a bunch of "#ref"'s because the code is not copy and pasting exactly what is on the master sheet.. I cannot post the excel sheet because it has sensitive information on it but here is an example.
    On the master sheet, in cell AB8 is: "=1694200*ReferenceSheet!J4 ". When i run the code it will copy it to the correct sheet in cell AB5 but with "=1694200*ReferenceSheet!J1 " , therefor the result: #ref . My code is not changing anything on the 'reference sheet' and I can manually change "J1" to "j4" and it works fine, but there is too much data to change manually. How can I make the data copy and paste correctly?


    On another issue I have the following:

    Please Login or Register  to view this content.
    BUT, there are 15 sheets, and I need to do it for around 20 columns. What I am doing now is copy and pasting exactly what I posted above and changing the sheet names to the 15 sheets and then changing the columns one at a time. The result is 300 "with sheets(sheet)...(code).....End With. The procedure is too long to run. Is there a way to condense this? There are 15 sheets and the columns that i need to refer to are Z-AN.

    Thanks in advance!! :D
    Last edited by hey1234; 08-22-2014 at 02:28 PM.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,992

    Re: Help with this code

    Hi, hey1234,

    please have a look at Forum Rule #3 and wrap your procedures with code-tags in the opening post. And please consider another thread title as well.

    Ciao,
    Holger
    Last edited by HaHoBe; 08-22-2014 at 02:15 PM.
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    08-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Macro copy and paste not pasting exactly whats in cell

    Thanks for the heads up. My apologies.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,992

    Re: Macro copy and paste not pasting exactly whats in cell

    Hi, hey1234,

    you either shoudl copy to the very same row, use absolut addresses for the cells or make use of Indirect - depending on the number of cells in question.

    Please Login or Register  to view this content.
    Ciao,
    Holger

  5. #5
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: Macro copy and paste not pasting exactly whats in cell

    Sadly whenever you Copy/Paste or Copy/PasteSpecial formulas (even within the same worksheet), the cell references will adjust. This is true whether you use VBA or do it manually. To avoid this use:

    Please Login or Register  to view this content.
    in place of

    Please Login or Register  to view this content.
    Gary's Student

  6. #6
    Registered User
    Join Date
    08-08-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    10

    Re: Macro copy and paste not pasting exactly whats in cell

    Thanks for the advice everyone!

+ 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. [SOLVED] VBA code to save current worksheet as temporary PDF file and then add to my existing code
    By brianfromla in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-20-2014, 08:35 AM
  2. [SOLVED] VBA code for assigning a numeric code to text; then numeric code populates table
    By cteaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2014, 08:01 PM
  3. Replies: 2
    Last Post: 03-09-2013, 04:30 AM
  4. Replies: 2
    Last Post: 03-17-2011, 08:55 PM
  5. Replies: 0
    Last Post: 10-06-2006, 09:05 AM

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