Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 13th 10, 11:09 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2010
Posts: 1
Default Spreadsheet with 12 tabs all formatted the same. XL2003

I have Excel 2003 at work and 2007 at home. I am setting up a spreadsheet
with 12 tabs (months) and I want them to have the same format in all 12. I
know there has to be a way to do this quickly. I can do it in 2007 maybe
2003 does not have that capability.

  #2   Report Post  
Old January 13th 10, 11:49 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default Spreadsheet with 12 tabs all formatted the same. XL2003

Several ways to do this.

Insert sheets until you have 12 sheets.

Right-click and "select all sheets"

Do what you want to active sheet and will be done to all.

If you already have one sheet as you wish, simply copy that sheet once.

Now you have two sheets.

Select both sheets and copy.

Now you have 4 sheets

Select those 4 and copy again etc.

Do it with a macro.

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each mycell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = mycell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next mycell

End Sub


Gord Dibben MS Excel MVP

On Wed, 13 Jan 2010 14:09:01 -0800, pegbill1
wrote:

I have Excel 2003 at work and 2007 at home. I am setting up a spreadsheet
with 12 tabs (months) and I want them to have the same format in all 12. I
know there has to be a way to do this quickly. I can do it in 2007 maybe
2003 does not have that capability.


  #3   Report Post  
Old January 18th 10, 02:36 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2009
Posts: 20
Default Spreadsheet with 12 tabs all formatted the same. XL2003

Hi,

I see this thread.

I tried the first 2 method.

It works fine in the 1st method.

For the 2nd method, kindly explain further.

For ex - if i have 12 sheets. I will highlight the whole sheet 1, copy and
paste in sheet 2. Then highlight both sheet 1 & 2, then copy and paste ?


Gord Dibben wrote:
Several ways to do this.

Insert sheets until you have 12 sheets.

Right-click and "select all sheets"

Do what you want to active sheet and will be done to all.

If you already have one sheet as you wish, simply copy that sheet once.

Now you have two sheets.

Select both sheets and copy.

Now you have 4 sheets

Select those 4 and copy again etc.

Do it with a macro.

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each mycell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = mycell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next mycell

End Sub

Gord Dibben MS Excel MVP

I have Excel 2003 at work and 2007 at home. I am setting up a spreadsheet
with 12 tabs (months) and I want them to have the same format in all 12. I
know there has to be a way to do this quickly. I can do it in 2007 maybe
2003 does not have that capability.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...setup/201001/1

  #4   Report Post  
Old January 18th 10, 09:20 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,907
Default Spreadsheet with 12 tabs all formatted the same. XL2003

If you already have 12 sheets, no need to copy anything.

Method 2 was to be used if you have only 1 sheet to start with.

Select tab of sheet1 and CRTL + drag to create a copy of that sheet.

Now select tab of sheet1 and sheet1(2) and right-clickmove or copycreate a
copy and OK

Now you have 4 sheets.

Etc. Etc.


Gord

On Mon, 18 Jan 2010 13:36:41 GMT, "tabylee via OfficeKB.com" <[email protected]
wrote:

Hi,

I see this thread.

I tried the first 2 method.

It works fine in the 1st method.

For the 2nd method, kindly explain further.

For ex - if i have 12 sheets. I will highlight the whole sheet 1, copy and
paste in sheet 2. Then highlight both sheet 1 & 2, then copy and paste ?


Gord Dibben wrote:
Several ways to do this.

Insert sheets until you have 12 sheets.

Right-click and "select all sheets"

Do what you want to active sheet and will be done to all.

If you already have one sheet as you wish, simply copy that sheet once.

Now you have two sheets.

Select both sheets and copy.

Now you have 4 sheets

Select those 4 and copy again etc.

Do it with a macro.

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each mycell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = mycell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next mycell

End Sub

Gord Dibben MS Excel MVP

I have Excel 2003 at work and 2007 at home. I am setting up a spreadsheet
with 12 tabs (months) and I want them to have the same format in all 12. I
know there has to be a way to do this quickly. I can do it in 2007 maybe
2003 does not have that capability.


  #5   Report Post  
Old January 21st 10, 06:29 PM posted to microsoft.public.excel.setup
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 4
Default Spreadsheet with 12 tabs all formatted the same. XL2003

How about the format painter??
open the sheet with the good formatting select the very top left colum and
row heading box. this will "select all" then click the paint brush button on
the menu bar open the tab you want to fix and click the top left heading box
"select all" and it will make the formatting the same on both tabs.

"Gord Dibben" wrote:

If you already have 12 sheets, no need to copy anything.

Method 2 was to be used if you have only 1 sheet to start with.

Select tab of sheet1 and CRTL + drag to create a copy of that sheet.

Now select tab of sheet1 and sheet1(2) and right-clickmove or copycreate a
copy and OK

Now you have 4 sheets.

Etc. Etc.


Gord

On Mon, 18 Jan 2010 13:36:41 GMT, "tabylee via OfficeKB.com" <[email protected]
wrote:

Hi,

I see this thread.

I tried the first 2 method.

It works fine in the 1st method.

For the 2nd method, kindly explain further.

For ex - if i have 12 sheets. I will highlight the whole sheet 1, copy and
paste in sheet 2. Then highlight both sheet 1 & 2, then copy and paste ?


Gord Dibben wrote:
Several ways to do this.

Insert sheets until you have 12 sheets.

Right-click and "select all sheets"

Do what you want to active sheet and will be done to all.

If you already have one sheet as you wish, simply copy that sheet once.

Now you have two sheets.

Select both sheets and copy.

Now you have 4 sheets

Select those 4 and copy again etc.

Do it with a macro.

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col A in a sheet: List
' Sub will copy sheets based on the sheet named as: Template
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim mycell As Range

Set TemplateWks = Worksheets("Template")
Set ListWks = Worksheets("list")
With ListWks
Set ListRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each mycell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
ActiveSheet.Name = mycell.Value
If Err.Number < 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next mycell

End Sub

Gord Dibben MS Excel MVP

I have Excel 2003 at work and 2007 at home. I am setting up a spreadsheet
with 12 tabs (months) and I want them to have the same format in all 12. I
know there has to be a way to do this quickly. I can do it in 2007 maybe
2003 does not have that capability.


.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I pass arguments to my formatted empty spreadsheet file? Paul H[_2_] Excel Discussion (Misc queries) 3 July 25th 09 07:34 PM
plain text opens instead of formatted spreadsheet Stellir Excel Discussion (Misc queries) 0 November 12th 07 04:21 PM
XL2003 Paste not keeping link to source spreadsheet xrbbaker Excel Discussion (Misc queries) 4 January 18th 07 03:36 PM
XL2003: Count formatted cells (not coloured)... Birmangirl Excel Worksheet Functions 0 October 19th 06 01:16 PM
XL2003 won't print a col of my spreadsheet CanCom Bill Excel Discussion (Misc queries) 0 October 14th 05 08:22 PM


All times are GMT +1. The time now is 09:08 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017