How to delete specific columns in protected copy of a macro enabled excel?

[thirstforvba]

Ok. This is what I am exactly looking for. I work on a complicated (for me) macro enabled excel tool which we consider as a master copy. It has several sheets. But not everything is shared with the users. So once we compile data and we run a copy of the sheet the current macro deletes few sheets and protects few columns in a specific sheet and gives us a copy of the sheet. This is how exactly it has to run everyday. All I had to do was click on 'Run protected copy' button and it would do it for me. I didn't need to worry about anything until now. I am now dealing with users in a new country. Here the requirement is different. We have to retain only 1 specific sheet, the sheet where we protect cells. I have modified the macro to do this by extrapolating the code that what was already there. I added the all the other sheet numbers. It seemed simple. Sample below.

'---------------------start preparing protected copy-----------------------------------------------------------

'--------------hide settings tab and remove aux tabs-------------
Sheet7.Visible = xlSheetVeryHidden
Sheet8.Delete
Sheet9.Delete
Sheet6.Delete
Sheet10.Delete
Sheet11.Delete
Sheet14.Delete
Sheet3.Delete
Sheet4.Delete
'-------------------------------------------

'------------Delete columns in the protected copy--------------

But along with this, I also have to delete few columns. Column 1 (A), Column 2 (B) and everything from column 43 - 92 (AQ to CN). Note, we deal with excel rows and columns in the form of numbers and not alphabets (if this makes any difference). I have added the heading (as you see above) but don't know how to proceed after this. I did go through few tutorials online, but I am bit perplexed of how to do this. The reason why I am worried is, regularly it is meant protect few of these columns that I want them deleted. This is connected to another sheet and the color of the column. There are 3 (orange, blue and yellow). Blue always stay protected and yellow and orange doesn't. See existing macro below. Will the below macro impact the codes for deleting columns? I am not even sure if I was able to articulate myself well. This is best to my ability. I am available to answer any questions you may have. Note, I cannot keep experimenting with this as macro restricts us from running only one copy every 24 hours. Can you please help?

'----------------lock first row and columns of the color specified in the Settings tab-------------------
For i = 1 To Sheet2.Columns(Columns.count).End(xlToLeft).Column
If Sheet2.Cells(1, i).Interior.ColorIndex = Sheet7.Cells(2, 13).Interior.ColorIndex Then
Sheet2.Columns(i).Locked = True
Else

Sheet2.Columns(i).Locked = False
End If
'------------hide exclusions column---------
If Sheet2.Cells(1, i).Value = "Exclusions" Then
Sheet2.Columns(i).Hidden = True
End If
'---------------------------------------------
Next i
Sheet2.Rows(1).Locked = True

'-------------------------------------------------------------------

 

[thirstforvba]

SO I crossed the time limit of editing the above message. I was thinking of application perspective. If I can hide the above noted columns with a password will also do. So when I receive updated files from users I can only unhide the columns and run against Master tracker. This is how I envision it now. Can you help with this new updated request? I prefer hiding them with password and not actually deleting them.

 

[thirstforvba]

I have to create a protected sheet from a master excel. But now, before sharing protected sheet with 3rd party user, I have to hide few columns [Column 1 (A), Column 2 (B) and everything from column 43 - 92 (AQ to CN)] in the generated protected sheet and this should be password enabled. that is people shouldn't be able to unhide columns. So, when I receive the file back from them with their updates, I should be able to unhide these sheets using the password. How can I do that ensuring above columns are password enabled hidden when protected sheet is generated? Thank you.

 

[thirstforvba]

I haven't received any answers so desperately managed to do the following:

I have created a code to hide a set of columns when protected copy of my master excel is created.
For i = 43 To 93
Columns(i).Hidden = True
Next i

I have also developed a code to create a protected copy of a master file ensuring they are saved in the same folder.

Dim destfile As String
destfile = auxpath & "\" & Sheet7.Cells(2, 6) & " " & LValue & " -copy"
Sheet2.Protect Password:=Sheet7.Cells(2, 12), AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheet2.Activate
If Dir(destfile & ".xlsm") <> "" Then
ThisWorkbook.SaveAs destfile & " v" & Sheet7.Cells(2, 1).Value

Else
ThisWorkbook.SaveAs destfile
End If

Now when I open the protected copy, anyone can unhide the columns.

What I need is to be able to unhide the columns in the protected copy only when some password is input. I cannot seem to get around this in a protected copy. Can you please help?