EXCEL: copy from one tab to another tab

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

Attachments

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....
 
Joined
Oct 20, 2004
Messages
7,837
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.text
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.
And here is another way that might work....
 

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
 

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.
 
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.
 

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.
 
Joined
Sep 4, 2003
Messages
4,916
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
 
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top