Error 400 when running Macro

J

Jim@Tech

I have a workbook which contains 6 worksheets and each worksheet queries the
same Access databse to return a different set of data (e.g. sheet 1 shows
project risks, sheet 2 shows project issues etc).
After I've imported the data, I do a small about of reformatting (replacing
1 and 0 with "Yes" and "No" and entering a formula into a column), I then
remove the queries to the database and save a copy of the workbook to a
shared folder.

I've come up with a macro to automate the reformatting and removal of the
queries from each sheet which works fine, but in order to post a 'clean' copy
of the workbook (i.e. no macros, links, queries etc) I then need to open the
VBA editor and remove the module which contains the macro.
I thought I'd be really clever and take the macro out of Module 1 and assign
it to a button on a new sheet in the workbook, so that once I'd run the macro
I can just delete the worksheet that contains the button and presto! - a
clean workbook.
Problem is, when I try to run the macro from the button, I get an error 400.

Can anyone tell me what causes an error 400 and what I need to do to fix it?

For reference, here's my code:

Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable


'
Sheets("Risks").Select
Range("H3:H100").Select
Selection.FormulaR1C1 = _

"=IF(RC[-1]="""","""",IF(RC[-2]=""Remote"",1,IF(RC[-2]=""Infrequent"",3,IF(RC[-2]=""Likely"",5,IF(RC[-2]=""Very
Likely"",7,0))))*IF(RC[-1]=""Very
High"",4,IF(RC[-1]=""High"",3,IF(RC[-1]=""Medium"",2,IF(RC[-1]=""Low"",1,0)))))"
Columns("X:X").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Issues").Select
Columns("M:N").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Milestones").Select
Range("J:J,L:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Dependencies").Select
Columns("I:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Changes").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Summary").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Dim FileName As Variant
FileName = Application.GetSaveAsFilename(filefilter:="Microsoft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.
 
J

Joel

I think you are right. The code is working for me. Try these c hanges.
I think the problem has to do with the activeworksheet. The code below I
specified the worksheet.


Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable


'
With Sheets("Risks")
With .Range("H3:H100")
.FormulaR1C1 = _
"=IF(RC[-1]=""""," & _
""""",IF(RC[-2]=""Remote"",1," & _
"IF(RC[-2]=""Infrequent"",3," & _
"IF(RC[-2]=""Likely"",5," & _
"IF(RC[-2]=""Very Likely"",7,0))))" & _
"*IF(RC[-1]=""Very High"",4," & _
"IF(RC[-1]=""High"",3," & _
"IF(RC[-1]=""Medium"",2," & _
"IF(RC[-1]=""Low"",1,0)))))"
End With
With .Columns("X:X")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Issues")
With .Columns("M:N")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Milestones")
With .Range("J:J,L:L")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Dependencies")
With .Columns("I:L")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Changes")
For Each qt In .QueryTables
qt.Delete
Next qt

End With

With Sheets("Summary")
For Each qt In .QueryTables
qt.Delete
Next qt
End With

Dim FileName As Variant
FileName = Application.GetSaveAsFilename( _
filefilter:="Microsoft Office Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub


Jim@Tech said:
I have a workbook which contains 6 worksheets and each worksheet queries the
same Access databse to return a different set of data (e.g. sheet 1 shows
project risks, sheet 2 shows project issues etc).
After I've imported the data, I do a small about of reformatting (replacing
1 and 0 with "Yes" and "No" and entering a formula into a column), I then
remove the queries to the database and save a copy of the workbook to a
shared folder.

I've come up with a macro to automate the reformatting and removal of the
queries from each sheet which works fine, but in order to post a 'clean' copy
of the workbook (i.e. no macros, links, queries etc) I then need to open the
VBA editor and remove the module which contains the macro.
I thought I'd be really clever and take the macro out of Module 1 and assign
it to a button on a new sheet in the workbook, so that once I'd run the macro
I can just delete the worksheet that contains the button and presto! - a
clean workbook.
Problem is, when I try to run the macro from the button, I get an error 400.

Can anyone tell me what causes an error 400 and what I need to do to fix it?

For reference, here's my code:

Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable


'
Sheets("Risks").Select
Range("H3:H100").Select
Selection.FormulaR1C1 = _

"=IF(RC[-1]="""","""",IF(RC[-2]=""Remote"",1,IF(RC[-2]=""Infrequent"",3,IF(RC[-2]=""Likely"",5,IF(RC[-2]=""Very
Likely"",7,0))))*IF(RC[-1]=""Very
High"",4,IF(RC[-1]=""High"",3,IF(RC[-1]=""Medium"",2,IF(RC[-1]=""Low"",1,0)))))"
Columns("X:X").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Issues").Select
Columns("M:N").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Milestones").Select
Range("J:J,L:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Dependencies").Select
Columns("I:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Changes").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Summary").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Dim FileName As Variant
FileName = Application.GetSaveAsFilename(filefilter:="Microsoft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.
Click to expand...
 
J

Jim@Tech

Thanks Joel this is great, your code works a treat!

I'm still none the wiser about the root cause of the 'code 400' error though
and I'd like to know more about the difference between the 'With' argument
you used and the 'sheets("name").select' method the macro records... Any
suggestions for a good source...? Perhaps I should try the VBA Help files...
;)

Joel said:
I think you are right. The code is working for me. Try these c hanges.
I think the problem has to do with the activeworksheet. The code below I
specified the worksheet.


Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable


'
With Sheets("Risks")
With .Range("H3:H100")
.FormulaR1C1 = _
"=IF(RC[-1]=""""," & _
""""",IF(RC[-2]=""Remote"",1," & _
"IF(RC[-2]=""Infrequent"",3," & _
"IF(RC[-2]=""Likely"",5," & _
"IF(RC[-2]=""Very Likely"",7,0))))" & _
"*IF(RC[-1]=""Very High"",4," & _
"IF(RC[-1]=""High"",3," & _
"IF(RC[-1]=""Medium"",2," & _
"IF(RC[-1]=""Low"",1,0)))))"
End With
With .Columns("X:X")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Issues")
With .Columns("M:N")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Milestones")
With .Range("J:J,L:L")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Dependencies")
With .Columns("I:L")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Changes")
For Each qt In .QueryTables
qt.Delete
Next qt

End With

With Sheets("Summary")
For Each qt In .QueryTables
qt.Delete
Next qt
End With

Dim FileName As Variant
FileName = Application.GetSaveAsFilename( _
filefilter:="Microsoft Office Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub


Jim@Tech said:
I have a workbook which contains 6 worksheets and each worksheet queries the
same Access databse to return a different set of data (e.g. sheet 1 shows
project risks, sheet 2 shows project issues etc).
After I've imported the data, I do a small about of reformatting (replacing
1 and 0 with "Yes" and "No" and entering a formula into a column), I then
remove the queries to the database and save a copy of the workbook to a
shared folder.

I've come up with a macro to automate the reformatting and removal of the
queries from each sheet which works fine, but in order to post a 'clean' copy
of the workbook (i.e. no macros, links, queries etc) I then need to open the
VBA editor and remove the module which contains the macro.
I thought I'd be really clever and take the macro out of Module 1 and assign
it to a button on a new sheet in the workbook, so that once I'd run the macro
I can just delete the worksheet that contains the button and presto! - a
clean workbook.
Problem is, when I try to run the macro from the button, I get an error 400.

Can anyone tell me what causes an error 400 and what I need to do to fix it?

For reference, here's my code:

Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable


'
Sheets("Risks").Select
Range("H3:H100").Select
Selection.FormulaR1C1 = _

"=IF(RC[-1]="""","""",IF(RC[-2]=""Remote"",1,IF(RC[-2]=""Infrequent"",3,IF(RC[-2]=""Likely"",5,IF(RC[-2]=""Very
Likely"",7,0))))*IF(RC[-1]=""Very
High"",4,IF(RC[-1]=""High"",3,IF(RC[-1]=""Medium"",2,IF(RC[-1]=""Low"",1,0)))))"
Columns("X:X").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Issues").Select
Columns("M:N").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Milestones").Select
Range("J:J,L:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Dependencies").Select
Columns("I:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Changes").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Summary").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Dim FileName As Variant
FileName = Application.GetSaveAsFilename(filefilter:="Microsoft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.
Click to expand...
Click to expand...
 
J

Joel

I avoid using the selection method which is what you get from a recorded
macro. I always convert to sheet references to aviod problems. I'm not
suure what was causing you r eeor. I ran you code unmodified and it seemed
to work.

Jim@Tech said:
Thanks Joel this is great, your code works a treat!

I'm still none the wiser about the root cause of the 'code 400' error though
and I'd like to know more about the difference between the 'With' argument
you used and the 'sheets("name").select' method the macro records... Any
suggestions for a good source...? Perhaps I should try the VBA Help files...
;)

Joel said:
I think you are right. The code is working for me. Try these c hanges.
I think the problem has to do with the activeworksheet. The code below I
specified the worksheet.


Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable


'
With Sheets("Risks")
With .Range("H3:H100")
.FormulaR1C1 = _
"=IF(RC[-1]=""""," & _
""""",IF(RC[-2]=""Remote"",1," & _
"IF(RC[-2]=""Infrequent"",3," & _
"IF(RC[-2]=""Likely"",5," & _
"IF(RC[-2]=""Very Likely"",7,0))))" & _
"*IF(RC[-1]=""Very High"",4," & _
"IF(RC[-1]=""High"",3," & _
"IF(RC[-1]=""Medium"",2," & _
"IF(RC[-1]=""Low"",1,0)))))"
End With
With .Columns("X:X")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Issues")
With .Columns("M:N")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Milestones")
With .Range("J:J,L:L")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Dependencies")
With .Columns("I:L")
.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

For Each qt In .QueryTables
qt.Delete
Next qt
End With

With Sheets("Changes")
For Each qt In .QueryTables
qt.Delete
Next qt

End With

With Sheets("Summary")
For Each qt In .QueryTables
qt.Delete
Next qt
End With

Dim FileName As Variant
FileName = Application.GetSaveAsFilename( _
filefilter:="Microsoft Office Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub


Jim@Tech said:
I have a workbook which contains 6 worksheets and each worksheet queries the
same Access databse to return a different set of data (e.g. sheet 1 shows
project risks, sheet 2 shows project issues etc).
After I've imported the data, I do a small about of reformatting (replacing
1 and 0 with "Yes" and "No" and entering a formula into a column), I then
remove the queries to the database and save a copy of the workbook to a
shared folder.

I've come up with a macro to automate the reformatting and removal of the
queries from each sheet which works fine, but in order to post a 'clean' copy
of the workbook (i.e. no macros, links, queries etc) I then need to open the
VBA editor and remove the module which contains the macro.
I thought I'd be really clever and take the macro out of Module 1 and assign
it to a button on a new sheet in the workbook, so that once I'd run the macro
I can just delete the worksheet that contains the button and presto! - a
clean workbook.
Problem is, when I try to run the macro from the button, I get an error 400.

Can anyone tell me what causes an error 400 and what I need to do to fix it?

For reference, here's my code:

Sub reformat()
'
' Macro2 Macro
' Macro recorded 17/10/2008 by Jim Russell
'
Dim qt As QueryTable


'
Sheets("Risks").Select
Range("H3:H100").Select
Selection.FormulaR1C1 = _

"=IF(RC[-1]="""","""",IF(RC[-2]=""Remote"",1,IF(RC[-2]=""Infrequent"",3,IF(RC[-2]=""Likely"",5,IF(RC[-2]=""Very
Likely"",7,0))))*IF(RC[-1]=""Very
High"",4,IF(RC[-1]=""High"",3,IF(RC[-1]=""Medium"",2,IF(RC[-1]=""Low"",1,0)))))"
Columns("X:X").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Issues").Select
Columns("M:N").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Milestones").Select
Range("J:J,L:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Dependencies").Select
Columns("I:L").Select
Selection.Replace What:="1", Replacement:="Yes", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="0", Replacement:="No", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Changes").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Sheets("Summary").Select
For Each qt In ActiveSheet.QueryTables
qt.Delete
Next qt
Range("A1").Select

Dim FileName As Variant
FileName = Application.GetSaveAsFilename(filefilter:="Microsoft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.
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

Find and Replace is going wrong in some cases 20
Problem with Macro 7
to read text tablimited file 4
Macro 2
Macro not working properly when run automatically 9
Replaceformat problem 2
Replace several words to One 2
Macro that isn't working 2

Top