1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL: copy from one tab to another tab

Discussion in 'Business Applications' started by sk0101, Sep 25, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Hi,

    When I am coping a line with data from one tab to another tab using VBA code.
    A cell that is format as text doesnt get copied with the same format.

    Here is my code:
    Sub auto_open()
    Sheets("Sheet3").Range("A1:E1").Value = Sheets("Sheet1").Range("A1:E1").Value
    End Sub


    The Cell (E1) in Sheet1 tab is formated as text.


    Question:
    How can I copy the data from Sheet1 to Sheet3 and still have the format from Sheet1?
    I can't format manually Sheet3 to format text because Sheet3 will be created dynamically. Is there away when creating a sheet set the format to text to all the fields?


    Attached an example xls file.

    Please advice,

    Thank you.
     

    Attached Files:

    • ie.xls
      File size:
      33.5 KB
      Views:
      257
  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    In copying only the value, you are doing essentially the same thing as Paste Special, Values - and so getting rid of the formatting.
    I think
    Range("A1:E1").Copy
    Sheets("Sheet1").Range("A1:E1").PasteSpecial Paste:=xlFormats
    should work....
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    And here is another way that might work....
     
  4. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Hi slurpee55,

    I tried:
    Sheets("Sheet3").Range("A1:E1").Value = _
    Sheets("Sheet1").Range("A1:E1").Value.Text

    and I get a runtime error '424' Object required.


    I also tired another way, so I changed it to:
    Sheets("Sheet1").Range("A1:E1").Select
    Selection.Copy
    Sheets("Sheet3").Range("A1:E1").PasteSpecial Paste:=xlValues

    and it works fine, but I need to be in Sheet1 in order the code to work. If I am in another sheet and run the code, I get an error: "Run time error 1004"- Select method of Rnage class failed.

    How can I make it work and not worry about in what sheet I am in.

    Thanks for your help
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  6. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Thanks, I will take a look at the module.

    It's just wired, if I use my old code that doesnt copy the format.
    Sheets("Sheet3").Range("A1:E1").Value = Sheets("Sheet1").Range("A1:E1").Value

    It works everywhere, doesn't matter in which sheet I am currently in.
     
  7. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    You might try this
    Code:
    Sheets("Sheet1").Select
    Sheets("Sheet1").Activate
    Range("A1:E1").Select
    I think the problem is that when you have another sheet open, Sheet1 does not have focus.
     
  8. sk0101

    sk0101 Thread Starter

    Joined:
    Nov 8, 2007
    Messages:
    277
    Perfect. Works.. Thanks for your help.

    just curious, when the data gets copied to Sheet3 from Sheet1.
    In the sheet1 the data is selected (You can see the dotted lines across the data that was copied (A1:E1)) Is there away to de-selected, so the user will not see what data was copied.
     
  9. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Remove the .Text from the second line in the code below. It should appear as

    Code:
    Sheets("Sheet3").Range("A1:E1").Value = _
    Sheets("Sheet1").Range("A1:E1").Value
    Regards,
    Rollin
     
  10. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Hi Rollin,

    That is essentially what the OP had before, but was wanting to keep the formatting, so got that to work with
    Code:
    Sheets("Sheet1").Select
    Sheets("Sheet1").Activate
    Range("A1:E1").Select
    Selection.Copy
    Sheets("Sheet3").Range("A1:E1").PasteSpecial Paste:=xlValues
    However, as for not showing which cells were copied? Not really. You can hide the column, of course, or you can go to Format, Cells, Special and enter ";;;" (just 3 colons, no "") and the data will not be visible.
    Either way, you will still see at least the first item (in cell A1) in the formula bar at the top.
    If you had an extra column (A1:F1) and had all the data in B1:F1, and hid the data, I think all that would show in the formula bar would be the nothing that you had in A1.
    But you would have to copy to A1:F1 in Sheet3 and would end up with a blank column.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/753345

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice