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

Is the workbook you want to go back to the workbook with the code in it?

If it is you can refer to it as ThisWorkbook in the code, so you don't need the name.

If it's not, are you opening the workbook at some point in the code?

If you are you could create a reference to it when you open it and use that in the rest of the code.

 

[Mister H]

THANKS Norie. SORRY I should have mentioned that

The code is in the Workbook titled IMPORT IFIS Receipt Register (verJuly 11th 2011).xls, the sheet I want to copy "IFIS Receipt Register Errors" is also in this workbook.

The code in the IMPORT IFIS Receipt Register (verJuly 11th 2011) workbook allows the user to
- Open a txt file
- it then imports it converts it
- multiple formatting is added
- useless data is deleted
- and then I need the sheet IFIS Receipt Register Errors copied to it which is in the workbook IMPORT IFIS Receipt Register (verJuly 11th 2011)
- the file is then saved in Excel format.

I have everything working except for copying the worksheet from one book to the other.

Hopefully this clarifies things a little

EDIT: I THINK this is the code that is opening the new workbook:

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


THANKS Again,
Mark

 

[Norie]

Mark

I'm confused.

What's the name of the workbook the code is in and what's the name of the text file you are opening?

Also, what's fn?

 

[Mister H]

THANKS for sticking with me Norie My apologies for the confusion.

ALL my code is stored in the workbook titled:
IMPORT IFIS Receipt Register (verJuly 11th 2011).xls

fn is the name of the new workbook. This is at the begiing of my code:

Dim fn As String

fn = vrtSelectedItem

fn winds up being the name of the text file that I choose to Import (changes daily but always begins with IFIS_Receipt_Register_)

Then I have this portion of code as well to save the file as xls instead of txt:

'Save file in Excel Format
    fn = Replace(fn, ".txt", ".xls")
Application.ScreenUpdating = True
Range("A1").Select
    ActiveWorkbook.SaveAs Filename:=fn, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
 
    MsgBox "The text file has been formated into excel.  The excel file was saved as : " & Chr(13) & fn, vbInformation
End Sub

When I used the macro recorder this is basically what I am trying to accomplish:

  Sheets("IFIS Receipt Register Errors").Copy after:=Workbooks( _
        "[B][COLOR=red]IFIS_Receipt_Register_140311 BEFORE[/COLOR][/B].xls").Sheets(1)

In the example above
fn = IFIS_Receipt_Register_140311 BEFORE.txt

I fear I may be confusing things even more now but I HOPE not.

THANKS,
Mark

 

[Norie]

Mark

I think you can set references to both workbooks like I mentioned in my first post.

That assumes of course that you know at some point the name of the file you are opening.

Which you kind of must or you wouldn't be able to open it.

The code to create a reference to the workbook the code is in:

Dim wbThis As Workbook
 
   Set wbThis = ThisWorkbook

The code to create a reference to the file you are opening.

Dim wbOpen As Workbook
 
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

You can the use these 2 references whenever you need to reference the workbooks in the code.

I hope that makes some sort of sense.

Perhaps if you posted the whole code I could show you exactly what I mean.

 

[Mister H]

THANKS Norie:

I am not really sure of how to add the reference you describe so here is a small portion of code. I removed all my formatting and removeal of the useless data as the code was very long and not very pretty

Here is the portion that gets the txt file and renames it:

Sub Format_Text_to_Excel()
'
    Dim fd As FileDialog
    Dim fn As String
    
    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 & "\"
    
    Dim vrtSelectedItem As Variant
    With fd
        .Filters.Clear
        .Filters.Add "All files", "*.txt"
        
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
             fn = vrtSelectedItem
             
            Next vrtSelectedItem
        Else
        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
    
 
'Rename Tab
With ActiveSheet
    .Name = "IFIS Receipt Register"
End With
Range("A1").Select
 
 
 
'Copy IFIS Receipt Register Errors sheet to new file
[B]'If I can just reference the open book that would work great
[/B]Windows("IMPORT IFIS Receipt Register (verJuly 11th 2011).xls").Activate
 
[COLOR=red][B][COLOR=black]'I need to copy the sheet below to the txt file that was open.  PASTE it 'at the end
[/COLOR]Sheets("IFIS Receipt Register Errors").Copy after:=Sheets(Sheets.Count)[/B][/COLOR]
[COLOR=red][/COLOR]

'Save file in Excel Format
    fn = Replace(fn, ".txt", ".xls")
Application.ScreenUpdating = True
Range("A1").Select
    ActiveWorkbook.SaveAs Filename:=fn, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
        
    MsgBox "The text file has been formated into excel.  The excel file was saved as : " & Chr(13) & fn, vbInformation
End Sub

THANKS for everything
Mark

 

[Norie]

Mark

You stick the first code I posted right at the start, you put the second where you open the text file.

I could probably post the code for that but it's not really the important part, the important part is what you do with thre references in the rest of the code.

I'm afraid I can't actually work that out.

For example, ActiveSheet, what does sheet does that refer to and in which workbook?

By the way you might be able to do this without opening any other workbook or file.

You could use Data>Get External Data... to import the data from the text file and put it in a new worksheet, or wherever is appropriate.

 

[Mister H]

THANKS for the info Norie. I (you) just about have it working. The only issue I am having now is to get the sheet to PASTE into the new workbook. I have highlight the line of code that is causing an error

Sub Format_Text_to_Excel()
'
    Dim fd As FileDialog
    Dim fn As String
    Dim wbThis As Workbook
 
    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 & "\"
 
    Dim vrtSelectedItem As Variant
    With fd
        .Filters.Clear
        .Filters.Add "All files", "*.txt"
 
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
             fn = vrtSelectedItem
 
            Next vrtSelectedItem
        Else
        End If
 
    End With
    Set fd = Nothing
 
    If Len(fn) < 3 Then
        Exit Sub
    End If
 
 
Dim wbOpen As Workbook
 
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
With ActiveSheet 'CURRENTLY this is the ONLY sheet in the workbook
    .Name = "IFIS Receipt Register"
End With
Range("A1").Select
 
 
 
'Copy IFIS Receipt Register Errors sheet to new file
'I need to copy the sheet "IFIS Receipt Register Errors" that is located in wbThis
 
wbThis.Activate
Sheets("IFIS Receipt Register Errors").Select
Sheets("IFIS Receipt Register Errors").Copy
 
 
 
'I need to Paste the "IFIS Receipt Register Errors"`sheet into the wbOpen workbook.  PASTE it at the end
wbOpen.Activate
[COLOR=red]Sheets("IFIS Receipt Register Errors").Paste after:=Sheets(Sheets.Count)[/COLOR]
 
 
 'Save file in Excel Format
    fn = Replace(fn, ".txt", ".xls")
Application.ScreenUpdating = True
Range("A1").Select
    ActiveWorkbook.SaveAs Filename:=fn, FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
 
    MsgBox "The text file has been formated into excel.  The excel file was saved as : " & Chr(13) & fn, vbInformation
End Sub

If anyone can assist in helping me get the correct code to PASTE the sheet into the workbook wbOpen that would be AWESOME.

Take Care,
Mark

 

[Mister H]

Hi:

Just hoping that someone today can assist in getting the code to PASTE into the workbook (wbOpen):

'I need to Paste the sheet into the wbOpen workbook.  PASTE it at the end

wbOpen.Activate
Sheets("IFIS Receipt Register Errors").Paste after:=Sheets(Sheets.Count)

THANKS,
Have a GREAT day
Mark