I'm am having an issue when trying to insert a range from an autofilter on
one sheet to another. The range has to be inserted above rows that have data
in them. When I use the insert.shift:xldown, it only moves down the first
column.

TIA,

Sub B_CreateTabs()

Dim rngE As Range
Dim lngLastRow As Long
Dim mgrval, lobval, shtval As String


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


lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

' ** start of problem code
Rows("1:" & lngLastRow).Select
Selection.Copy
Sheets(shtval).Select
Selection.Insert Shift:=xlDown
' ** end of problem code

Range("A6").Select
Sheets("Reports").Select
Range("A2").Select
ActiveSheet.AutoFilterMode = False

End Sub




"Chris" wrote:

I'm am having an issue when trying to insert a range from an autofilter on
one sheet to another. The range has to be inserted above rows that have data
in them. When I use the insert.shift:xldown, it only moves down the first
column.

TIA,

Here is what I usually do

1) Get last row of data on orignal sheet before filtering

LastRow = .range("A" & rows.count).end(xlup).row

2) Apply filters

3) Copy visibile rows using specialcellst method

Set filterData = rows("1:" &
LastRow).SpecialCells(type:=xlCellTypeVisible)

4) Copy Range

FilerData.Copy

5) No insert the rows on 2nd sheet

Sheets("sheet2").Rows(1).Insert


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198948

http://www.thecodecage.com/forumz

Hi Chris,

Modified to include the column headers. Does not subtract 1 from the
numbRows and removed the offset and resize from the range to copy.

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("MyWorkbook.xls").Activate
Sheets(shtval).Select
Sheets(shtval).Copy _
After:=Workbooks("MyWorkbook.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.
'Includes column headers
numbRows = Sheets("Reports") _
.AutoFilter.Range.Columns(1) _
.SpecialCells(xlCellTypeVisible) _
.Cells.Count

'Insert the number of required rows starting row 2
'Includes row for column header.
Sheets(shtval).Rows(2 & ":" & 2 + numbRows - 1) _
.Insert Shift:=xlDown

'Copy the visible data including column headers
With Sheets("Reports").AutoFilter.Range
.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


--
Regards,

OssieMac

Thank you very much. It's working.

Chris



"OssieMac" wrote:

Hi Chris,

Modified to include the column headers. Does not subtract 1 from the
numbRows and removed the offset and resize from the range to copy.

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("MyWorkbook.xls").Activate
Sheets(shtval).Select
Sheets(shtval).Copy _
After:=Workbooks("MyWorkbook.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.
'Includes column headers
numbRows = Sheets("Reports") _
.AutoFilter.Range.Columns(1) _
.SpecialCells(xlCellTypeVisible) _
.Cells.Count

'Insert the number of required rows starting row 2
'Includes row for column header.
Sheets(shtval).Rows(2 & ":" & 2 + numbRows - 1) _
.Insert Shift:=xlDown

'Copy the visible data including column headers
With Sheets("Reports").AutoFilter.Range
.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


--
Regards,

OssieMac