Add New workbook

Dim new_xl As Object

Dim workbook_name As String

Set new_xl = Workbooks.Add

new_xl.Activate

workbook_name = ActiveWorkbook.Name

Sheets("sheet1").Select

Cells(1, 1) = "CURRENT STOCK REPORT OF ALL PRODUCTS"

‘copy data from xl file x.xls

For i = 1 To lastrow

  Cells(i + 2, 1) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 1)

  Cells(i + 2, 2) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 2)

  Cells(i + 2, 3) = Application.Workbooks("x.xls").Worksheets("Balanced").Cells(i, 3)

Next i

‘Formatting

Range("A1:F1").Select

    Selection.Merge

    With Selection

        .HorizontalAlignment = xlLeft

        .VerticalAlignment = xlBottom

        .WrapText = False

        .Orientation = 0

        .AddIndent = False

        .IndentLevel = 0

        .ShrinkToFit = False

        .ReadingOrder = xlContext

        .MergeCells = True

    End With

    With Selection.Interior

        .ColorIndex = 10

        .Pattern = xlSolid

    End With

    Selection.Font.ColorIndex = 2

    Selection.Font.Bold = True

    Cells.Select

    Cells.EntireColumn.AutoFit

End Sub