Problem with Paste

[GN0001]

I put filter on a column

Then copy the cells and I want to paste the values on the same range from which I have made my copy from (to paste on non-adjacent cells), it doesn't work. It says the copy and paste area should be the same.

I posted a question, a team member replied to my question, but that case is different from this case, he gave a short cut how to paste the values to a different column.

Now, I need to paste the values to the same column that I have maded my copy from, and that column contains formulas.

I appreciate your response.
GN0001

 

[Luke M]

This can not be done as you describe. You will need to first sort your data, then filter, then copy and paste.

 

[GN0001]

This can not be done as you describe. You will need to first sort your data, then filter, then copy and paste.[/quote]

May I ask why? We filter and the I need to paste only on the filtered values which are non-adjacent cells. How could it be related to

This can not be done as you describe. You will need to first sort your data, then filter, then copy and paste.

Hello Luke
Are you saying that we can't paste on the a column which has a filter on? I think it is possible. Then what is solution if we want to pasted on a non adjacent cells. One the members showed me a short cut that take cares of this.
but that pastes to a different column.
Thanks
GN0001

 

[Luke M]

Correct, you can not copy from a discontinuous range and paste to a discontinuous range. You can paste a single value/formula to a discontuous range. You can paste a discontinuous range to a continuous range. If it's the same formula (relatively) in all the cells, I'd select a single formula and copy to discontinuous range, or select all the cells, type your formula, and confirm with Ctrl+Enter which confirms entry to all selected cells.

 

[GN0001]

Hello Luke,
Let me clarify on what you say,
If I have a discontinuous ranges as c1 c3 c4 and this range contains formulas.
If I would copy this range, can't I paste the range to c1 c3 c4?
I think we can, please advise me.
Regards,
GN0001

 

[SirJB7]

Hi, GN0001!
Are you pasting over itself?
Regards!

 

[GN0001]

Hi, GN0001!
Are you pasting over itself?
Regards!

SirJB7,
Yes I am pasting over itself.
Please save me from this frustration.
Regards,
GN0001

 

[SirJB7]

Hi, GN0001!
Why should be a reason for pasting something over itself, if pasting all attributes?
Please elaborate and explain what do you want to do.
Regards!

 

[Debraj]

@GN0001

• Use a helper column.. completely blank.
• After apply filter.. in blank helper column, use formula as "=A1" (1st visible cell from the source column)
• Now drag only in visible cells..
• Unfilter.. Copy helper column.. and paste over the source column.. using Skip Blank..

 

[GN0001]

Hi, GN0001!
Why should be a reason for pasting something over itself, if pasting all attributes?
Please elaborate and explain what do you want to do.
Regards!

Hello SirJB7,

Because I want to copy the formula results and paste as value over itself. For example I have done a VLookUp, I have some returned values and some #NA, I filter on returned values and I need to copy and paste returned values over themselves.

I can paste to a different column (one the members showed me how), but I want to copy over itself.


Regards,
GN0001

 

[GN0001]

@GN0001

• Use a helper column.. completely blank.
• After apply filter.. in blank helper column, use formula as "=A1" (1st visible cell from the source column)
• Now drag only in visible cells..
• Unfilter.. Copy helper column.. and paste over the source column.. using Skip Blank..

Hello Debraj,
How can I skip blanks?
Regards,
GN0001

 

[SirJB7]

Hi, GN0001!

So you're not pasting a range over itself with all its attributes but pasting a discontinuous range over itself pasting only values; that's reasonable and a very different thing from that I understood earlier.

Give a look at the uploaded file. This is the VBA code:

Option Explicit

Sub ItIsLateIWannaGoToSleepAndIRanOutOfCarlsberg()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRange = "C:C"
    ' declarations
    Dim rngAll As Range, rngFiltered As Range, rngEach As Range
    ' start
    Set rngAll = Worksheets(ksWS).Range(ksRange)
    ' process
    '  filter err values
    rngAll.AutoFilter Field:=3, Criteria1:=Array( _
        "#¡DIV/0!", "#¡REF!", "#N/A"), Operator:=xlFilterValues
    Set rngFiltered = rngAll.SpecialCells(xlCellTypeVisible)
    '  do the job
    For Each rngEach In rngFiltered.Areas
        With rngEach
            .Copy
            .PasteSpecial xlPasteValues
        End With
    Next rngEach
    '  un-filter
    rngAll.AutoFilter Field:=3
    ' end
    [A2].Select
    Set rngEach = Nothing
    Set rngFiltered = Nothing
    Set rngAll = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

 

[NARAYANK991]

Hi ,

Suppose you have filtered your data , and rows 3 , 11 and 17 are visible ; in some unused column , away from your data , say column AA , put in cell AA3 the formula =F3 , assuming that F is the column on which you filtered your data , and F3 , F11 and F17 are the cells which you want converted to values.

Copy the formula in the remaining 2 cells AA11 and AA17.

Select the range AA3 through AA17 , and do a Copy + Paste Special , Values , so that instead of the formulae =F3 , =F11 and =F17 , only the values are present.

Now go back to your data range , and clear the filter so that all data rows are visible.

Check that in column AA , only the cells AA3 , AA11 and AA17 have values ; if any other cells have values , you will need to clear them.

Now select the range AA3 through AA17 , and do a Copy ; select Paste Special ; on the dialog form , in the bottom left corner , you will see a checkbox labelled Skip Blanks. Check this , and click OK.

Narayan

 

[GN0001]

Hi ,

Suppose you have filtered your data , and rows 3 , 11 and 17 are visible ; in some unused column , away from your data , say column AA , put in cell AA3 the formula =F3 , assuming that F is the column on which you filtered your data , and F3 , F11 and F17 are the cells which you want converted to values.

Copy the formula in the remaining 2 cells AA11 and AA17.

Select the range AA3 through AA17 , and do a Copy + Paste Special , Values , so that instead of the formulae =F3 , =F11 and =F17 , only the values are present.

Now go back to your data range , and clear the filter so that all data rows are visible.

Check that in column AA , only the cells AA3 , AA11 and AA17 have values ; if any other cells have values , you will need to clear them.

Now select the range AA3 through AA17 , and do a Copy ; select Paste Special ; on the dialog form , in the bottom left corner , you will see a checkbox labelled Skip Blanks. Check this , and click OK.

Narayan

Hello Narayan,


Thank you for the response,

This

select the range AA3 through AA17 , and do a Copy + Paste Special , Values , so that instead of the formulae =F3 , =F11 and =F17 , only the values are present.

doesn't work,

It says that copy area and paste area is not the same.

I think this can be taken care by skip blanks, however I will try it a couple of time more to let you know the result.

Regards,
GN0001

Hi, GN0001!

Hi, GN0001!

So you're not pasting a range over itself with all its attributes but pasting a discontinuous range over itself pasting only values; that's reasonable and a very different thing from that I understood earlier.

Give a look at the uploaded file. This is the VBA code:

Option Explicit

Sub ItIsLateIWannaGoToSleepAndIRanOutOfCarlsberg()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRange = "C:C"
    ' declarations
    Dim rngAll As Range, rngFiltered As Range, rngEach As Range
    ' start
    Set rngAll = Worksheets(ksWS).Range(ksRange)
    ' process
    '  filter err values
    rngAll.AutoFilter Field:=3, Criteria1:=Array( _
        "#¡DIV/0!", "#¡REF!", "#N/A"), Operator:=xlFilterValues
    Set rngFiltered = rngAll.SpecialCells(xlCellTypeVisible)
    '  do the job
    For Each rngEach In rngFiltered.Areas
        With rngEach
            .Copy
            .PasteSpecial xlPasteValues
        End With
    Next rngEach
    '  un-filter
    rngAll.AutoFilter Field:=3
    ' end
    [A2].Select
    Set rngEach = Nothing
    Set rngFiltered = Nothing
    Set rngAll = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

So you're not pasting a range over itself with all its attributes but pasting a discontinuous range over itself pasting only values; that's reasonable and a very different thing from that I understood earlier.

Give a look at the uploaded file. This is the VBA code:

Option Explicit

Sub ItIsLateIWannaGoToSleepAndIRanOutOfCarlsberg()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRange = "C:C"
    ' declarations
    Dim rngAll As Range, rngFiltered As Range, rngEach As Range
    ' start
    Set rngAll = Worksheets(ksWS).Range(ksRange)
    ' process
    '  filter err values
    rngAll.AutoFilter Field:=3, Criteria1:=Array( _
        "#¡DIV/0!", "#¡REF!", "#N/A"), Operator:=xlFilterValues
    Set rngFiltered = rngAll.SpecialCells(xlCellTypeVisible)
    '  do the job
    For Each rngEach In rngFiltered.Areas
        With rngEach
            .Copy
            .PasteSpecial xlPasteValues
        End With
    Next rngEach
    '  un-filter
    rngAll.AutoFilter Field:=3
    ' end
    [A2].Select
    Set rngEach = Nothing
    Set rngFiltered = Nothing
    Set rngAll = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

 

[GN0001]

Hello Narayan,


Thank you for the response,

This

select the range AA3 through AA17 , and do a Copy + Paste Special , Values , so that instead of the formulae =F3 , =F11 and =F17 , only the values are present.

doesn't work,

It says that copy area and paste area is not the same.

I think this can be taken care by skip blanks, however I will try it a couple of time more to let you know the result.

Regards,
GN0001

Hi, GN0001!

So you're not pasting a range over itself with all its attributes but pasting a discontinuous range over itself pasting only values; that's reasonable and a very different thing from that I understood earlier.

Give a look at the uploaded file. This is the VBA code:

Option Explicit

Sub ItIsLateIWannaGoToSleepAndIRanOutOfCarlsberg()
    ' constants
    Const ksWS = "Hoja1"
    Const ksRange = "C:C"
    ' declarations
    Dim rngAll As Range, rngFiltered As Range, rngEach As Range
    ' start
    Set rngAll = Worksheets(ksWS).Range(ksRange)
    ' process
    '  filter err values
    rngAll.AutoFilter Field:=3, Criteria1:=Array( _
        "#¡DIV/0!", "#¡REF!", "#N/A"), Operator:=xlFilterValues
    Set rngFiltered = rngAll.SpecialCells(xlCellTypeVisible)
    '  do the job
    For Each rngEach In rngFiltered.Areas
        With rngEach
            .Copy
            .PasteSpecial xlPasteValues
        End With
    Next rngEach
    '  un-filter
    rngAll.AutoFilter Field:=3
    ' end
    [A2].Select
    Set rngEach = Nothing
    Set rngFiltered = Nothing
    Set rngAll = Nothing
    Beep
End Sub

Just advise if any issue.

Regards!

Hello Sir JB7,

I appreciate your time for writing this code. unfortunately, I don't have any knowledge of VBA code. However I will keep this code in my library to go back to that in the Future.

GN0001

 

[NARAYANK991]

Hi ,

I think I need to be more explicit ; before you do a copy from column AA to column F , you first need to convert the formulae in column AA to values.

In order to do that , you need to copy the range AA3 through AA17 to itself.

In order to do this , you first need to remove the filter on your data so that all rows are visible.

Once all the rows are visible , do a Copy + Paste Special , Values of the range AA3:AA17 onto itself. This will ensure that all the data in column AA is only values.

Now do a Copy + Paste Special of the range AA3:AA17 onto F3 ; in this action , check the Skip Blanks checkbox.

Narayan

 

[GN0001]

That's it...!. I really like it.
Thank you so very much.
Warm regards,
GN0001

 

[Debraj]

Narayan.. Thanks for handling the situation..