[Solved] How to copy each table in a sheet to other sheet

[bigme]

dear friends,
i have a sheets (name : source) that fill with several small table, kindly help me how to copy each table to other sheets (name : template) and print it, and clear the table before next table copied to template, till the last table, thank you for your help.

regards,
bigMe

 

[Yousuf Shovon]

i have a sheets (name : source) that fill with several small table, kindly help me how to copy each table to other sheets (name : template) and print it, and clear the table before next table copied to template, till the last table

Hello Bigme,

You can copy each table from Source sheet, paste them in Template separately and finally print them using Excel VBA.

However, it's necessary to format the Source sheet data into Table formats. This is because you have empty cells in your table and this would be confusing for Excel to differentiate between the tables. You can format the data into Tables using the second method of this How to Create a Table in Excel with Data article.
So, your tables should look like this.

​

Note: Excel will name your tables as Table1, Table2, Table3, and so on.

Now, you run the below VBA code:

Sub CopyAndPrintNamedTables()
    Dim sourceSheet As Worksheet
    Dim templateSheet As Worksheet
    Dim table As ListObject
    Dim pasteRange As Range
    Dim colWidths() As Double
    Dim col As Long
    Dim numRows As Long
    Dim startRow As Long
 
    Set sourceSheet = ThisWorkbook.Sheets("source")
    Set templateSheet = ThisWorkbook.Sheets("template")
    pasteRow = 8
 
    For Each table In sourceSheet.ListObjects
     
        If table.Name Like "Table*" Then
            numRows = table.ListRows.Count
            startRow = table.ListRows(1).Range.Row
         
         
            Set pasteRange = templateSheet.Range("A" & pasteRow)
            table.DataBodyRange.Copy pasteRange
        
            ReDim colWidths(1 To table.ListColumns.Count)
            For col = 1 To table.ListColumns.Count
                colWidths(col) = table.ListColumns(col).Range.ColumnWidth
            Next col
         
            pasteRange.Resize(numRows, table.ListColumns.Count).EntireRow.AutoFit
         
            For col = 1 To table.ListColumns.Count
                pasteRange.Resize(numRows, 1).Offset(0, col - 1).ColumnWidth = colWidths(col)
            Next col
         
            templateSheet.PrintOut
         
            pasteRange.Resize(numRows, table.ListColumns.Count).Clear
         
            pasteRow = 8
        End If
    Next table
End Sub

Consequently, the macro will ask you to rename the PDF files and save them.

​

So, the given 6 tables will appear in the template in 6 PDF files like this.

​

I am attaching the workbook file. Let me know if it works for you.

Regards.

 

[bigme]

Dear Yousuf,
very big thank you for your help, according to your explanation, the data should be transform into table format, this makes a new case... how do i change each data into tables? since in the sheets there are several data (in this sample the data become 6 tables) please help me with this matter, a huge thank you for your help.

regards,
bigMe

 

[Yousuf Shovon]

how do i change each data into tables?

Hello Bigme,

Thank you for your response. And Yes, it is necessary to convert the data to Excel table format first to accurately define them. Here is the way to do so:

Steps:

• First, select the data you want to define as a table >> go to Home >> Format as Table >> choose any table format.

​

• Tap OK in Create Table dialog.

​

• Thus, the selected range turns into a table format.

​

• Repeat the same steps for other tables too.​

Note: Excel automatically names your created table as Table1, Table2 etc. These names were used in the VBA code of my previous post. If you change your table names, make sure you change them in the code too.

For details, click How to Create a Table in Excel with Data. Let me know if you have further queries related to this topic.

Regards.

 

[bigme]

Hi, Yousuf,
thank you for the explanation, but can we do it using VBA instead doing it manually one by one? thank you.

regards,
bigMe

 

[Yousuf Shovon]

thank you for the explanation, but can we do it using VBA instead doing it manually one by one?

Hello Bigme,

Thank you for your feedback. Formatting data into Excel Tables can be troublesome with VBA. Instead, let's try an easier way.

I have modified the VBA code that takes sections of data from the source worksheet, pastes them into the template worksheet, saves each section as a separate PDF file, and repeats the process until all sections have been saved as PDFs. And, the good news is, this time you can use the raw data without formatting them into Excel tables. Here are the steps to do so:

• Copy the VBA code to a module and Run it.

Sub PrintPDF()

'Produced by ExcelDemy

Set Sh1 = Sheets("source")
Set sh2 = Sheets("template")

Set Rng1 = Sh1.Range("A1:D22")
Set Rng2 = sh2.Range("A8")

Start = 1
savePath = "C:\Users\PC 05\Downloads\Bigme\Sheet"
Number = 1
iRows = 1
iColumns = 1

For i = 1 To Rng1.Rows.Count
    If Rng1.Cells(i, 4).Value = "" Then
        Ending = i - 1
        Range(Rng2.Cells(1, 1), Rng2.Cells(iRows, iColumns)).ClearContents
        Set PrintRng = Range(Rng1.Cells(Start, 1), Rng1.Cells(Ending, 4))
        PrintRng.Copy Destination:=Rng2
        savePath = savePath & Str(Number) & ".pdf"
        sh2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=savePath, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
        Start = i + 1
        Number = Number + 1
        savePath = "C:\Users\PC 05\Downloads\Bigme\Sheet"
        iRows = PrintRng.Rows.Count
        iColumns = PrintRng.Columns.Count
    End If
Next i
    
End Sub

• Consequently, the tables will be automatically saved in the given file path.

• Open any file to check if the printed template is accurate.

Code Explanation:

savePath = "C:\Users\PC 05\Downloads\Bigme\Sheet"

Don't forget to modify the file path where you want to save the pdf files. Also, change the file names in this line as you desire. For example, I have set the file names as Sheet 1, Sheet 2, and so on.

Set Sh1 = Sheets("source")
Set sh2 = Sheets("template")

Set Rng1 = Sh1.Range("A1:D22")
Set Rng2 = sh2.Range("A8")

This section of the code is the input section. Make sure you take an extra row after the last table while declaring the Set Rng1. For example. the last row of the last table was D21. So, I have taken D22 as the last row.

Tips: Instead of printing tables, you can directly copy and paste tables as images or HTML tables into Email body using VBA. For details, click on How to Use Excel VBA to Paste Table or Range into Email Body.

I have attached the Excel workbook. Kindly let me know if it works for you.

Regards.

 

[bigme]

wow...... thank you very much.....
thank you for your kindness for helping me....

best regards,
bigMe