Consulting

Results 1 to 8 of 8

Thread: Solved: Copy active sheet & Clear unlocked cells on new sheet

  1. #1

    Solved: Copy active sheet & Clear unlocked cells on new sheet

    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

  2. #2
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location
    Just a couple of changes and you should be running.

    [VBA] 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
    [/VBA]

    Good luck!

  3. #3
    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.

  4. #4
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    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.
    ~Anne Troy

  5. #5
    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

  6. #6
    VBAX Newbie
    Joined
    Mar 2005
    Posts
    5
    Location
    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.

  7. #7
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,906
    Location
    Hello there,

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

    [vba]'*************************************************
    '********** 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
    '*************************************************
    '*************************************************[/vba]

  8. #8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •