I added the .Offset line to clear the rest of the line if Client is blank like in Line 7
Remove this line
.Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents
I added the .Offset line to clear the rest of the line if Client is blank like in Line 7
Remove this line
.Offset(0, 1).Resize(1, .Parent.Columns.Count - 2).ClearContents
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi, I have an add on to this now. I have included a new worksheet called MOST. I need to copy specific fields from the "MDS Equipment Detail Worksheet and put them into the MOST worksheet. On the MOST worksheet I have put the Headers from the MDS worksheet and their column letter so you can see what I am trying to copy over. I would like this set as a macro (button) that I can click once I have all the data keyed in properly on the MDS worksheet.
Thank you for any help.
Please see Sample Workbook_18
Try this
You might need to tweak it
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi,
If I wanted to use the Column Headers for reference on the MOST and MDS worksheet instead of the Column Number do I substitute the number i.e. 1 with the Header name i.e. "Oracle Project Code" on the MDS worksheet and "Project Number" on the MOST worksheet?
Additional Question: Why do I get the following when I go to look at my macro'swsMOST.Cells(i - 1, "Project Number").Value = .Cells(i, "Oracle Project Code").Value'Sample Workbook_19.xlsm'!VersionNotes.VersionNotes
Last edited by pawcoyote; 02-09-2017 at 08:34 AM.
1. I added new sub LUV to return the n-th row bu searching the col headers row to find the column string each time
I added error message to help figure out what was wrong with the column header
I would have been a LOT easier if the column headers in MDS and MOST were called the same
Option Explicit 'Look Up value Function LUV(rColHeaders As Range, sColHeader As String, N As Long) As Range Dim i As Long i = 0 On Error Resume Next i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0) On Error GoTo 0 If i <> 0 Then Set LUV = rColHeaders.Parent.Cells(N, i) Else MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name End If End Function Function GetColumnNumber(sColHeader As String, rColHeaders As Range) As Long Dim i As Long i = 0 On Error Resume Next i = Application.WorksheetFunction.Match(sColHeader, rColHeaders, 0) On Error GoTo 0 If i <> 0 Then GetColumnNumber = i Else MsgBox "Column " & sColHeader & " not found in row " & rColHeaders.Address & " on worksheet " & rColHeaders.Parent.Name End If End Function
Probably because you had 2 workbooks open with the same sheet names and macro namesAdditional Question: Why do I get the following when I go to look at my macro's
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
No idea why that sheet is being added
Ver 20 does not have it, so it must be something in your real workbook
It references another workbook if that helps
Capture.JPG
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Thank you, I will look at what I might have put in...
When I changed it to use column headers instead of column numbers, I had a couple wrong
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
That would have been on the MDS module correct? I want to be sure I am looking at the right mod for fixes.
Yes
The way I made the LUV() function it takes the col header string so look in that area
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Last edited by pawcoyote; 02-23-2017 at 11:22 AM.
Don't know what Jive is, but maybe don't use it
To allow some cells to be updated when the sheet is protected, you need to make .Locked = False
Option Explicit Sub ProtectMDS() With Worksheets("MDS Equipment Detail") If .ProtectContents Then Exit Sub Range(.Rows(1), .Rows(6)).Locked = True Range(.Rows(7), .Rows(.Rows.Count)).Locked = False .Protect Password:="password", UserInterfaceOnly:=True, DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowFiltering:=True End With End Sub Sub UnProtectMDS() With Worksheets("MDS Equipment Detail") If Not .ProtectContents Then Exit Sub .Unprotect Password:="password" End With End Sub
Last edited by Paul_Hossler; 02-24-2017 at 01:11 PM. Reason: Needed to allow filtering
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Hi, I added the code to the MDS Equipment Detail Sheet in VBA but I keep getting an error once I protect the sheet.
I need to unprotect the following when we enter the MDS Equipment Detail sheet. Allowing all VBA and Macros to run...
Row 5 & 6 Certain Headers Columns CA - CL (I want to be able to change the names of the headers as needed
Row 7 and on all fields to be open for running VBA and Macros..
I am also looking to only copy the information from the MDS Equipment Detail Sheet to the MOST Add Sheet when a Client Name is filled in the Client Name column otherwise do not copy those rows..
When I protect the MDS Equipment Detail sheet this is where the error goes too..
With rMDS
.Interior.Color = xlNone
Last edited by pawcoyote; 02-25-2017 at 11:42 AM.
1. I put the protect subs on mod_Util since you have to run them manually and added the CA-CL write capability, marked with <<< below so that you can change it
2. You must have changed the layout of MOST so the header row was wrongSub ProtectMDS() With Worksheets("MDS Equipment Detail") If .ProtectContents Then Exit Sub Range(.Rows(1), .Rows(6)).Locked = True Range("CA5").Resize(2, 16).Locked = False '<<<<<<<<<<<<<<<<<<< Range(.Rows(7), .Rows(.Rows.Count)).Locked = False .Protect Password:="password", UserInterfaceOnly:=True, _ DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _ AllowDeletingRows:=True, AllowFiltering:=True .EnableSelection = xlUnlockedCells End With End Sub Sub UnProtectMDS() With Worksheets("MDS Equipment Detail") If Not .ProtectContents Then Exit Sub .Unprotect Password:="password" End With End Sub
Public Const rowHeaderMOST As Long = 4 Public Const rowDataStartMOST As Long = 5
3. I changed the logic to not use the UsedRange on MDS but only down to the last row that has a Client
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Yes sorry I did add another Header in Column B.