HELP Altering Copy sheet Code PLEASE

[Mister H]

Hi All:

Hopefully I can explain this and I am showing you enough code to get an answer

I am trying to alter this code in a couple of ways:

'Rename Tab
With ActiveSheet
    .Name = "IFIS Receipt Register"
End With
Range("A1").Select
 
'Copy IFIS Receipt Register Errors sheet to new file
Windows("IMPORT IFIS Receipt Register (verJuly 11th 2011).xls").Activate
Sheets("IFIS Receipt Register Errors").Copy after:=Sheets(Sheets.Count)
 
 
'Save file in Excel Format
    fn = Replace(fn, ".txt", ".xls")

1) Is there a way to alter this code to make it more generic? I would like to go back to the workbook titled "IMPORT IFIS Receipt Register" regardless of the version date. I don't want to have to change the code if I alter the filename with a new version date. If it matters the workbook is already open when the code is running:

Windows("IMPORT IFIS Receipt Register [B][COLOR=red](verJuly 11th 2011).xls[/COLOR][/B]").Activate

2) This portion of code is not working for me. Basically I am trying to copy the sheet from one file "IMPORT IFIS Receipt Register" and paste it at the end of the Workbook that WAS active (file name is identified in the code by fn (above):

'Copy IFIS Receipt Register Errors sheet to new file
Windows("IMPORT IFIS Receipt Register (verJuly 11th 2011).xls").Activate
Sheets("IFIS Receipt Register Errors").[B]Copy after:=Sheets(Sheets.Count)[/B]

I HOPE this makes sense but I doubt it because I was getting a little confused just typing it...

I may have to repost later and break it down a little more.

THANKS to anyone that can assist.

Take Care,
Mark

 

[Norie]

Mark

I thought you wanted to copy data from the file you were opening(wbOpen) to the workbook the code is in.

Also, what 'new' workbook?

 

[Mister H]

Norie:

My APOLOGIES for my confusing Posts...

I need to copy a sheet from the original workbook (wbThis) to the new workbook (wbOpen).

The code I showed is working except for the line in Red. I can't get the code to paste the sheet into the wbOpen workbook.

I think I may have to look into your other suggestion regarding GetExternalData

Again, THANKS for all your input and I am truly sorry for not being able to clearly state what I am trying to accomplish

Bye 4 now,
Mark

 

[Norie]

Mark

Can I just clarify?

You want to copy data from the workbook the code is in to the 'new' workbook which is the text file you opened saved as a workbook?

 

[Mister H]

That is correct Norie

THANKS

 

[Norie]

Mark

No idea if this will work, partly because quite a lot of it's based on guesses.

Option Explicit

Sub Format_Text_to_Excel()
Dim wbOpen As Workbook
Dim wbThis As Workbook
Dim vrtSelectedItem As Variant
Dim fd As FileDialog
Dim fn As String
Dim wsRegRec As Worksheet
Dim wsRegErr As Worksheet
 
    Set wbThis = ThisWorkbook
 
    Set fd = Application.FileDialog(msoFileDialogOpen)
 
    fd.Title = "Please open the  IFIS RECEIPT REGISTER  text file in order to convert it into Excel."
 
    'Added this to make it open at the folder that this file is stored in
    fd.InitialFileName = ThisWorkbook.Path & "\"

    With fd
        .Filters.Clear
        .Filters.Add "All files", "*.txt"
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                fn = vrtSelectedItem
            Next vrtSelectedItem
        End If
    End With
 
    Set fd = Nothing
 
    If Len(fn) < 3 Then
        Exit Sub
    End If
 
    Workbooks.OpenText Filename:=fn, Origin:=xlWindows, _
                       StartRow:=9, DataType:=xlFixedWidth, FieldInfo:= _
                       Array(Array(0, 1), Array(22, 1), Array(57, 1), Array(77, 1), Array(92, 1), Array(106, 1), _
                             Array(121, 1), Array(136, 1)), TrailingMinusNumbers:=True
 
    Set wbOpen = ActiveWorkbook

    'Rename Tab IN NEWLY OPENED txt file
    Set wsRegRec = wbOpen.Worksheets(1)
 
    wsRegRec.Name = "IFIS Receipt Register"
 
    Set wsRegErr = wbThis.Sheets("IFIS Receipt Register Errors")
 
    ' this copys the worksheet 'IFIS Receipt Register Errors") to the file opened, after the 'IFIS Receipt Register' sheet
 
    wsRegErr.Copy After:=wsRegRec
 
    'Save file in Excel Format
    fn = Replace(fn, ".txt", ".xls")
 
    Application.ScreenUpdating = True
 
    wbOpen.SaveAs Filename:=fn, FileFormat:=xlNormal
 
    MsgBox "The text file has been formated into excel.  The excel file was saved as : " & Chr(13) & fn, vbInformation
End Sub

 

[Mister H]

Norie:

THANKS for everything.

Not only did you stick with me while I babbled on trying to explain what I was trying to accomplish BUT you ALSO RESOLVED the issue.

Your code is doing exactly what I want

THANK You VERY much Norie

Have an AWESOME day,
Mark