locked
Help RRS feed

  • Question

  • Hi

    Good Day Everyone

    I have to form a table in which data on daily basis is to be stored. is there any function which can take values from  different worksheets and generate a  single table ?

    Friday, October 21, 2016 5:26 AM

All replies

  • Hi,

    If you want to create a VBA project, you could use Workbooks.Open Method (Excel) to open the source workbooks to get the data from different worksheets and use ListObjects.Add Method (Excel) to format as a table.

    E.g.

    Sub Demo()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim iws As Worksheet
    Set iws = ActiveSheet
    Set wb = Workbooks.Open("C:\test.xlsx")
    Set ws = wb.Worksheets("Sheet1")
    Set rng = ws.Range("A1:B2")
    rng.Copy
    iws.Range("A1:B2").PasteSpecial (xlPasteAll)
    iws.ListObjects.Add(xlSrcRange, iws.Range("A1:B2"), , xlNo).Name = "Table1"
    wb.Close
    End Sub



    • Edited by Chenchen Li Monday, October 24, 2016 6:14 AM
    Monday, October 24, 2016 6:08 AM