Copy paste error in Excel 2010

Question

I have 2 sheets, say source sheet and destination sheet. Destination sheet has multiple random hidden rows, I want to copy data from source sheet to the destination one while skipping/ignoring/avoiding hidden rows in destination sheet. ***The data should be copied to visible rows/cells only.

I have tried copy-pasting a column from one Excel sheet to another. I have to exclude hidden rows from the paste area and I am capable of excluding by using "Alt +;" command. But while pasting data I face problem saying "Copy area and Paste area are not the same" Please provide me the solution if you can.

***Note while pasting in a single column and let it auto-fill also paste data in hidden rows.

 I want to avoid pasting data to hidden rows.

Answer 1

Could you use a formula to extract the data from the source into the correct cell in the destination? Otherwise, the manual method is to un-hide the rows and sort your destination table to bring the desired cells into one contiguous block, then do the copy/paste  and re-sort to the original sort.  You could also use a macro to basically step through the visible cells of the destination.

Answer 2

Hi Bernie,

 I tried Vlookup formula but it could not work,

Can you please explain what is macro or any related tutorial?

Muhammad

Answer 3

This macro willl copy a filtered range and paste it into a filtered range: select the ranges when prompted.

Sub TestMacro()

    Dim rngC As Range

    Dim rngP As Range

    Dim i As Integer

    Dim j As Integer

    Dim k As Integer

    Set rngC = Application.InputBox("Select source", Type:=8).SpecialCells(xlCellTypeVisible)

    Set rngP = Application.InputBox("Select destination", Type:=8).SpecialCells(xlCellTypeVisible)

    If rngC.Cells.Count <> rngP.Cells.Count Then

        If MsgBox("Cells counts don't match. Continue?", vbYesNo) = vbNo Then Exit Sub

    End If

    With rngC.Parent

        rngC.Copy .Cells(.Rows.Count, rngC(1).Column).End(xlUp)(3)

        Set rngC = Intersect(.Cells(Rows.Count, rngC(1).Column).End(xlUp).CurrentRegion, rngC.EntireColumn)

    End With

    i = 1

    For j = 1 To rngP.Areas.Count

        For k = 1 To rngP.Areas(j).Cells.Count

            rngC.Cells(i).Copy rngP.Areas(j).Cells(k)

            i = i + 1

            If i > rngC.Cells.Count Then GoTo FinishUp

        Next k

    Next j

FinishUp:

    rngC.Clear

End Sub