paste - skipping cell
 

I have protected and shared workbook. The data is in columns "A to J".
Column "D" is locked.

Is there a solution to copy paste rows? I don't need to c/p entire rows,
just columns A to J, skipping the D column.

I guess it can only be done by using office clipboard or making sort of
loop, checking for locked cells when pasting but im stuck with that..

Limiting to c/p only one row is not a problem.

Does anyone have solution? Link to explanation of using office clipboard?

Thank you,
Dubo, from Croatia...

paste - skipping cell
 

Dubo,

Move column D? Either before or after your data columns, or to another
worksheet within the workbook.

Or use a macro:

Sub DuboCopyMacro()

Dim myCR As Long
Dim myPR As Long
Dim Str1 As String
Dim Str2 As String
Dim InsertCells As Boolean

Str1 = "Select a cell in the row to copy"

InsertCells = (MsgBox("Do you want to insert (yes) or paste (no)", vbYesNo)
= vbYes)
If InsertCells Then
Str2 = "Select a cell where you want to insert the copy"
Else
Str2 = "Select a cell where you want to paste the copy"
End If

myCR = Application.InputBox(Str1, , , , , , , 8).Row
myPR = Application.InputBox(Str2, , , , , , , 8).Row

If InsertCells Then
Cells(myCR, 1).Resize(1, 3).Copy
Cells(myPR, 1).Resize(1, 3).Insert
Cells(myCR, 5).Resize(1, 6).Copy
Cells(myPR, 5).Resize(1, 6).Insert
Else
Cells(myCR, 1).Resize(1, 3).Copy _
Cells(myPR, 1).Resize(1, 3)
Cells(myCR, 5).Resize(1, 6).Copy _
Cells(myPR, 5).Resize(1, 6)
End If

Application.CutCopyMode = False

End Sub

HTH,
Bernie
MS Excel MVP


"pixel" wrote in message ...
I have protected and shared workbook. The data is in columns "A to J".
Column "D" is locked.

Is there a solution to copy paste rows? I don't need to c/p entire rows,
just columns A to J, skipping the D column.

I guess it can only be done by using office clipboard or making sort of
loop, checking for locked cells when pasting but im stuck with that..

Limiting to c/p only one row is not a problem.

Does anyone have solution? Link to explanation of using office clipboard?

Thank you,
Dubo, from Croatia...

paste - skipping cell
 

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Dubo,

Move column D? Either before or after your data columns, or to another
worksheet within the workbook.

Or use a macro:

Sub DuboCopyMacro()

Dim myCR As Long
Dim myPR As Long
Dim Str1 As String
Dim Str2 As String
Dim InsertCells As Boolean

Str1 = "Select a cell in the row to copy"

InsertCells = (MsgBox("Do you want to insert (yes) or paste (no)",
vbYesNo) = vbYes)
If InsertCells Then
Str2 = "Select a cell where you want to insert the copy"
Else
Str2 = "Select a cell where you want to paste the copy"
End If

myCR = Application.InputBox(Str1, , , , , , , 8).Row
myPR = Application.InputBox(Str2, , , , , , , 8).Row

If InsertCells Then
Cells(myCR, 1).Resize(1, 3).Copy
Cells(myPR, 1).Resize(1, 3).Insert
Cells(myCR, 5).Resize(1, 6).Copy
Cells(myPR, 5).Resize(1, 6).Insert
Else
Cells(myCR, 1).Resize(1, 3).Copy _
Cells(myPR, 1).Resize(1, 3)
Cells(myCR, 5).Resize(1, 6).Copy _
Cells(myPR, 5).Resize(1, 6)
End If

Application.CutCopyMode = False

End Sub

HTH,
Bernie
MS Excel MVP


"pixel" wrote in message ...
I have protected and shared workbook. The data is in columns "A to J".
Column "D" is locked.

Is there a solution to copy paste rows? I don't need to c/p entire rows,
just columns A to J, skipping the D column.

I guess it can only be done by using office clipboard or making sort of
loop, checking for locked cells when pasting but im stuck with that..

Limiting to c/p only one row is not a problem.

Does anyone have solution? Link to explanation of using office clipboard?

Thank you,
Dubo, from Croatia...




Thank you very much, it works very good!

Yes, I know the best solution is to move column to the end, but that column
is important for me and I would like to keep it in the middle of the screen.
I had to ask this question just to make sure if there is no other way but
moving it...

I have one more question because I haven't provided enough information..

Two things. I would like to do it without inputboxes because i would like
pasting only, and c/p range will be set by selecting the cell, that's fine -
I'll try to modify your code.
Second - I would like to paste several times. I think the best way would be
to use temporary place for copy range. For example one hidden sheet and then
when copying second time to overwrite the old data. Do you think it could
work?

Once more thank you for your effort !

paste - skipping cell
 

Or use a macro:

Sub DuboCopyMacro()

<snip

Application.CutCopyMode = False

End Sub

Thank you very much, it works very good!

Yes, I know the best solution is to move column to the end, but that
column is important for me and I would like to keep it in the middle of
the screen. I had to ask this question just to make sure if there is no
other way but moving it...

I have one more question because I haven't provided enough information..

Two things. I would like to do it without inputboxes because i would like
pasting only, and c/p range will be set by selecting the cell, that's
fine - I'll try to modify your code.
Second - I would like to paste several times. I think the best way would
be to use temporary place for copy range. For example one hidden sheet and
then when copying second time to overwrite the old data. Do you think it
could work?

Once more thank you for your effort !

paste - skipping cell
 

Or use a macro:

Sub DuboCopyMacro()

<snip

Application.CutCopyMode = False

End Sub

Thank you very much, it works very good!

Yes, I know the best solution is to move column to the end, but that
column is important for me and I would like to keep it in the middle of
the screen. I had to ask this question just to make sure if there is no
other way but moving it...

I have one more question because I haven't provided enough information..

Two things. I would like to do it without inputboxes because i would like
pasting only, and c/p range will be set by selecting the cell, that's
fine - I'll try to modify your code.
Second - I would like to paste several times. I think the best way would
be to use temporary place for copy range. For example one hidden sheet and
then when copying second time to overwrite the old data. Do you think it
could work?

Once more thank you for your effort !


Ooops, sorry about that last message. Hit the wrong button.

If you want to paste several times, you could use the pastespecial method
for each of the ranges, just make sure that you don't set the cutcopymode to
false in between.

For example...
Range("C9:F9").Copy
Range("C14:F14").PasteSpecial
Range("C16:F16").PasteSpecial

HTH,
Bernie
MS Excel MVP