Copy and paste the data in excel ignoring Protected cells.

[lalaarif1]

Dear Friends,

Please find the vba code below.
Its does copy & paste special data related to another sheet in the sheet.

The macro fails if there are protected cells in the sheet.

Could you please assist me in solving the issue.

Co
Sub Break_Link_Col_ActiveNext()
'
' Macro1 Macro
'
'Dim abc As Range
ActiveWindow.ActivateNext
Dim wb As Workbook
Dim wb2 As Workbook
Set wb = ThisWorkbook
Dim cell As Range


Set Rng = Application.InputBox( _
Title:="Break_links", _
Prompt:="Select a cell/range to break links", _
Type:=8)
If Rng Is Nothing Then
MsgBox "please select range/cell"
Else
m = Rng.Address
End If

'Below code would do a for loop
For Each cell In Range(m)
If IsEmpty(cell) Then
GoTo B
Else
abc = cell.Address
If (InStr(1, Range(abc).Formula, "!", vbTextCompare)) > 0 Then
Range(cell.Address).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
End If
B:
Next cell

Application.CutCopyMode = False

MsgBox "Break link done"

End Sub

[ORoos]

When a cell property is set to be 'locked' the for this to apply, the sheet needs to be protected (with or without a password).
If this is what you are referring to, the vba code could updated to unprotect the sheet, and protect again at the end.

Please Login or Register  to view this content.

If there is a password;

Please Login or Register  to view this content.

If we need to check if the sheet is protected, then the code will need a few more lines.

[lalaarif1]

Sorry ORoos.

This is not what I am looking for.

There is a template file. Few cells are protected and few cells can be modified.
In the modified cells I link the data of other files and other tabs.

I need to paste special the data when I send the file to my boss.

So the macro (attached above) does the work of paste specialing the cells.
Macro fails only when there are protected cells.

So I need help on macro which will ignore the protected cells and does paste special to unprotected cells. ( as defined in the above macro)

Please assist.

[ORoos]

If you simply want to skip protected cells when looping through your range, maybe adding the line below will work for you;

Please Login or Register  to view this content.

[lalaarif1]

Thanks a lot.

I am not too familiar with macros.

Could you please add this code in my VBA code and do me a favour, please ?

[ORoos]

Line inserted in your original code.
Not tested: Make a copy/backup of your workbook for testing.

Please Login or Register  to view this content.

[lalaarif1]

Sorry ORoos.
Its popping up debug error on line
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Could you please assist ?

[ORoos]

Without knowing how your worksheet is structured, I can't really know what the errors is.
If you have a sample workbook (copy without sensitive information) please attach it so members can see what you are working with.

[lalaarif1]

Dear ORoos,

PFA the sample workbook for your reference.

Please assist.

[ORoos]

Sorry I have no idea what you try to do.

What is the macro supposed to do?
The macro is referring to 2 workbooks, is there another workbook in play.
What data is coming from where?
Which cells is what data to be entered?

Maybe make a mock-up on what the result should be.

[lalaarif1]

PFA the revised file.

I have added the macro in the file.
The macro will paste special the data when the cells are selected.

When you run macro, select col. C.
Macro runs fine.

When you select col. C and D, the macro fails bcos col. D is protected.

So please assist me in solving the debug error.

[ORoos]

Have a look if the attached does what you need.

[lalaarif1]

Sorry ORoos,

The protected cell should be left as it is.
In the file you sent, when we run macro, col. D does not remain protected.

The macro should skip the protected cell and go to next cell and so on.

The protected cells should remain as it is since its password protected by management.

Please assist.

[ORoos]

Ok. If cell is protected, skip it.

Try the attached.

[lalaarif1]

Thanks a ton, ORoos.

You made my day.
Its working fine.

[lalaarif1]

Dear ORoos,
I more favour.

If I select multiple sheets and run the macro, its failing.
Is there any way to run this macro on multiple sheet or whole workbook ?

Please assist.

[ORoos]

Macro 2 will allow you to select a range or ranges on another sheet.

[lalaarif1]

Hi ORoos,

The macro fails when I select multiple worksheets and run the macro.

Could you please assist ?