PDA

View Full Version : Solved: Copy active sheet & Clear unlocked cells on new sheet



Mjack59_59
03-13-2005, 03:45 PM
:hi: 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?



:help Any Help would be greatly appriciated.

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

folk754
03-13-2005, 04:32 PM
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
03-14-2005, 02:25 PM
Very :cool:Cool FOLK754,:cleverman

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
03-14-2005, 02:34 PM
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
03-14-2005, 02:38 PM
Very :cool:Cool FOLK754,:cleverman

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:thumb
Mjack59

folk754
03-14-2005, 02:47 PM
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
03-14-2005, 02:53 PM
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
03-14-2005, 03:25 PM
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