Please make my code for an active workbook

[KAMRAN AJ]

Below is a code it is on the file name if I change the file name it will not work, please make it for an active workbook. Now my workbook name is Windows("io_2022-09-16.xlsx").Activate



Sub test4()
' test4 Macro pass
Workbooks.Add
Range("A1").Select
ActiveCell.FormulaR1C1 = "MB_ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Website"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Business Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Last Name"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Address"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Country"
Range("G1").Select
ActiveCell.FormulaR1C1 = "City"
Range("H1").Select
ActiveCell.FormulaR1C1 = "State"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Postal Code"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Phone"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Email"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Business Type"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Industry"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Lead Import Source"
Range("N2").Select
ActiveCell.FormulaR1C1 = "Data Import MB"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Contact Type"
Range("O2").Select
ActiveCell.FormulaR1C1 = "New Lead"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Migrating From"
Range("P2").Select
ActiveCell.FormulaR1C1 = "MINDBODY"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Import ID"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "KA 2023_May 02, May 04"
Windows("PERSONAL.XLSB").Activate
Windows("io_2022-09-16.xlsx").Activate
End Sub


Details
I make a code but it is on the file name, if i change the workbook name it will not work please make it for an active workbook

[Paul_Hossler]

I edited your post to add CODE tags to format the macro for easier viewing. Use the [#] icon next time please

I cleaned up the macro since it's not necessary to .Select objects to act on them

This adds a new workbook, put data in rows 1 and 2, and then activates what ever workbook the macro is in



Option Explicit


Sub test4_phh()
Dim wb As Workbook
Workbooks.Add
Set wb = ActiveWorkbook
With wb
.Range("A1").Value = "MB_ID"
.Range("B1").Value = "Website"
.Range("C1").Value = "Business Name"
.Range("D1").Value = "Last Name"
.Range("E1").Value = "Address"
.Range("F1").Value = "Country"
.Range("G1").Value = "City"
.Range("H1").Value = "State"
.Range("I1").Value = "Postal Code"
.Range("J1").Value = "Phone"
.Range("K1").Value = "Email"
.Range("L1").Value = "Business Type"
.Range("M1").Value = "Industry"
.Range("N1").Value = "Lead Import Source"
.Range("N2").Value = "Data Import MB"
.Range("O1").Value = "Contact Type"
.Range("O2").Value = "New Lead"
.Range("P1").Value = "Migrating From"
.Range("P2").Value = "MINDBODY"
.Range("Q1").Value = "Import ID"
.Range("Q2").Value = "KA 2023_May 02, May 04"
End With
ThisWorkbook.Activate
End Sub

[KAMRAN AJ]

Option Explicit


Hi dear the code you have provided me was showing me error ,unfornatly
I cannot attached a screenshot error= (Run_time error "438"
object doesnot support this property or method)
i modified the code a use it

but the code below is working for me but brother i important work is not yet completed
now i have to copy and paste rows

Sub test4_phh()
Dim wb As Workbook

Workbooks.Add

Set wb = ActiveWorkbook

With wb
Range("A1").Select
ActiveCell.FormulaR1C1 = "MB_ID"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Website"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Business Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Last Name"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Address"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Country"
Range("G1").Select
ActiveCell.FormulaR1C1 = "City"
Range("H1").Select
ActiveCell.FormulaR1C1 = "State"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Postal Code"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Phone"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Email"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Business Type"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Industry"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Lead Import Source"
Range("N2").Select
ActiveCell.FormulaR1C1 = "Data Import MB"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Contact Type"
Range("O2").Select
ActiveCell.FormulaR1C1 = "New Lead"
Range("P1").Select
ActiveCell.FormulaR1C1 = "Migrating From"
Range("P2").Select
ActiveCell.FormulaR1C1 = "MINDBODY"
Range("Q1").Select
ActiveCell.FormulaR1C1 = "Import ID"
Range("Q2").Select
ActiveCell.FormulaR1C1 = "KA 2023_May 02, May 04"
End With

ThisWorkbook.Activate
End Sub

[mancubus]

With wb.Worksheets("Sheet1")
change Sheet1 to the name of any worksheet. (local excel application's default. you can see the application's default tab name(s) when you open a blank workbook.)

or

With wb.Worksheets(1)

[mancubus]

alternatively...



Sub vbax_70827()

Dim wb As Workbook

Workbooks.Add
Set wb = ActiveWorkbook

With wb.Worksheets(1)
.Range("A1:Q1").Value = Array("MB_ID", "Website", "Business Name", "Last Name", "Address", "Country", "City", "State", "Postal Code", "Phone", _
"Email", "Business Type", "Industry", "Lead Import Source", "Contact Type", "Migrating From", "Import ID")
.Range("N2:Q2").Value = Array("Data Import MB", "New Lead", "MINDBODY", "KA 2023_May 02, May 04")
.Columns.AutoFit
End With

ThisWorkbook.Activate

End Sub