Hi Chris,
Do I understand correctly that you want to copy the visible data and insert
it at a specific row in the destination sheet and that you only want to copy
and insert the data and NOT include the column headers? If correct then try
following code.
See below the code example for more explanation of some of the code.
Code example is all code between the asterisk lines.
'***************************************
Sub B_CreateTabs()
Dim rngE As Range
Dim lngLastRow As Long
Dim mgrval, lobval, shtval As String
Dim numbRows As Long
mgrval = "myself"
lobval = "dept"
shtval = mgrval & "-" & lobval
Windows("Mybook.xls").Activate
Sheets(shtval).Select
Sheets(shtval).Copy _
After:=Workbooks("Mybook.xls").Sheets(1)
Sheets("Reports").Select
ActiveSheet.AutoFilterMode = False
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Apply the filter
ActiveSheet.Range("A1:G" & lngLastRow) _
.AutoFilter Field:=3, Criteria1:=lobval
ActiveSheet.Range("A1:G" & lngLastRow) _
.AutoFilter Field:=4, Criteria1:=mgrval
'Count number of visible cells in one column.
numbRows = Sheets("Reports") _
.AutoFilter.Range.Columns(1) _
.SpecialCells(xlCellTypeVisible) _
.Cells.Count - 1 'Subtract 1 for column header
'Insert the number of required rows starting row 2
'Must be done before Copy or will loose copy.
Sheets(shtval).Rows(2 & ":" & 2 + numbRows - 1) _
.Insert Shift:=xlDown
'Copy the visible data excluding column headers
With Sheets("Reports").AutoFilter.Range
.Offset(1, 0) _
.Resize(.Rows.Count - 1, _
.Columns.Count) _
.EntireRow _
.SpecialCells(xlCellTypeVisible) _
.Copy
End With
'Paste the data starting row 2
Sheets(shtval).Rows(2).PasteSpecial
Sheets("Reports").Select
Range("A2").Select
ActiveSheet.AutoFilterMode = False
End Sub
'**********************************
More code explanation:
Rows cannot be counted in filtered ranges because count stops at first non
contiguous row. However, cells can be counted so count the visible cells in
one column to get the number of rows of data displayed. The header is
included so subtract 1 for the headers.
numbRows = Sheets("Reports") _
.AutoFilter.Range.Columns(1) _
.SpecialCells(xlCellTypeVisible) _
.Cells.Count - 1
'Copy the visible data excluding column headers
With Sheets("Reports").AutoFilter.Range
.Offset(1, 0) _
.Resize(.Rows.Count - 1, .Columns.Count) _
.EntireRow _
.SpecialCells(xlCellTypeVisible) _
.Copy
With Sheets("Reports").AutoFilter.Range - is the entire filtered rnage
including both the visible and non-visible rows.
..Offset(1, 0) - shift entire range down one row off the column headers but
that then includes an extra row at the bottom.
..Resize(.Rows.Count - 1, .Columns.Count) - Reduces range by one row to
remove row from bottom and width is number of columns in autofilter range.
..EntireRow - expand across page to include entire rows.
..SpecialCells(xlCellTypeVisible) - only include the visible cells.
..Copy - Self expanatory.
