Copy active sheet & Clear unlocked cells on new sheet

[Mjack59_59]

Hello,

I need help please. I've been working on a spreadsheet to help with my construction estimating. what I will do is start with one room input each wall area calculate materials needed for That room, studs, sheating, drywall, paint, etc. When you finish that room click a button add another room,etc.

What I would like to do is 1) copy an existing sheet, 2) Rename it a unique name not already in the wookbook, and then 3) clear all unlocked cells in the new sheet only. Items 1 & 2 I have managed to accomplish after extensive Web Surfing and brain teasing. You will notice I have a clear cells button @ H2 I would like for this to happen with the add a room button.

Also is it possible have formulas on one sheet update as sheets are added?



Any Help would be greatly appriciated.

Thanks Mjack59
P.s. Have atttached copy of workbook. Hope this helps

[folk754]

Just a couple of changes and you should be running.

Private Sub CommandButton1_Click()
Dim ActshNm As String
Dim s As Worksheet
ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet
Set s = ActiveSheet
ActNm = ActiveSheet.Name
'Here you can the macro you already have, to clear the cells.
CommandButton2_Click
On Error Resume Next
ActiveSheet.Name = "kitchen"
NoName: If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give Room a Unique Name.")
If ActiveSheet.Name = ActshNm Then GoTo NoName
On Error GoTo 0
'Just a perk, to rename the heading of the spreadsheet
s.Range("C2").Value = ActiveSheet.Name
End Sub
 
Private Sub CommandButton2_Click()
Dim s As Worksheet
'The macro runs from the button,
'and it seems it was not clear when running,
'that the active sheet was the new one.
'(It was taking the 'Kitchen' spreadsheet before.
'So here it solves it.
Set s = ActiveSheet
With s
Dim c As Range
On Error Resume Next
'Another way to use the range, is "Used range"
For Each c In s.UsedRange
If Not c.Locked Then c.ClearContents
If Not c.Locked And c.MergeCells Then
c.MergeArea.ClearContents
End If
Next c
End With
End Sub

Good luck!

[Mjack59_59]

Very Cool FOLK754,

The Code worked better than what was requested, the added line to rename form was a great Idea,as was being able to leave the clear button on the sheet in case of mistakes.

[Anne Troy]

Hi, mjack and folk! Nice to see you both!!

Folk: I fixed your VBA so it didn't stretch the screen width.. (just edited the one comment line)

Mjack: You can mark your own threads solved here by going to the Thread Tools dropdown at the top of your thread and choosing to Mark it solved.

[Mjack59_59]

Very Cool FOLK754,

The Code worked better than what was requested, the added line to rename C2 was a great Idea, as was being able to leave the clear button on the sheet in case of mistakes.

My Next problem may be harder to solve, in this work book I will have a sheet called "Whole Job take off". What will happen here is it will take data from lets say Sheet Kitchen cell I25 as you add a sheet it will ref the same cell on the new sheet.
Maybe I Should Close Out this thread and start a new one. Since original problem was solved?
Thank You Very much
Mjack59

[folk754]

MJack:

The is a feature on Excel called "Consolidate" (Data menu). That should do the job with a little work. You can also include a sum function (it works awesome, you can actually "roll up" spreadsheets, writing the sum function as =sum(Kitchen:Lastroom!B15)), and it will show the totals between those two spreadsheets.

Good luck,

Folk.

[Zack Barresse]

Hello there,

I might also suggest making use of a UDF to test if a sheet exists or not...

IN YOUR WORKSHEET MODULE

Option Explicit

Private Sub CommandButton1_Click()
    Dim s As Worksheet, sName As String
    ActiveSheet.Copy After:=ActiveSheet
    Set s = ActiveSheet
    Call CommandButton2_Click
    If DoesWsExist("Kitchen") Then
        sName = InputBox("Give Room a Unique Name.", "Name")
        If sName <> "" And DoesWsExist(sName) = False Then
            s.Name = sName
        End If
    Else: s.Name = "Kitchen"
    End If
    s.Range("C2").Value = s.Name
End Sub
Private Sub CommandButton2_Click()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If Not c.Locked Then
            c.ClearContents
            If c.MergeCells Then
                c.MergeArea.ClearContents
            End If
        End If
    Next c
End Sub

IN A STANDARD MODULE

Public Function DoesWsExist(wsName As String) As Boolean
    Dim ws As Worksheet
    On Error Resume Next
    Set ws = Worksheets(wsName)
    DoesWsExist = IIf(Not ws Is Nothing, True, False)
End Function

[Mjack59_59]

Hey Guys I Really appriciate all the help I have recieved here and quicknees of a reply.

Folk754 I have found a mistake with my original code, just under your line

set s =  on command button1

,

My line reads

ActNm = ActiveSheet.Name

Should be

ActshNm = ActiveSheet.Name

I forgot the sh This makes sure the sheet is a unique name

Again Thanks very much To ALL
Mjack59