formating paste problem

rjointern

New Member
Joined
Aug 16, 2005
Messages
11
Hi,

So I've created some code that will copy and paste data from one worksheet in one workbook to another new worksheet that I have to create. Is there any way to automatically keep the textsize, column spacings, row spacings etc from the data that was copied?

Also the way that i am copying is using the workbook.worksheet.range.copy code, is there any way to not have to use the range and just copy the entire sheet in it's entirety? This would alleviate the issue I think, but I have failed to find the proper coding.

thanks.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Can we see your code?

You can copy an entire worksheet like this.
Code:
Worksheets("Sheet1").Copy After:=Worksheets(Worksheets.Count)
 

rjointern

New Member
Joined
Aug 16, 2005
Messages
11
heres my code

here's all four lines of code for my copying..

Code:
Workbooks("omg.xls").Sheets("adfas").Range("a1:da150").Copy
Workbooks("bbq.xls").Worksheets.Add
ActiveSheet.Name = "Day " & r
ActiveSheet.Paste

I don't know the way you're doing it. I'm a real novice at vba, but fairly good at c++ but it's been a while... so it's a little change

thnx for the help
 

rjointern

New Member
Joined
Aug 16, 2005
Messages
11

ADVERTISEMENT

um actually, i have another question... how can i use that code and take the data that i am copying and paste it into another workbook?

i'm trying to use..

Code:
Worksheets("omg.xls").Copy After:=Worksheets(Worksheets.Count)
Workbooks("bbq.xls").Worksheets.Add
Activesheet.name = "Day " & r
activesheet.Paste

but it's pasting the data in the omg.xls and not in the bbq xls like i want to....

any help would be appreciated
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,057
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
Worksheets("omg.xls").Copy After:=Workbooks("bbq.xls").Worksheets(Workbooks("bbq.xls").Worksheets.Count)
 

rjointern

New Member
Joined
Aug 16, 2005
Messages
11
got a subscript out of range error.. and i also believe u meant to change omg.xls to my sheet name right?


Worksheets("asdfsa").Copy After:=Workbooks("bbq.xls").Worksheets(Workbooks("bbq.xls").Worksheets.Count)

thnx though
 

rjointern

New Member
Joined
Aug 16, 2005
Messages
11
omg!!

nevermind.. lol i'm so sorry it actually did work, my stupid testing earlier renamed one of the sheets so it didn't work initially... but i caught that..

thanks a lot man, you rock! :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,118,436
Messages
5,572,107
Members
412,441
Latest member
kelethymos
Top