Copying all sheets from one workbook to another

Question

I have an XLTM Template and an xlsx workbook.

The XLSX workbook has around 300-400 sheets.

I am looking to copy sheets from XLSX to XLSM Template.

What is the fastest way to do it?

I would like to have the same sheet name and formats along with the values.

Here is a copy of the code that I am using,

For k = 1 To xlWkb.Worksheets.Count
    xlWkb.Activate
    xlWkb.Sheets(k).Select
    xlWkb.Sheets(k).Cells.Copy

    xlWkb2.Activate
    xlWkb2.Sheets("Sheet" & k).Select
    xlWkb2.Sheets("Sheet" & k).Range("A1").Select
    xlWkb2.ActiveSheet.Paste ' changes the column width and all, I want those formats

    xlWkb2.Sheets("Sheet" & k).Name = xlWkb.Sheets(k).Name
Next

Answer 1

First of all, remove the lines that have Activate and Select on them and restructure it more like this:

For k = 1 To xlWkb.Worksheets.Count
    xlWkb.WorkSheets(k).Cells.Copy
    xlWkb2.WorkSheets("Sheet" & k).Range("A1").Paste 
    xlWkb2.WorkSheets("Sheet" & k).Name = xlWkb.WorkSheets(k).Name
Next

Answer 2

Please, find next API

Sheets("SheetName").Copy Before:=Workbooks("WorkbookName").Sheets(1)
Workbooks("WorkbookName").Sheets(1).Name = Sheets("SheetName").Name

This method Copy entire Worksheet with formatting, range names, values, formulas.. etc.