paste - skipping cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pixel - 21 Jan 2007 13:29 GMT

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...

Reply to this Message

Bernie Deitrick - 21 Jan 2007 15:24 GMT

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

>I have protected and shared workbook. The data is in columns "A to J".
>Column "D" is locked.
[quoted text clipped - 11 lines]
> Thank you,
> Dubo, from Croatia...

Reply to this Message

pixel - 21 Jan 2007 16:13 GMT

> Dubo,
>
[quoted text clipped - 59 lines]
>> 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 !

Reply to this Message

Bernie Deitrick - 21 Jan 2007 20:44 GMT

>> Or use a macro:
>>
>> Sub DuboCopyMacro()

<snip>

>> Application.CutCopyMode = False
>>
[quoted text clipped - 18 lines]
>
> Once more thank you for your effort !

Reply to this Message

Bernie Deitrick - 21 Jan 2007 21:04 GMT

>> Or use a macro:
>>
>> Sub DuboCopyMacro()

<snip>

>> Application.CutCopyMode = False
>>
[quoted text clipped - 18 lines]
>
> 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

Reply to this Message