Is the sheet visible at the time the code runs? You can only select on
visible sheets...
--
HTH...

Jim Thomlinson


"Anolan" wrote:

> Jim,
>
> Yes, I do have a sheet named "S&M." I checked the spelling on the tab and
> in the code, and it appears to be correct. Any suggestion? Thank you. Andy
>
> "Jim Thomlinson" wrote:
>
> > It is the first time you are trying to select sheet S&M. Do you have a sheet
> > S&M and is it spelled exactly correct with no extra spaces or such...
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Anolan" wrote:
> >
> > > Anyone...I need help debugging the following code, please? The macro stops
> > > at the point marked, "Stops Here!" My macro opens files, performs a vlookup
> > > and closes the files, one by one. I have two sheets: G&A and S&M. When I
> > > switch from sheet G&A to S&M, I get two run-time errors: '1004' Select
> > > method of range class failed, or '13' Type mismatch. I can't figure out
> > > what is wrong. Thanks, Andy.
> > >
> > > Here is my code...
> > >
> > > Private Sub CommandButton1_Click()
> > > 'Created by anolasco on 11/29/2005
> > > 'To update the 2005 Salary Variances Report for mmyy
> > > 'using information from the CC EV 100 Reports located in
> > > 'U:\CC_EV_100 Reports\mmyy\. This macro reside in this workbook.
> > >
> > > Dim myDir As String
> > > Dim myMonth As String
> > > Dim nResult As Long
> > > nResult = MsgBox(Prompt:="Do You Really Want To Run This Macro?",
> > > Buttons:=vbYesNo, Title:="Run Macro")
> > > If nResult = vbYes Then
> > > myDir = Application.InputBox(Prompt:="Enter File Location",
> > > Default:="U:\CC_EV_100 Reports\mmyy\", Type:=2)
> > > myMonth = Application.InputBox(Prompt:="Enter Month Name",
> > > Default:="October", Type:=2)
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("G&A").Range("D7").Select
> > > On Error Resume Next
> > > Workbooks.Open Filename:=myDir & "4164302000.xls"
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("G&A").Range("D7").Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,2,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,2,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,12,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,12,FALSE))"
> > > Calculate
> > > ActiveCell.Offset(1, -8).Select
> > > Workbooks("4164302000.xls").Activate
> > > ActiveWorkbook.Close SaveChanges:=False
> > > '
> > > Workbooks.Open Filename:=myDir & "4164302100.xls"
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("G&A").Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,2,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,2,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,12,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,12,FALSE))"
> > > Calculate
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Calculate
> > > ActiveCell.Offset(1, -8).Select
> > > Workbooks("4164302100.xls").Activate
> > > ActiveWorkbook.Close SaveChanges:=False
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("G&A").Range("D5").Select
> > > ActiveCell.FormulaR1C1 = myMonth
> > > Sheets("G&A").Range("H5").Select
> > > ActiveCell.FormulaR1C1 = myMonth & " YTD"
> > > Sheets("G&A").Range("D7:E23").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("G&A").Range("H7:I23").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("G&A").Range("L7:L23").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("G&A").Range("D29:E30").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("G&A").Range("H29:I30").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("G&A").Range("L29:L30").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("G&A").Range("L31").Select
> > > Calculate
> > > '
> > > '
> > > Workbooks.Open Filename:=myDir & "4164804000.xls"
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Stops Here!
> > >
> > > Sheets("S&M").Range("D5").Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,2,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,2,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,12,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,12,FALSE))"
> > > Calculate
> > > ActiveCell.Offset(1, -8).Select
> > > Workbooks("4164804000.xls").Activate
> > > ActiveWorkbook.Close SaveChanges:=False
> > > '
> > > Workbooks.Open Filename:=myDir & "4164805000.xls"
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("S&M").Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,2,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,2,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,12,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,12,FALSE))"
> > > Calculate
> > > ActiveCell.Offset(1, -8).Select
> > > Workbooks("4164805000.xls").Activate
> > > ActiveWorkbook.Close SaveChanges:=False
> > > '
> > > Workbooks.Open Filename:=myDir & "4164805050.xls"
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("S&M").Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,2,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,2,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,12,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,12,FALSE))"
> > > Calculate
> > > ActiveCell.Offset(1, -8).Select
> > > Workbooks("4164805050.xls").Activate
> > > ActiveWorkbook.Close SaveChanges:=False
> > > '
> > > Workbooks.Open Filename:=myDir & "4164805200.xls"
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("S&M").Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,2,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,2,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,3,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,3,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,7,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,7,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,8,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,8,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,12,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,12,FALSE))"
> > > Calculate
> > > ActiveCell.Offset(1, -8).Select
> > > Workbooks("4164805200.xls").Activate
> > > ActiveWorkbook.Close SaveChanges:=False
> > > '
> > > Workbooks.Open Filename:=myDir & "4164805300.xls"
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("S&M").Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,2,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,2,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,3,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,3,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,7,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,7,FALSE))"
> > > ActiveCell.Offset(0, 1).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,8,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,8,FALSE))"
> > > ActiveCell.Offset(0, 3).Select
> > > ActiveCell.FormulaR1C1 =
> > > "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,12,FALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,12,FALSE))"
> > > Calculate
> > > ActiveCell.Offset(1, -8).Select
> > > Workbooks("4164805300.xls").Activate
> > > ActiveWorkbook.Close SaveChanges:=False
> > > '
> > > Workbooks("2005 Salary Variances_Template.xls").Activate
> > > Sheets("S&M").Range("D3").Select
> > > ActiveCell.FormulaR1C1 = myMonth
> > > Sheets("S&M").Range("H3").Select
> > > ActiveCell.FormulaR1C1 = myMonth & " YTD"
> > > Sheets("S&M").Range("D5:E17").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("S&M").Range("H5:I17").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Sheets("S&M").Range("L5:L17").Select
> > > Selection.Copy
> > > Selection.PasteSpecial Paste:=xlValues
> > > Calculate
> > > End If
> > > Workbooks("2005 Salary Variances_Template.xls").Save
> > > Sheets("G&A").Range("D4").Select
> > > If nResult = vbYes Then
> > > MsgBox ("Macro Completed")
> > > Else
> > > MsgBox ("Macro Cancelled")
> > > End If
> > > End Sub
> > >