Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Hi Everyone,
I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Paul,
here is the code I have span for you so far Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub the problem i have is knwoing how much of the master sheet you want to coy. I can't imagine it is everything else this would mean the code that writes the sheet name to A1 of each cell is rendered useless. (It will be overwritten) let me know what range of the Master sheet you want copying and where to in each sheet somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Hi Everyone, I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Thanks for the Reply somethinglikeant,
I did want to Copy ALL of the Master Sheet to EACH of the New Sheets ( Because of Formatting and Column Widths etc ). Would it be Easier to do this First and then put the Name of EACH Sheet in Cell A1. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Paul, here is the code I have span for you so far Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub the problem i have is knwoing how much of the master sheet you want to coy. I can't imagine it is everything else this would mean the code that writes the sheet name to A1 of each cell is rendered useless. (It will be overwritten) let me know what range of the Master sheet you want copying and where to in each sheet somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Hi Everyone, I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Yep
Here you a- Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 pasteto = ActiveSheet.Name: Sheets("Master").Cells.Copy Sheets(pasteto).[A1] [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub send me an e-mail if you would like me to send the workbook I've been working with. unless, can you attach workbooks in google groups?? somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Thanks for the Reply somethinglikeant, I did want to Copy ALL of the Master Sheet to EACH of the New Sheets ( Because of Formatting and Column Widths etc ). Would it be Easier to do this First and then put the Name of EACH Sheet in Cell A1. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Paul, here is the code I have span for you so far Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub the problem i have is knwoing how much of the master sheet you want to coy. I can't imagine it is everything else this would mean the code that writes the sheet name to A1 of each cell is rendered useless. (It will be overwritten) let me know what range of the Master sheet you want copying and where to in each sheet somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Hi Everyone, I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Thanks somethinglikeant,
It does Insert & Number the Sheet Tabs and put the Sheet Tab Number in Cell A1 of EACH Sheet, but it does NOT Copy & Paste Anything from the Sheet Named Master. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Yep Here you a- Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 pasteto = ActiveSheet.Name: Sheets("Master").Cells.Copy Sheets(pasteto).[A1] [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub send me an e-mail if you would like me to send the workbook I've been working with. unless, can you attach workbooks in google groups?? somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Thanks for the Reply somethinglikeant, I did want to Copy ALL of the Master Sheet to EACH of the New Sheets ( Because of Formatting and Column Widths etc ). Would it be Easier to do this First and then put the Name of EACH Sheet in Cell A1. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Paul, here is the code I have span for you so far Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub the problem i have is knwoing how much of the master sheet you want to coy. I can't imagine it is everything else this would mean the code that writes the sheet name to A1 of each cell is rendered useless. (It will be overwritten) let me know what range of the Master sheet you want copying and where to in each sheet somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Hi Everyone, I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Hi Paul
I have it working on my example. can't think what's wrong drop me your e-mail address and i'll send this working version http://www.excel-ant.co.uk Paul Black wrote: Thanks somethinglikeant, It does Insert & Number the Sheet Tabs and put the Sheet Tab Number in Cell A1 of EACH Sheet, but it does NOT Copy & Paste Anything from the Sheet Named Master. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Yep Here you a- Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 pasteto = ActiveSheet.Name: Sheets("Master").Cells.Copy Sheets(pasteto).[A1] [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub send me an e-mail if you would like me to send the workbook I've been working with. unless, can you attach workbooks in google groups?? somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Thanks for the Reply somethinglikeant, I did want to Copy ALL of the Master Sheet to EACH of the New Sheets ( Because of Formatting and Column Widths etc ). Would it be Easier to do this First and then put the Name of EACH Sheet in Cell A1. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Paul, here is the code I have span for you so far Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub the problem i have is knwoing how much of the master sheet you want to coy. I can't imagine it is everything else this would mean the code that writes the sheet name to A1 of each cell is rendered useless. (It will be overwritten) let me know what range of the Master sheet you want copying and where to in each sheet somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Hi Everyone, I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Hi somethinglikeant,
I have Copied & Pasted the Code again and Run it. It does Not Copy ANY of the Data from the Sheet Named Master. I can't Understand why?. I don't Really want to Post my email Address for Obvious Reasons. Thanks Again. All the Best. Paul somethinglikeant wrote: Hi Paul I have it working on my example. can't think what's wrong drop me your e-mail address and i'll send this working version http://www.excel-ant.co.uk Paul Black wrote: Thanks somethinglikeant, It does Insert & Number the Sheet Tabs and put the Sheet Tab Number in Cell A1 of EACH Sheet, but it does NOT Copy & Paste Anything from the Sheet Named Master. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Yep Here you a- Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 pasteto = ActiveSheet.Name: Sheets("Master").Cells.Copy Sheets(pasteto).[A1] [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub send me an e-mail if you would like me to send the workbook I've been working with. unless, can you attach workbooks in google groups?? somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Thanks for the Reply somethinglikeant, I did want to Copy ALL of the Master Sheet to EACH of the New Sheets ( Because of Formatting and Column Widths etc ). Would it be Easier to do this First and then put the Name of EACH Sheet in Cell A1. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Paul, here is the code I have span for you so far Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub the problem i have is knwoing how much of the master sheet you want to coy. I can't imagine it is everything else this would mean the code that writes the sheet name to A1 of each cell is rendered useless. (It will be overwritten) let me know what range of the Master sheet you want copying and where to in each sheet somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Hi Everyone, I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
The problem is probably wordwrap
Sub SheetInsert() Dim s1 as string, s2 as String Dim x as Long, y as Long, i as Long Dim pasteto as String s1 = "How many sheets would you like to add?", "Insert Sheets" s2 = "What would you like the number of the first sheet to be?" x = InputBox(s1) y = InputBox(s2, "Insert Sheets") For i = 1 To x Sheets.Add After:=Sheets(sheets.count) pasteto = i + y - 1 ActiveSheet.Name = pasteto With Sheets(pasteto) Sheets("Master").Cells.Copy _ .Range("A1") .Range("A1").Value = .Name .Range("B22").Select End With Next i End Sub -- Regards, Tom Ogilvy "Paul Black" wrote in message ps.com... Hi somethinglikeant, I have Copied & Pasted the Code again and Run it. It does Not Copy ANY of the Data from the Sheet Named Master. I can't Understand why?. I don't Really want to Post my email Address for Obvious Reasons. Thanks Again. All the Best. Paul somethinglikeant wrote: Hi Paul I have it working on my example. can't think what's wrong drop me your e-mail address and i'll send this working version http://www.excel-ant.co.uk Paul Black wrote: Thanks somethinglikeant, It does Insert & Number the Sheet Tabs and put the Sheet Tab Number in Cell A1 of EACH Sheet, but it does NOT Copy & Paste Anything from the Sheet Named Master. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Yep Here you a- Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 pasteto = ActiveSheet.Name: Sheets("Master").Cells.Copy Sheets(pasteto).[A1] [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub send me an e-mail if you would like me to send the workbook I've been working with. unless, can you attach workbooks in google groups?? somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Thanks for the Reply somethinglikeant, I did want to Copy ALL of the Master Sheet to EACH of the New Sheets ( Because of Formatting and Column Widths etc ). Would it be Easier to do this First and then put the Name of EACH Sheet in Cell A1. Thanks in Advance. All the Best. Paul somethinglikeant wrote: Paul, here is the code I have span for you so far Sub SheetInsert() x = InputBox("How many sheets would you like to add?", "Insert Sheets") y = InputBox("What would you like the number of the first sheet to be?", "Insert Sheets") For i = 1 To x Sheets.Add ActiveSheet.Name = i + y - 1 [A1] = i + y - 1 ActiveSheet.Move After:=Sheets(Worksheets(Worksheets.Count).Name) Next i End Sub the problem i have is knwoing how much of the master sheet you want to coy. I can't imagine it is everything else this would mean the code that writes the sheet name to A1 of each cell is rendered useless. (It will be overwritten) let me know what range of the Master sheet you want copying and where to in each sheet somethinglikeant http://www.excel-ant.co.uk Paul Black wrote: Hi Everyone, I have a Sheet Named Master. I would like 2 Input Boxes :- Input Box 1 will ask me for the Number of Sheets I would like to be Inserted. Input Box 2 will ask me what the Number of the First New Sheets Tab is to be. If I was to ask for 50 Sheets to be Inserted and the Number of the First Sheet was to be 901 for Example, the 50 Sheets Tabs would go from 901 to 950. I would also like the Number of the Sheets Tab to be Placed in Cell A1 of EVERY Sheet as a Value. Finally I would like the Sheet Named Master to be Copied to EVERY Sheet and the Cursor to End Up in Cell B22 of EVERY Sheet. It would also be Very Useful if the Sheets to be Inserted are Inserted Directly After the Number of the Current Highest Sheet Number Used Please. I hope I have Explained this Clear Enough. Thanks in Advance. All the Best. Paul |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Copy this code into a module and run the Main sub Code: -------------------- Option Explicit Dim ns As Integer Dim fs As Integer Sub Main() Application.ScreenUpdating = False GetInfo CreateSheets Application.ScreenUpdating = True End Sub Sub GetInfo() ns = InputBox("Please enter the number of sheets required.", "Number of Sheets") fs = InputBox("Please enter the number of the first sheet.", "Number of First Sheet") End Sub Sub CreateSheets() Dim i As Integer For i = 0 To ns - 1 Sheets("Master").Copy After:=Sheets(Sheets.Count) Sheets("Master (2)").Name = fs + i Range("A1").Value = fs + i Range("B22").Select Next i End Sub -------------------- -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=566252 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Tom,
Thanks for the Reply. For some Reason the line ... s1 = "How many sheets would you like to add?", "Insert Sheets" .... comes up with a Syntax Error. Excelenator, Thanks for the Reply. It Produces the Correct Results. All the Best. Paul |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
s1 = "How many sheets would you like to add?", "Insert Sheets"
should have been s1 = "How many sheets would you like to add?" Just didn't get things all cleaned up when I tried to help you overcome your inability to discern your word wrap problem, but only exacerbated it by creating another problem you couldn't discern. Sorry about that. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Tom, Thanks for the Reply. For some Reason the line ... s1 = "How many sheets would you like to add?", "Insert Sheets" ... comes up with a Syntax Error. Excelenator, Thanks for the Reply. It Produces the Correct Results. All the Best. Paul |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Hi Tom,
I Appreciate your Help and Time on this. Unfortunately it is giving me an Object Required Error on ... Sheets("Master").Cells.Copy _ .Range("A1") .... for some Reason. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: s1 = "How many sheets would you like to add?", "Insert Sheets" should have been s1 = "How many sheets would you like to add?" Just didn't get things all cleaned up when I tried to help you overcome your inability to discern your word wrap problem, but only exacerbated it by creating another problem you couldn't discern. Sorry about that. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Tom, Thanks for the Reply. For some Reason the line ... s1 = "How many sheets would you like to add?", "Insert Sheets" ... comes up with a Syntax Error. Excelenator, Thanks for the Reply. It Produces the Correct Results. All the Best. Paul |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Think you/I have stumbled into a bug on that one.
Sub Eft() Worksheets(2).Cells.Copy _ Worksheets(1).Range("A1") End Sub works Sub Eft() With Worksheets(1) Worksheets(2).Cells.Copy .Range("A1") End With End Sub works, but adding a line continuation character Sub Eft() With Worksheets(1) Worksheets(2).Cells.Copy _ .Range("A1") End with End Sub doesn't work but adding the argument name Sub Eft() Worksheets(2).Copy _ Destination:=.Range("A1") End Sub works, so for some reason, excel doesn't recognize the .Range("A1") after the line continuation character even though I would expect this to be legal and the use of the named argument shows that it is. Sub SheetInsert() Dim s1 As String, s2 As String Dim x As Long, y As Long, i As Long Dim pasteto As String s1 = "How many sheets would you like to add?" s2 = "What would you like the number of the first sheet to be?" x = InputBox(s1) y = InputBox(s2, "Insert Sheets") For i = 1 To x Sheets.Add After:=Sheets(Sheets.Count) pasteto = i + y - 1 ActiveSheet.Name = pasteto With Sheets(pasteto) Sheets("Master").Cells.Copy _ Destination:=.Range("A1") .Range("A1").Value = .Name .Range("B22").Select End With Next i End Sub worked for me. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Hi Tom, I Appreciate your Help and Time on this. Unfortunately it is giving me an Object Required Error on ... Sheets("Master").Cells.Copy _ .Range("A1") ... for some Reason. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: s1 = "How many sheets would you like to add?", "Insert Sheets" should have been s1 = "How many sheets would you like to add?" Just didn't get things all cleaned up when I tried to help you overcome your inability to discern your word wrap problem, but only exacerbated it by creating another problem you couldn't discern. Sorry about that. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Tom, Thanks for the Reply. For some Reason the line ... s1 = "How many sheets would you like to add?", "Insert Sheets" ... comes up with a Syntax Error. Excelenator, Thanks for the Reply. It Produces the Correct Results. All the Best. Paul |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Sheets & Copy
Hi Tom,
The Final Solution Macro Works Perfectly Thank You. Many Thanks for the Time & Effort you have put in to Help me, it is Most Appreciated. All the Best. Paul Tom Ogilvy wrote: Think you/I have stumbled into a bug on that one. Sub Eft() Worksheets(2).Cells.Copy _ Worksheets(1).Range("A1") End Sub works Sub Eft() With Worksheets(1) Worksheets(2).Cells.Copy .Range("A1") End With End Sub works, but adding a line continuation character Sub Eft() With Worksheets(1) Worksheets(2).Cells.Copy _ .Range("A1") End with End Sub doesn't work but adding the argument name Sub Eft() Worksheets(2).Copy _ Destination:=.Range("A1") End Sub works, so for some reason, excel doesn't recognize the .Range("A1") after the line continuation character even though I would expect this to be legal and the use of the named argument shows that it is. Sub SheetInsert() Dim s1 As String, s2 As String Dim x As Long, y As Long, i As Long Dim pasteto As String s1 = "How many sheets would you like to add?" s2 = "What would you like the number of the first sheet to be?" x = InputBox(s1) y = InputBox(s2, "Insert Sheets") For i = 1 To x Sheets.Add After:=Sheets(Sheets.Count) pasteto = i + y - 1 ActiveSheet.Name = pasteto With Sheets(pasteto) Sheets("Master").Cells.Copy _ Destination:=.Range("A1") .Range("A1").Value = .Name .Range("B22").Select End With Next i End Sub worked for me. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Hi Tom, I Appreciate your Help and Time on this. Unfortunately it is giving me an Object Required Error on ... Sheets("Master").Cells.Copy _ .Range("A1") ... for some Reason. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: s1 = "How many sheets would you like to add?", "Insert Sheets" should have been s1 = "How many sheets would you like to add?" Just didn't get things all cleaned up when I tried to help you overcome your inability to discern your word wrap problem, but only exacerbated it by creating another problem you couldn't discern. Sorry about that. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Tom, Thanks for the Reply. For some Reason the line ... s1 = "How many sheets would you like to add?", "Insert Sheets" ... comes up with a Syntax Error. Excelenator, Thanks for the Reply. It Produces the Correct Results. All the Best. Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
How do I copy a header to insert on multilpe sheets in a workbook | Excel Discussion (Misc queries) | |||
Macro to insert copy and insert formulas only to next blank row | Excel Programming | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions | |||
Macro to insert new sheets and copy information. | Excel Programming |