Copy & Paste from one Worksheet to another without giving focus?

[Lars Uffmann]

My next problem:

I currently have this code to "delete" unwanted lines:
' **************************
Dim wb As Workbook ' wb for workBook
Dim ds As Worksheet ' ds for dataSheet

Set wb = Workbooks("myWorkbook")
Set ds = wb.Worksheets("myWorksheet")

ds.Columns.AutoFilter 4, "<>0" ' only show data where column 4 is <> 0
' (I want to "delete" where column 4 = 0)

ds.Rows.Copy ' copy into clipboard
' I'd prefer to give the new worksheet as destination
' and not use clipboard at all

ds.Name = "dummy" ' give worksheet a temporary name

' create new worksheet under old name
wb.Worksheets.Add().Name = "myWorksheet"

' select position for pasting
Worksheets("myWorksheet").Range("A1").Select

Selection.PasteSpecial ' paste data & formats

Application.DisplayAlerts = False
Worksheets("dummy").Delete ' delete old worksheet
Application.DisplayAlerts = True
' **************************

Now I would like to change the ds.Rows.Copy and Selection.PasteSpecial
to some code that also works in the background, if for example right
before Selection.PasteSpecial, someone clicks on a different worksheet -
I want to avoid pasting to that then. Any ideas?

Best Regards,

Lars

 

[Lars Uffmann]

Found it:

' *******
Dim wb As Workbook
Dim oldWs As Worksheet, newWs As Worksheet

Set wb = Workbooks(filename & ".txt")
Set oldWs = wb.Worksheets("myWorksheet")

oldWs.Columns.AutoFilter 4, "<>0" ' only display where colum 4 <> 0
oldWs.Name = "dummy" ' rename old worksheet to temporary name

wb.Worksheets.Add().Name = "myWorksheet" ' create new worksheet
Set newWs = Worksheets("myWorksheet")
oldWs.Cells.Copy newWs.Cells ' keyline here, copy stuff over
newWs.Cells(1, 1).Select ' unselect everything

Application.DisplayAlerts = False
Worksheets("dummy").Delete ' delete old Worksheet
Application.DisplayAlerts = True

Set oldWs = Nothing
Set newWs = Nothing
' ********