Help Debugging VB Code

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

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
>

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
> >

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
> > >

Hi Anolan
You might need to select the sheet first...
Have you tried

Sheets("S&M").Select
Range("D5").Select

?

HTH
Best rgds
Chris Lav

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> 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,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=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,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,2,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=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,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=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,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=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,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,2,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=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,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,2,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,3,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,7,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,8,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=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,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,2,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,3,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,7,FA
LSE))"
> > > > ActiveCell.Offset(0, 1).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,8,FA
LSE))"
> > > > ActiveCell.Offset(0, 3).Select
> > > > ActiveCell.FormulaR1C1 =
> > > >
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=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
> > > >

That will be it... I never use selects so they just don't regiser with me...
Nice catch Chris...
--
HTH...

Jim Thomlinson


"Chris Lavender" wrote:

> Hi Anolan
> You might need to select the sheet first...
> Have you tried
>
> Sheets("S&M").Select
> Range("D5").Select
>
> ?
>
> HTH
> Best rgds
> Chris Lav
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > 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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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 =
> > > > >

Thank you Chris! I will try it...Andy

"Chris Lavender" wrote:

> Hi Anolan
> You might need to select the sheet first...
> Have you tried
>
> Sheets("S&M").Select
> Range("D5").Select
>
> ?
>
> HTH
> Best rgds
> Chris Lav
>
> "Jim Thomlinson" <[email protected]> wrote in message
> news:[email protected]...
> > 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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,2,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 1).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
> LSE))"
> > > > > ActiveCell.Offset(0, 3).Select
> > > > > ActiveCell.FormulaR1C1 =
> > > > >
> "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,12,F
> ALSE))=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 =
> > > > >