Error 400 when running Macro
 

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:="Microso ft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.

Error 400 when running Macro
 

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" wrote:

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:="Microso ft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.

Error 400 when running Macro
 

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" wrote:

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" wrote:

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:="Microso ft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.

Error 400 when running Macro
 

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" wrote:

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" wrote:

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" wrote:

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:="Microso ft Office
Excel File (*.xls),*.xls")
If FileName = False Then Exit Sub
ActiveWorkbook.SaveAs FileName

End Sub

Thanks,
Jim.