Solved: Copying a range from one sheet to a new sheet in the background

[itipu]

Hi,

Currently I am filtering for some errors in my sheet using the following code:

[vba]Application.ScreenUpdating = False
With Cells
.Columns("A:L").AutoFilter Field:=1, Criteria1:="0"
.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Copy
.Columns("A:L").AutoFilter Field:=1, Criteria1:="<>"
End With
Application.ScreenUpdating = True
[/vba]

So currently I just delete all the rows selected.

I would like to however add a new Sheet called "Errors" and copy the data prior deletion into this new sheet:

[vba] .Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Copy
.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Copy[/vba]

The problem is I don't seem to figure out how to do this, without making "Error" sheet an Active one... Basically I want this copying to happen in the background as I need to have my current sheet Active for other purposes...

Thanks a lot

Mike

[Bob Phillips]

expression.Copy(Destination)

expression Required. An expression that returns a Range object.

Destination Optional Variant. Specifies the new range to which the specified range will be copied. If this argument is omitted, Microsoft Excel copies the range to the Clipboard.

[itipu]

Because as soon as I add sheet "Error" it becomes active...

[vba]Application.ScreenUpdating = False
With Cells
.Columns("A:L").AutoFilter Field:=1, Criteria1:="0"
Set xlSheet = ActiveWorkbook.Sheets.Add
xlSheet.Name = "Error"
.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Worksheets("Error")
.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Columns("A:L").AutoFilter Field:=1, Criteria1:="<>"
End With
Application.ScreenUpdating = True[/vba]

[Bob Phillips]

Stop using activesheet.

Set a sheet variable to the activesheet at the start and then refer to the sheet via that variable, just as you are doing wiith xlSheet for the new sheet.

[Charlize]

[VBA]Sub Copy_Error()
Dim xlSheet As Worksheet
Dim Current As Worksheet
Dim dest_pos As Range
Dim xlSheet_row As Long
'Activesheet because I don't know the name
'of the sheet that you are checking for errors
Set Current = ActiveWorkbook.ActiveSheet
Set xlSheet = ActiveWorkbook.Sheets.Add
xlSheet.Name = "Error"
'Probably always one because I suppose you will
'delete the errorsheet afterwards based on the fact
'that you create this sheet by coding.
xlSheet_row = xlSheet.Range("A" & Rows.Count).End(xlUp).Row
Set dest_pos = xlSheet.Range("A" & xlSheet_row)
Current.Activate
With Cells
.Columns("A:L").AutoFilter Field:=1, Criteria1:="0"
.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.COPY dest_pos
.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Columns("A:L").AutoFilter Field:=1, Criteria1:="<>"
End With
End Sub[/VBA]

[itipu]

Created sheet "Error" & sheet "Sheet4"

Left "Error" blank and copied correct stuff to "Sheet4"



Thanks

Mike

[itipu]

Thanks