What a rooky move. Thanks for ponting that out. I have corrected the code and
it runs great. Yes, I still a novis with VB, so some times my code is long
and messy. Thanks for the pointers.
How would I go about renaming the active worksheet? When the code runs, it
renames the tab.

Thanks

"Per Jessen" wrote:

Hi

First, always test on a copy of your workbook.

Are you sure that cell A1 in sheet INPUT_A holds any data?

At the start of your macro you delete Column A of activesheet. I would
select the desired sheet by code, otherwise the wrong sheet may be active
when the macro is started...

Your macro is very complex and this very difficult to read.

Below is part of your code which reworked a bit.

Columns("A:A").Delete Shift:=xlToLeft
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

Sheets("S- Overnight Rates ").Select
Range("X4", Range("X4").End(xlDown)).Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast

Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").End(xlDown).Offset(1, 0).Select

Sheets("S - 2 Day Rates").Select
Range("X4").Select
Range("X4", Range("X4").End(xlDown)).Copy
Application.CutCopyMode = False
ActiveWindow.ScrollWorkbookTabs Position:=xlLast

Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select

Regards,
Per

"pgarcia" skrev i meddelelsen
...
Sub Copy_Paste()
'
' Copy_Paste Macro
' Macro recorded 4/8/2008 by autpbg1
'

'
Application.ScreenUpdating = False

Dim MyPath As String
Dim MyFileName As String

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("S- Overnight Rates ").Select
Range("X4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("X4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("4900_B").Select
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Call Loop_Example
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("Y4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("Y4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("4900_B").Select
Range("G2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Call Loop_Example
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("Z4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("Z4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("4900_B").Select
Range("K2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Call Loop_Example
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AA4:AA5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AA4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("1900_D").Select
Range("c2:c101").Select
'Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AB4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AB4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AC4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AC4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S- Overnight Rates ").Select
Range("AD4:AD5").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Cells((Selection.Count + 1), 1).Select
Sheets("S - 2 Day Rates").Select
Range("AD4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Text_Out_Put_File").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
Range("A1").Select

MyPath = "S:\SUPPORT\CADTAR\CMS\!Exported_Text_Files!\"
MyFileName = "d" & Sheets("INPUT_A").Range("A1").Value & "MR"