Mehedi Hasan Shimul

• Hi NARASIMHAN!

Thank you for your queries. Let’s change the code a bit. Use the following code to copy the data and paste it as values.

Sub CopyWorksheetsToNewWorkbook()
    Dim srcWorkbook As Workbook
    Dim newWorkbook As Workbook
    Dim srcWorksheet As Worksheet
    Dim newWorksheet As Worksheet
    ' Set the source workbook
    Set srcWorkbook = ThisWorkbook
    ' Create a new workbook
    Set newWorkbook = Workbooks.Add
    ' Loop through each worksheet in the source workbook
    For Each srcWorksheet In srcWorkbook.Worksheets
        ' Copy only values to the new workbook
        srcWorksheet.UsedRange.Value = srcWorksheet.UsedRange.Value
        ' Copy the data to the new workbook
        srcWorksheet.Copy After:=newWorkbook.Sheets(newWorkbook.Sheets.Count)
        Set newWorksheet = newWorkbook.Sheets(newWorkbook.Sheets.Count)
        newWorksheet.Name = srcWorksheet.Name
        newWorksheet.Cells.Copy
        newWorksheet.Cells.PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    Next srcWorksheet
    ' Save and close the new workbook
    ' Replace "C:\Path\To\Save\NewWorkbook.xlsx" with your desired file path and name
    newWorkbook.SaveAs "C:\ExcelDemy\NewWorkbook.xlsx"
    newWorkbook.Close
    ' Clean up
    Set newWorksheet = Nothing
    Set newWorkbook = Nothing
    Set srcWorksheet = Nothing
    Set srcWorkbook = Nothing
End Sub

Afterward, you will see result like this.

Hi MIGUEL,

We are glad, you asked the questions. It’s quite easy to import data from separate sheets and save it as separate documents. Simply, use the following code in a new module of VBA. Only, you have to change the file directory to save the doc file.

Sub ExportWorksheetsToWord()
Dim objWord As Object
Dim objDoc As Object
Dim objRange As Object
Dim ws As Worksheet
' Create a new instance of Microsoft Word
Set objWord = CreateObject("Word.Application")
' Make Word visible (optional)
objWord.Visible = True
' Loop through each worksheet in the workbook
For Each ws In ThisWorkbook.Worksheets
' Create a new Word document for each worksheet
Set objDoc = objWord.Documents.Add
' Reference the range of the current worksheet
Set objRange = ws.UsedRange
' Copy the range to the Clipboard
objRange.Copy
' Paste the range into the Word document
objDoc.Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
' Save the Word document with the worksheet name
objDoc.SaveAs "C:\ExcelDemy\" & ws.Name & ".docx" ' Replace with your desired file path
' Close the Word document
objDoc.Close
' Clean up
Set objDoc = Nothing
Next ws
' Close Microsoft Word
objWord.Quit
' Clean up
Set objWord = Nothing
Set objRange = Nothing
End Sub

After running the code, you will see the doc files created according to your dataset.