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,

 

[rxsp]

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

Thanks will try this.

 

[rxsp]

Thanks will try this.

I got an error stating: Compile error: Expected Function or variable and it highlighted the OpenText
Set wbFromText = Workbooks.OpenText(Filename:=fName, Origin:=437, StartRow:=1, DataT

 

[EFANYoutube]

Sorry I didn't know the constraint of the function .opentext, so this should work

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()
    
    
    app.Workbooks.OpenText fName, 437, 1, xlDelimited, xlDoubleQuote, False, True, False, False, False, False, 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)), True
    Set wbFromText = ActiveWorkbook
    'Copy the sheet
    wbFromText.Sheets(1).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

 

[rxsp]

Sorry I didn't know the constraint of the function .opentext, so this should work

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()
   
   
    app.Workbooks.OpenText fName, 437, 1, xlDelimited, xlDoubleQuote, False, True, False, False, False, False, 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)), True
    Set wbFromText = ActiveWorkbook
    'Copy the sheet
    wbFromText.Sheets(1).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

Thanks so much...will try this

 

[rxsp]

Hi, for some reason I get error (Object variable or with block variable not set) and it highlights this code below:
'Copy the sheet
wbFromText.Sheets(1).Copy After:=wbToCopyTo.Sheets(4)

 

[EFANYoutube]

I would say there are not 4 sheets in the workbook your copying to. If you specify after the fourth sheet, the forth has to exist.

 

[rxsp]

This:
myFile = Application.GetOpenFilename(, , "Browse for Workbook")
Set wbToCopyTo = Workbooks.Open(myFile)
has 4 sheets with data.

When I run the VBA code, it 1st asks me to select the excel file that has 4 sheets with data already in it from my folder
myFile = Application.GetOpenFilename(, , "Browse for Workbook")
Set wbToCopyTo = Workbooks.Open(myFile)

then it does the formatting that I coded, then I am asked to select the text file from the same folder fName = Application.GetOpenFilename()
it does the conversion from text to excel (in a new workbook) which is Set wbFromText = ActiveWorkbook

at this point, I can see that I have 2 workbooks open....the 1st one (wbToCopyTo) has 4 sheets with data and the 2nd workbook (wbFromText) has the converted text into excel in sheet 1

Now, it gives me an error when trying to copy that 1 sheet from workbook (wbFromText) to sheet 5 on the wbToCopyTo by using this VBA code wbFromText.Sheets(1).Copy After:=wbToCopyTo.Sheets(4)

I even changed that 4 into 5 but still getting an error.

 

[EFANYoutube]

Need to find out where it's failing
What happens in you put this instead?

wbFromText.Sheets(1).Copy wbToCopyTo.Sheets(4)

 

[rxsp]

I finally got it.
I changed the code from wbFromText.Sheets(1).Copy After:=wbToCopyTo.Sheets(4)
to wbFromText.Sheets("Client Text").Copy After:=wbToCopyTo.Sheets(4) and placed it at the end of the VBA codes....I realized that I changed the name of the sheet to Client Text and that needed to be identified.
Thanks so much for all your help with all the codes...I am still learning and took some online courses for VBA coding....but I still have alot to learn.

 

[rxsp]

one thing, what is the vba code to place the words "QC of" in front of the title of the wbToCopyTo? I want to place this code at the end of coding.
the rest of the title of this workbook will change every month, but the words "QC of" will always be in front.