Since you're the guru and I'm the novice, I'll just do what you say, and take your word for it. Paste Like I mentioned, the macro runs on its own, it just has problems when I try and attach it to a command button. -Helene Hey Si - What do you mean the sheet doesn't have a focus? Although, it works better all on one line as I have about 20 different numbers to copy from one worksheet to another so doing one copy then a paste then another copy/paste etc seems to be just the ticket.So I have created a button in that excel file and am now trying to somehow put some code together so when I click on that button a new file is created and the columns A, C and D with the contents are copied to the new created excel file. Paste Special Paste:=xl Paste All, Operation:=xl None, Skip Blanks:=False, Transpose:=False End With wbk. Close as you can see I am closing the new file at the end. I have a lot of files with slightly different order and column names. I upload two files - this is a simplified version of what I have. and i will upload the files with the macro for your testing.As I recently discovered Power Shell I decided to give it a fair chance. This is the script I came up with and to be fair it works like a charm.
Const Filter String As String = "No" Dim lng Last Row As Long Dim rng To Check As Range Dim xval As Variant Dim lcol As Long Dim xl Calc As Xl Calculation Call Freeze Screen worksheets("sheet1"). Unprotect 'If Named Range Exists("Pick List") Then With Sheet1. Auto Filter Field:=2, Criteria1:=Filter String On Error Resume Next . Protect Call Un Freeze Screen End Sub sorry to come back on this again but I'm back to getting the 1004 error with the entrirerow.delete statement. I run into similar problems with filtering, and can't always figure out the rhyme or reason for it!
So as far as Excel was concerned, I suspect it wasn't an error at all, it just wasn't using the range you were expecting.
Paste Special Paste:=xl Paste All, Operation:=xl None, Skip Blanks:= _ False, Transpose:=False End With Yes, your are right, and when I put them all on the same line without the With/End With I had to add the . Just a note; you don't need to qualify the Range object; Excel automatically assumes that you mean the range on the worksheet that you are in unless you tell it differently.
(I'm an analyst, not a programmer.) And, I'm the so called Excel whiz, so I guess it's time to learn to play with the big guys. (This is only part of it, when printed its 4 1/2 pages.) The runtime error occurs immediately. Private Sub Command Button1_Click() ' Sheets("2-04 Up").
I'll let you know how it goes, and thanks again for your help.