Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:


Range("C8").Select
Selection.Copy
Range("C17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False



Range("G8").Select
Selection.Copy
Range("E17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Thank you for any help.

Drop the copy|paste special|values and just assign the value.

Dim FromCell As Range
Dim DestCell As Range

With ActiveSheet
Set FromCell = .Range("c8")
Set DestCell = .Cells.Find(What:="", _
After:=FromCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If DestCell Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
End If
End With



Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
Selection.Copy
Range("C17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("G8").Select
Selection.Copy
Range("E17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

* * Dim FromCell As Range
* * Dim DestCell As Range

* * With ActiveSheet
* * * * Set FromCell = .Range("c8")
* * * * Set DestCell = .Cells.Find(What:="", _
* * * * * * * * * * * * * After:=FromCell, _
* * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * LookAt:=xlPart, _
* * * * * * * * * * * * * Searchorder:=xlByColumns, _
* * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * MatchCase:=False, _
* * * * * * * * * * * * * SearchFormat:=False)

* * * * If DestCell Is Nothing Then
* * * * * * 'do nothing
* * * * Else
* * * * * * DestCell.Value = FromCell.Value
* * * * End If
* * End With





Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
* * Selection.Copy
* * Range("C17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False

* * Range("G8").Select
* * Selection.Copy
* * Range("E17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Dave

Thank you... I wil give it a try...


/Kasper

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

* * Dim FromCell As Range
* * Dim DestCell As Range

* * With ActiveSheet
* * * * Set FromCell = .Range("c8")
* * * * Set DestCell = .Cells.Find(What:="", _
* * * * * * * * * * * * * After:=FromCell, _
* * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * LookAt:=xlPart, _
* * * * * * * * * * * * * Searchorder:=xlByColumns, _
* * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * MatchCase:=False, _
* * * * * * * * * * * * * SearchFormat:=False)

* * * * If DestCell Is Nothing Then
* * * * * * 'do nothing
* * * * Else
* * * * * * DestCell.Value = FromCell.Value
* * * * End If
* * End With





Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
* * Selection.Copy
* * Range("C17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False

* * Range("G8").Select
* * Selection.Copy
* * Range("E17").Select
* * Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
* * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
* * * * :=False, Transpose:=False
* * Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

I do have one objection... I need the macro to start searching after
an empty cell from C17 and down, not directly under "FromCell". How do
I do this?

After:=.range("C17"), _

(I tried to match what your posted code did <bg.)

Kasper wrote:
<<snipped
I do have one objection... I need the macro to start searching after
an empty cell from C17 and down, not directly under "FromCell". How do
I do this?

--

Dave Peterson

I tried to match what your code did, but I didn't do it right!

Kasper wrote:

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

Dim FromCell As Range
Dim DestCell As Range

With ActiveSheet
Set FromCell = .Range("c8")
Set DestCell = .Cells.Find(What:="", _
After:=FromCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If DestCell Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
End If
End With





Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.
The original cell is merged and the goal cell is not.
Normally this is not a problem, but since I started to protect the
sheet the problem has risen.

I have an idea that i cannot copy the merged cell since the cell next
to the goal cell is locked. The goal cell is not locked.
I need the cell after the goalcell to be locked so I cannot change
that.

Any ideas on how to get around this? Maybe rewriting the paste line?

Macro below:

Range("C8").Select
Selection.Copy
Range("C17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Range("G8").Select
Selection.Copy
Range("E17").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, Searchorder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Thank you for any help.

--

Dave Peterson- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

I do have one objection... I need the macro to start searching after
an empty cell from C17 and down, not directly under "FromCell". How do
I do this?

--

Dave Peterson

On 25 Feb., 14:55, Dave Peterson wrote:
I tried to match what your code did, but I didn't do it right!





Kasper wrote:

On 25 Feb., 14:34, Dave Peterson wrote:
Drop the copy|paste special|values and just assign the value.

* * Dim FromCell As Range
* * Dim DestCell As Range

* * With ActiveSheet
* * * * Set FromCell = .Range("c8")
* * * * Set DestCell = .Cells.Find(What:="", _
* * * * * * * * * * * * * After:=FromCell, _
* * * * * * * * * * * * * LookIn:=xlFormulas, _
* * * * * * * * * * * * * LookAt:=xlPart, _
* * * * * * * * * * * * * Searchorder:=xlByColumns, _
* * * * * * * * * * * * * SearchDirection:=xlNext, _
* * * * * * * * * * * * * MatchCase:=False, _
* * * * * * * * * * * * * SearchFormat:=False)

* * * * If DestCell Is Nothing Then
* * * * * * 'do nothing
* * * * Else
* * * * * * DestCell.Value = FromCell.Value
* * * * End If
* * End With

Kasper wrote:

Hi

I've created a macro to copy two values to another part of the sheet.

The macro ended up being:

Dim FromCell As Range
Dim DestCell As Range


With ActiveSheet
Set FromCell = .Range("c8")
Set DestCell = .Cells.Find(What:="", _
After:=.Range("C17"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Set FromCell2 = .Range("G8")
Set DestCell2 = .Cells.Find(What:="", _
After:=.Range("E17"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)


If DestCell Or DestCell2 Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
DestCell2.Value = FromCell2.Value
End If
End With


Although the "If DestCell Or DestCell2 Is Nothing Then" doesn't seem
to stop the macro. It runs through the "else" part anyway.