PDA

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



itipu
06-20-2007, 12:37 AM
Hi,

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

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


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:

.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Copy
.Rows("2:" & .Rows.Count).SpecialCells(xlCellTypeVisible).EntireRow.Copy

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
06-20-2007, 12:45 AM
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
06-20-2007, 01:10 AM
Because as soon as I add sheet "Error" it becomes active...

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

Bob Phillips
06-20-2007, 01:27 AM
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
06-20-2007, 01:34 AM
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

itipu
06-20-2007, 02:21 AM
Created sheet "Error" & sheet "Sheet4"

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

:)

Thanks

Mike

itipu
06-20-2007, 02:24 AM
Thanks