Copy Rows from Filtered Data and Insert into Existing Data

Question

I am trying to copy rows of data (which may or may not be filtered) and INSERT it into rows above existing data (sort of a rolling schedule). Below is my code that works for unfiltered data. If I apply any filters to the data to be copied, my macro will only copy 1 cell. Can anyone provide an example of a macro that can copy both filtered and unfiltered data?

Sub DynamicRange()
'Best used when first column has value on last row and first row has a value in the last column

Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range
Dim SelectedRange As Range

Set sht = ActiveWorkbook.ActiveSheet
Set StartCell = Range("C9")

If IsEmpty(StartCell.Value) = True Then
  MsgBox "Enter Dates to export"
  Exit Sub
End If

'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

'Select Range and Copy
  Set SelectedRange = sht.Range(StartCell, sht.Cells(LastRow, LastColumn))
  SelectedRange.Select
  Selection.SpecialCells(xlCellTypeVisible).Select
  Selection.Copy

'Select sheet "TRACKER" insert values above previous data
Sheets("TRACKER").Select
Range("B9").Select
Selection.Insert Shift:=xlDown

'clear selection
Application.CutCopyMode = False

End Sub

score 1 · Answer 1 · edited May 23 '17 at 12:31

I've rewritten your sub procedure and tried to avoid the use of .Select and Selection. Relying on properties like the ActiveCell¹ and ActiveSheet¹ is haphazard at best.

Sub DynamicRange()
    Dim sc As Range, sht As Worksheet

    Set sht = ActiveWorkbook.Worksheets("Sheet1")  '<~~ set this worksheet reference properly
    'btw, if you really needed ActiveWorkbook here then you would need it with Worksheets("TRACKER") below.

    With sht
        Set sc = .Range("C9")   'don't really have a use for this
        If IsEmpty(.Range("C9")) Then
          MsgBox "Enter Dates to export"
          Exit Sub
        End If
        With .Range(.Cells(9, 3), .Cells(9, Columns.Count).End(xlToLeft))
            With Range(.Cells(1, 1), .Cells(Rows.Count, .Columns.Count).End(xlUp))
                'got the range; determine non-destructively if anything is there
                If CBool(Application.Subtotal(103, .Cells)) Then
                    'there are visible values in the cells
                    .Cells.Copy _
                      Destination:=Worksheets("TRACKER").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
                End If
            End With
        End With
    End With

End Sub

The worksheet's SUBTOTAL function does not count hidden values so it is a good non-destructive test for the existence of visible values. You do not need to copy the Range.SpecialCells with the xlCellTypeVisible property specifically. A regular Range.Copy method will only copy visible cells. By immediately specifying the destination, there is no need to transfer the ActiveSheet property to the TRACKER worksheet; only the top-left corner of the destination need be specified.

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Thank you so much for your advice, I'll definitely read that article on avoiding the use of SELECT. However, this does not solve my dilemma - I need to copy filtered data and INSERT it above existing data. This macro copies the headers (row 8)? and pastes them below existing data row 41&42) — cfrz, Jan 12 '16 at 22:56

Copy Rows from Filtered Data and Insert into Existing Data

1 Answers1