Help please....

[Guest]

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

 

[Guest]

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.

 

[Auric__]

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.

 

[Tom Ogilvy]

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

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

 

[Guest]

To
Thanks for your work.This brings over the new sheet name, however, it doenst bring over A5:A10

 

[Tom Ogilvy]

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

Tom
Thanks for your work.This brings over the new sheet name, however, it

doenst bring over A5:A10

 

[Guest]

Yes if createsht is the new sheet created by the code. The range to copy is actually every 4th line in range N12104. Thought I could modify code after using generic range, but that didnt work out too well.
Thanks