Help Debugging VB Code

G

Guest

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
 
G

Guest

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 said:
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
Click to expand...
 
G

Guest

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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

Jim Thomlinson


Anolan said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
C

Chris Lavender

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 said:
Is the sheet visible at the time the code runs? You can only select on
visible sheets...
--
HTH...

Jim Thomlinson


Anolan said:
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
Click to expand...
"=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302000.xls]HRFileRetrieve!C1:C12,12,
FALSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,2,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,2,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,3,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,7,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,8,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164302100.xls]HRFileRetrieve!C1:C12,12,
FALSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164804000.xls]HRFileRetrieve!C1:C12,12,
FALSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,2,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,3,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,7,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,8,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805000.xls]HRFileRetrieve!C1:C12,12,
FALSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,2,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,2,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,3,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,7,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,8,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805050.xls]HRFileRetrieve!C1:C12,12,
FALSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,2,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,2,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,3,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,7,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,8,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805200.xls]HRFileRetrieve!C1:C12,12,
FALSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,2,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,2,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,3,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,3,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,7,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,7,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,8,FA
LSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,8,FA
LSE))" "=IF(ISERROR(VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,12,F
ALSE))=TRUE,0,VLOOKUP(""Salaries"",[4164805300.xls]HRFileRetrieve!C1:C12,12,
FALSE))"
Click to expand...
 
G

Guest

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

Jim Thomlinson


Chris Lavender said:
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 said:
Is the sheet visible at the time the code runs? You can only select on
visible sheets...
--
HTH...

Jim Thomlinson


Anolan said:
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

:

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


:

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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Chris Lavender said:
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 said:
Is the sheet visible at the time the code runs? You can only select on
visible sheets...
--
HTH...

Jim Thomlinson


Anolan said:
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

:

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


:

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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
"=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 =
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Advanced Filter works in recorder but not while running macro 5
Macro is not able to identify the value. 3
VLOOKUP and then some 2
Please help correct this macro! :) 2
VB Problem - Help! 3
Help Fixing a Macro 2
input numbers for code 3
Help!! (vb macro in excel) 4

Top