+ Reply to Thread
Results 1 to 8 of 8

Format cells while copying data from one spreadsheet to another

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    31

    Format cells while copying data from one spreadsheet to another

    Hey peeps, I'm having an issue with Excel's amazing auto-formatting. I'm entering 12-digit UPC codes consisting of all numbers into column D. When the codes are put in they are of course formatted awkwardly, but even when I change the format from "General" to "Numbers" and run my Macro, is either copies the value of the cell in the general formatting or pastes the value at the destination cell using general formatting.

    Here is my code that works with no errors.

    Please Login or Register  to view this content.
    I tried using an Integer instead of a string and the CInt function as follows but with no joy; ran into errors.
    Please Login or Register  to view this content.
    Any help would be awesome!

  2. #2
    Registered User
    Join Date
    07-27-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007/2010
    Posts
    86

    Re: Format cells while copying data from one spreadsheet to another

    Why don't you use copy?
    Please Login or Register  to view this content.
    Boon

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Format cells while copying data from one spreadsheet to another

    Thanks for the reply, unfortunately the code you posted gave me an error. Highlighting the "SrcStr" after the 'Set' and says
    Compile error: Object required

  4. #4
    Registered User
    Join Date
    07-27-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007/2010
    Posts
    86

    Re: Format cells while copying data from one spreadsheet to another

    What if you remove the 'set'?

  5. #5
    Registered User
    Join Date
    07-31-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Format cells while copying data from one spreadsheet to another

    It highlights the "SrcStr" in the "SrcStr.Copy" function with the following error:
    Invalid qualifier

  6. #6
    Registered User
    Join Date
    07-27-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007/2010
    Posts
    86

    Re: Format cells while copying data from one spreadsheet to another

    Dim SrcStr as Range
    Try

  7. #7
    Registered User
    Join Date
    07-31-2012
    Location
    California
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Format cells while copying data from one spreadsheet to another

    At first it gave me an error, but then I put the "Set" back in, as follows:

    Please Login or Register  to view this content.
    Works perfectly now, thank you bheanloh!

  8. #8
    Registered User
    Join Date
    07-27-2012
    Location
    Malaysia
    MS-Off Ver
    Excel 2007/2010
    Posts
    86

    Re: Format cells while copying data from one spreadsheet to another

    Opps. Forgot to mention that. Glad that I helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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