×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Macro error when cells locked

Macro error when cells locked

Macro error when cells locked

(OP)
I am trying to create a template that once a few cells are filled in the user can run a macro and auto populate specific named sheets to enter specific info.

To use this spreadsheet the user is required to enter specifics (Series/Chute#) in Cells A2&B2,A3&B3.... within the summary sheet then Select the CREATE SHEETS macro button. From there the user goes into each sheet created and enter the values in Cells D-F and those values then transfer back to the summary sheet.

I have 3 main issues I am looking for help not sure if I can fix this within the macro or a special formatting function.

1.On the summary sheet I tried locking the cells for columns C-F to avoid the users entering information directly, but when I run the macro with protected sheet it just transfers everything from the summary sheet to each auto created sheet. Works when the workbook is unprotected, but hoping there is a way to keep the cells with formulas on the summary sheet locked.

2. When the macro creates the new sheets it also copy's over the Macro button and the highlighting format of columns A&B.Is there a way to stop the macro button and highlighting from Columns A&B from coping over to the new sheets?

3. After running this macro if a few more series to the summary sheet were added is there an easy way to run the macro again, but code it to say ignore all sheets that have been created already and only create new sheets for the additional added series?


Below is the current VBA im using and attached is the file if what I have summarized above doesn't make sense.


Dim xRCount As Long
Dim xSht As Worksheet
Dim xNSht As Worksheet
Dim I As Long
Dim xTRrow As Integer
Dim xCol As New Collection
Dim xTitle As String
Dim xSUpdate As Boolean
Set xSht = ActiveSheet
On Error Resume Next
xRCount = xSht.Cells(xSht.Rows.Count, 1).End(xlUp).Row
xTitle = "A1:C1"
xTRrow = xSht.Range(xTitle).Cells(1).Row
For I = 2 To xRCount
Call xCol.Add(xSht.Cells(I, 1).Text, xSht.Cells(I, 1).Text)
Next
xSUpdate = Application.ScreenUpdating
Application.ScreenUpdating = False
For I = 1 To xCol.Count
Call xSht.Range(xTitle).AutoFilter(1, CStr(xCol.Item(I)))
Set xNSht = Nothing
Set xNSht = Worksheets(CStr(xCol.Item(I)))
If xNSht Is Nothing Then
Set xNSht = Worksheets.Add(, Sheets(Sheets.Count))
xNSht.Name = CStr(xCol.Item(I))
Else
xNSht.Move , Sheets(Sheets.Count)
End If
xSht.Range("A" & xTRrow & ":A" & xRCount).EntireRow.Copy xNSht.Range("A1")
xNSht.Columns.AutoFit
Next
xSht.AutoFilterMode = False
xSht.Activate
Application.ScreenUpdating = xSUpdate
End Sub


RE: Macro error when cells locked

Hi,

Your upload is not a valid link.

Why not have a separate sheet, other than the Summary sheet, that you copy as your template, sans locked cells and controls?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Macro error when cells locked

In answer to 1, usually if you're working with protected cells you'll have to disable the protection temporarily in the start of your macro, and then re-enable it when you're finished. So user sees no change in the protection essentially.

RE: Macro error when cells locked

(OP)
Agent666,
That suggesting worked perfectly thanks for pointing that out.

JG2828,

Thanks for the info on file names and creating a valid attachment link.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close