how to use getVisibleView()

Question

Hi,

Please help me to copy filtered data from a worksheet to a new worksheet.

I have data in a worksheet. I use filter to get some specific type of data. After using the filter I want to copy those filtered rows (data) to another worksheet. I tried to use the "Automate" function to record my action as script, it does not work. Because when I run the script it copy and paste all data (unfiltered). Use of "Go special function" will not work for me, because it needs to be dynamic.

Here is an example when I used the "Go special function" -

  sheet3.getRange("B1:E78").setValues([["Category","Instrument","Organisation","Description"],["Self-........

So it defines the cells - and will not going to work.

I tried:

const sourceRange = source.getRange().getVisibleView();

  targetRange.copyFrom(sourceRange.getRange(), ExcelScript.RangeCopyType.all, false, false);

• did not work - error in the last line.

Please help

Kind regards

Manab

Answer 1

Thank you OssueMac for your quick response.

However, this does not work for "Office script" - my excel file is in sharepoint. So I need to use office script

Regards

Sorry! I am not able to help with that.

Answer 2

Thank you OssueMac for your quick response.

However, this does not work for "Office script" - my excel file is in sharepoint. So I need to use office script

Regards

Answer 3

Below is an example of code for copying the filtered visible data and pasting to another worksheet.

Explanation of code between the asterisk lines. (The asterisk lines are prefixed with a single quote, so they are comments only)

Firstly: When you copy and paste the code, this section will probably have blank lines between the typed lines. Remove the blank lines or the code will error because the space and underscore at the end of the lines are actually line breaks in an otherwise single line of code and the code cannot have blank lines between such lines after line breaks.

Actual code explanation:

With wsSource is the source data workheet containing the filtered data.

With .AutoFilter.Range is the entire AutoFiltered range. (Includes column headers plus visible and non-visible data)

.Offset(1, 0) moves the range down one row off the column headers but adds an additional blank row at bottom.

.Resize(.Rows.Count - 1, .Columns.Count) This resizes by deleting the additional line added by previous Offset command.

.SpecialCells(xlCellTypeVisible) is self-explanatory (Only includes visible data)

Sub CopyPastFiltData()

Dim wsSource As Worksheet 

Dim wsDestin As Worksheet 

Dim rngVisible As Range 

Dim rngDestin As Range 

Set wsSource = Worksheets("Sheet1")     'Edit "Sheet1" to your worksheet name 

Set wsDestin = Worksheets("Sheet2")     'Edit "Sheet2" to your worksheet name 

'\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 

With wsSource 

    With .AutoFilter.Range 

        Set rngVisible = .Offset(1, 0) \_ 

                    .Resize(.Rows.Count - 1, .Columns.Count) \_ 

                    .SpecialCells(xlCellTypeVisible) 

    End With 

End With 

'\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 

'Assign first cell of destination  to a range variable 

With wsDestin 

    Set rngDestin = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) 'Next blank row in destination sheet 

    If rngDestin.Row > 1 Then   'Not first row of worksheet with column headers 

        'Copy the visible data and Paste to the Destination worksheet 

        rngVisible.Copy Destination:=rngDestin 

    End If 

End With 

End Sub