Creating a Macro

[rxsp]

Hi,
I want to create a macro that will convert a text file into excel file. After the conversion, there will be only 1 sheet in the excel file. Next, I need to take that 1 sheet from the excel file and copy and paste into another excel workbook.
I am able to create the macro to convert a text file into excel file. However, I am not able to create a macro to copy and paste that excel sheet (which has the converted text file) into another excel workbook.
The problem is the name of the sheet. When I convert the text file into excel file, the sheet gets an automatic name based on the title of the text file. However, the name of the sheet will keep changing every month when i use this macro because each month, the text file has a different name....so how do I create a VBA code that would reference that converted text to excel file worksheet without using its name?

Thank you,

 

[EFANYoutube]

Hi rxsp & welcome
Listen, you have to be very explicit with your coding, VBA allows you to do a lot of thiings that other languages don't because it has been made to be used by people with little to ne coding experience. I think you just aren't creating a variable to keep the instance of your workbooks, worksheets in.
If you share your code we could help you reconfigure it to be more explicit and controlled

 

[Saurabhj]

Hi,

when you are converting text file to excel, there would be only one sheet in excel.

So instead of using sheet name Sheets("sheetname") you can use Sheets(1).

 

[EFANYoutube]

Hi,

when you are converting text file to excel, there would be only one sheet in excel.

So instead of using sheet name Sheets("sheetname") you can use Sheets(1).

Again, you are assuming things
Better to reference the sheet in a new workbook when creating the workbook from text and keep that reference for copying than to just assume

 

[rxsp]

Hi,

when you are converting text file to excel, there would be only one sheet in excel.

So instead of using sheet name Sheets("sheetname") you can use Sheets(1).

Hi, I just did that and it worked...thank you...but now my problem is that I want to copy that sheet into another workbook however, that workbook name will change every month...how do I create a VBA for that? Basically the same situation as the Sheet name but I can't just put Workbooks (1) because that didn't work.

 

[EFANYoutube]

If you post some code we can clean it up for you. The way your doing it, you'll have to change the code when ever something small changes. But up to you

 

[rxsp]

If you post some code we can clean it up for you. The way your doing it, you'll have to change the code when ever something small changes. But up to you

I would like to copy and paste the sheet titled Client Text from a workbook (this workbook will always have a different name per month) to a different workbook (this workbook will always have a different name per month)....here is my code but don't know what to put in the ??
Sheets("Client Text") .Select
Sheets("Client Text") .Copy After:=Workbooks(??).Sheets(4)

 

[EFANYoutube]

So this is what I mean
You should be in a position where youc can control everything
so the below code should work if you set everything up a bit better

Sub copySheet()
    Dim app As Application
    Set app = Application
    Dim thisWB As Workbook
    Dim wbFromText As Workbook
    Dim wbToCopyTo As Workbook
    
    'Code to copy your text to excel file but make sure you reference the new file as wbFromText
    '.......
    
    'Now you can open a workbook to copy to by asking the user
    strfile = Application.GetOpenFilename(FileFilter:="Excel files (*.xlsx*), *.xlsx*", Title:="Choose an Excel file to open", MultiSelect:=True)
    'now you have the path to the wb you want to copy to
    app.Workbooks.Add strfile 'add doesn't mean create just open
    wbFromText.Sheets("Client Text").Copy After:=wbToCopyTo.Sheets(4)
    
End Sub

 

[rxsp]

So this is what I mean
You should be in a position where youc can control everything
so the below code should work if you set everything up a bit better

Sub copySheet()
    Dim app As Application
    Set app = Application
    Dim thisWB As Workbook
    Dim wbFromText As Workbook
    Dim wbToCopyTo As Workbook
   
    'Code to copy your text to excel file but make sure you reference the new file as wbFromText
    '.......
   
    'Now you can open a workbook to copy to by asking the user
    strfile = Application.GetOpenFilename(FileFilter:="Excel files (*.xlsx*), *.xlsx*", Title:="Choose an Excel file to open", MultiSelect:=True)
    'now you have the path to the wb you want to copy to
    app.Workbooks.Add strfile 'add doesn't mean create just open
    wbFromText.Sheets("Client Text").Copy After:=wbToCopyTo.Sheets(4)
   
End Sub

I tried but didn't work...I think its because I have other codes....let me send you my full code but I took out the codes for formatting the workbook as it would be too long...

Sub Format_Audit_For_QC()

myFile = Application.GetOpenFilename(, , "Browse for Workbook")

Workbooks.Open myFile

'bunch of codes to format the workbook
'Look for text file

fName = Application.GetOpenFilename()


Workbooks.OpenText Filename:=fName, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _

xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _

Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _

Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _

Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _

TrailingMinusNumbers:=True

Sheets(1).Select

Sheets(1).Name = "Client Text"
Range("D25") .Select

Sheets ("Client Text") .Select
With ActiveWorkbook.Sheets ("Client Text") .Tab
.Color = 5296274

'bunch of codes to format this Client Text sheet
'now I need to copy and paste this Client Text sheet and into that 1st workbook I manipulated in the beginning

 

[EFANYoutube]

Have a play with this
You'll see how I have referenced everything so you have control over every object your are manipulating

Sub Format_Audit_For_QC()
    Dim app As Application
    Set app = Application
    Dim thisWB As Workbook
    Dim wbFromText As Workbook
    Dim wbToCopyTo As Workbook
    myFile = Application.GetOpenFilename(, , "Browse for Workbook")
    Set wbToCopyTo = Workbooks.Open(myFile)
    
    'bunch of codes to format the workbook
    'Look for text file
    
    fName = Application.GetOpenFilename()
    
    
    Set wbFromText = Workbooks.OpenText(Filename:=fName, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
    Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
    Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _
    TrailingMinusNumbers:=True)
    
    'Copy the sheet
    wbFromText.Sheets("Sheet1").Copy After:=wbToCopyTo.Sheets(4)
    
    
    
    
    Sheets(1).Select
    
    Sheets(1).Name = "Client Text"
    Range("D25") .Select
    
    Sheets ("Client Text") .Select
    With ActiveWorkbook.Sheets ("Client Text") .Tab
    .Color = 5296274
    
    'bunch of codes to format this Client Text sheet
    'now I need to copy and paste this Client Text sheet and into that 1st workbook I manipulated in the beginning
End Sub