Help please....
The second part of my post never showed up so I am reposting
I have the following code that creates a new sheet when activated. I also would like it create a new column on a 'total' worksheet each time it is run, with the new sheet name (myname?) from the code as the column header. I would also like to copy A5:A10 to the Total worksheet Public Sub CreateExtendedReservation() Dim wkSht As Worksheet Dim createSht As Worksheet Dim myName As String Application.ScreenUpdating = False Set createSht = Worksheets("CREATE RESERVATION") myName = createSht.Range("C5").Text On Error Resume Next Set wkSht = Worksheets(myName) On Error GoTo 0 If wkSht Is Nothing Then With Worksheets("EXTENDED RESERVATION") .Visible = True .Copy After:=Sheets(2) .Visible = False End With With ActiveSheet .Name = myName With .Range("C1:C7") .Value = createSht.Range("C2:C8").Value .Locked = True Range("c2").Select End With With .Range("B9:B104") ' .Formula = createSht.Range("B9:B104").Formula .Locked = True .FormulaHidden = False End With .Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End If Application.ScreenUpdating = True Sheets("create reservation").Select Range("c2:c8").Select Selection.ClearContents Range("c2").Select 'Sheets("createsht").Select End Sub Thanks in advance Steve |
Help please....
Am I doing something wrong???? Mostpost here have at least 1 helpful reply, as has been my experience with my own previous posts. If more info is needed I will gladly supply.
|
Help please....
On Tue, 16 Dec 2003 14:21:08 -0800, steveh wrote:
Am I doing something wrong???? Mostpost here have at least 1 helpful reply, as has been my experience with my own previous posts. If more info is needed I will gladly supply. You are not being patient. Don't forget that nobody here (I think) is being paid to do this. If you want an answer *now*, go pay somebody. I (for one) haven't reviewed your code yet (I saw it before I went to work), and it's quite possible that the true pros haven't either. Be patient and an answer will come. -- auric "underscore" "underscore" "at" hotmail "dot" com ***** Anytime things go better, you have overlooked something. |
Help please....
assume copy A5:A10 from createSht
Public Sub CreateExtendedReservation() Dim wkSht As Worksheet Dim createSht As Worksheet Dim myName As String Application.ScreenUpdating = False Set createSht = Worksheets("CREATE RESERVATION") myName = createSht.Range("C5").Text On Error Resume Next Set wkSht = Worksheets(myName) On Error GoTo 0 If wkSht Is Nothing Then With Worksheets("EXTENDED RESERVATION") .Visible = True .Copy After:=Sheets(2) .Visible = False End With With ActiveSheet .Name = myName With .Range("C1:C7") .Value = createSht.Range("C2:C8").Value .Locked = True Range("c2").Select End With With .Range("B9:B104") ' .Formula = createSht.Range("B9:B104").Formula .Locked = True .FormulaHidden = False End With .Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End If With Worksheets("Total") set rng = .Cells(1,"IV").End(xltoLeft)(1,2) .Range(.Cells(5,rng.column),.Cells(10,rng.column)) .Value = _ createSht.Range("A5:A10") .Cells(1,rng.column).Value = myName End With Application.ScreenUpdating = True Sheets("create reservation").Select Range("c2:c8").Select Selection.ClearContents Range("c2").Select 'Sheets("createsht").Select End Sub steveh wrote in message ... The second part of my post never showed up so I am reposting I have the following code that creates a new sheet when activated. I also would like it create a new column on a 'total' worksheet each time it is run, with the new sheet name (myname?) from the code as the column header. I would also like to copy A5:A10 to the Total worksheet Public Sub CreateExtendedReservation() Dim wkSht As Worksheet Dim createSht As Worksheet Dim myName As String Application.ScreenUpdating = False Set createSht = Worksheets("CREATE RESERVATION") myName = createSht.Range("C5").Text On Error Resume Next Set wkSht = Worksheets(myName) On Error GoTo 0 If wkSht Is Nothing Then With Worksheets("EXTENDED RESERVATION") .Visible = True .Copy After:=Sheets(2) .Visible = False End With With ActiveSheet .Name = myName With .Range("C1:C7") .Value = createSht.Range("C2:C8").Value .Locked = True Range("c2").Select End With With .Range("B9:B104") ' .Formula = createSht.Range("B9:B104").Formula .Locked = True .FormulaHidden = False End With .Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With End If Application.ScreenUpdating = True Sheets("create reservation").Select Range("c2:c8").Select Selection.ClearContents Range("c2").Select 'Sheets("createsht").Select End Sub Thanks in advance Steve |
Help please....
To
Thanks for your work.This brings over the new sheet name, however, it doenst bring over A5:A10 |
Help please....
Sure it does:
.Range(.Cells(5,rng.column),.Cells(10,rng.column)) .Value = _ createSht.Range("A5:A10") However, you never said A5:A10 from where. I stated I assumed createsht - but that may not be what you want. Modify the code to reflect the correct sheet where the A5:A10 is located. -- Regards, Tom Ogilvy "steveh" wrote in message ... Tom Thanks for your work.This brings over the new sheet name, however, it doenst bring over A5:A10 |
Help please....
Yes if createsht is the new sheet created by the code. The range to copy is actually every 4th line in range N12:P104. Thought I could modify code after using generic range, but that didnt work out too well.
Thanks |
All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com