Paste into only visible cells in Excel 2013?

So to confirm what you want to do on a smaller scale: You want to copy rows 1 to 10 to the visible rows of a second sheet, so maybe rows 2 4 6 8 10 12 14 16 18 20 ?

Are you dealing with just one column?
Could you run some sort of vlookup maybe and insert that formula in the blank column?
Is that column blank for all rows, including the hidden ones?

One option, assuming your blank destination column is column B, and you want to copy sheet1!column A, would be to put the following formula in your visible columns:

=index(sheet1!A:A,countif($B$1:$b[row number above first visible row]))

Update the [row number above first visible row] parameter, and add 1 to the countif if you don't have a header row.

Thomas

Thanks for posting.

So I have the modified formula:

=index('[Part-desc-check.xlsx]All-All Old'!$CD:$CD,countif(Sheet1!$AZ$1:$AZ41))

Where my source sheet is this All-All Old tab in the Part-desc-check.xslx workbook.

Then Sheet1 is the destination sheet and AZ is the column where I want to write the values. But I'm getting Too Few arguments message - like I don't have a criteria identified.

Also there is no reference or "key" field with which to do a VLOOKUP - I just know the values line up between these two fields based on the activities that got me to this point.

AZ41 is the last invisible cell, per your instructions and AZ42 is the first cell where I want this formula.

What am I doing wrong?

Thanks again

Bill

my bad, replace countif by counta

Ok so when I add this formula to a few cells in the destination sheetL

=INDEX('[Part-desc-check.xlsx]All-All Old'!$CD:$CD,COUNTA(Sheet1!$B$1:$B169))

 I see an immediate mismatch. For example, if I click on cell 170, the formula is pulling in 169, but it really should be pulling in B4. Because my source row is contiguous data and my destination row is non-contiguous, filtered data. So my filtered row shows rows 1, 42, 49, 170, etc.

Any ideas?

Thanks

Bill

Which means you have data in that new column, we need to change the formula so it only counts the visible rows.

Let's try:
=INDEX('[Part-desc-check.xlsx]All-All Old'!$CD:$CD,SUBTOTAL(3,Sheet1!$B$1:$B169))

After you've got that working, you're going to have to do a VB paste values before removing the filter. The easiest for that is to run the attached macro:

Sub PasteValues_OnTheSpot()
Dim rgSel As range, rgArea As range

application.Calculate
application.Calculation = xlCalculationManual
application.ScreenUpdating = False

On Error Resume Next

Set rgSel = selection.SpecialCells(xlCellTypeFormulas)

If Err <> 0 Then
    MsgBox "No formulas to convert to values."
    Exit Sub
End If

For Each rgArea In rgSel.Areas
    rgArea.Value = rgArea.Value
Next rgArea

application.Calculation = xlCalculationAutomatic
application.ScreenUpdating = True

End Sub

Select allOpen in new window

Hmm - I'm sorry if I'm not being clear. First, I do not have any values in the destination column.

Second, your new formula, when applied to the first cell in my filtered destination sheet, is an immediate mismatch.

This destination column is filtered and currently showing row 1 (label), then row 42, 49, 170, 446, 452, etc - it's all over the place.

The source sheet is using column CD, unfiltered. I need CD row 2 to populate AZ row 42. I need CD row 3 to populate AZ row 49. Etc:

Source Cell               Destination Cell
CD2                           AZ42
CD3                           AZ49
CD4                           AZ170
CD5                           AZ446
CD6                           AZ452

The current formula isn't doing this.

Could you load me a small sample of your file?

Thomas

Hi,

OK Sheet 1 has the filtered column as A and the destination column as B. Then Sheet 2 has unfiltered, contiguous data that I already know matches the values in the filtered Sheet1 column A.

So where Sheet 1 Filtered Column A row 2 is Value 1, unfiltered Sheet 2 Column A row 2 is the right match. But Value 3 in row 4 should have corresponding value Matching Value 3 in Sheet 2 row 3.

So somehow achieve the equivalent of "paste into visible only".

Thanks - please see attached

Bill
sample-file.xlsx