none
update worksheet table data RRS feed

  • Question

  • Hey Fellows,

    I am working on an issue since couple of days but cannot find the right solution. I have a macro that copy the data from the main sheet and paste it in a second sheet using Excel table (listobjects). In the first run the data are well saved and the table style is added correctly, but if i add a new row on the main datasheet and run the macro again the second table add a new line under the table but with no formatting as data above it. The code is added below..........  ANY HELP OUT THERE GUYS!!!!!!!!!!!!!!....

    'if no table exist, create new tables and set it to variable LO(listobject)
        If temp = False Then
            '''''''''''''create equipment table''''''''''''''''''
            Worksheets(copyto5).ListObjects.Add(xlSrcRange, CurrentTable, , xlYes).Name = "equipment" & Iteration
            Set LO = Worksheets(copyto5).ListObjects("equipment" & Iteration)   
            
        Else 'if table do exist, set it to variable
          
            Set LO = Worksheets(copyto5).ListObjects("equipment" & Iteration)
            
            ' resize objects ..............................................
            'Set rng = Range("equipment" & Iteration & "[#All]").Resize(LineRange.Rows.Count, LO.Range.Columns.Count)
          
            'LO.Resize rng
           
           
        End If


    • Edited by paulkengne Thursday, September 25, 2014 6:26 AM
    Thursday, September 25, 2014 6:11 AM

All replies

  • Hi Paul,

    How did you copy the data from the main sheet and paste it in a second sheet using Excel table (listobjects)?

    Would you mind providing a workable sample for us to reproduce this issue?

    >>but if i add a new row on the main datasheet and run the macro again the second table add a new line under the table but with no formatting as data above it.<<

    In fact, you could add a new row of a ListObject through ListRows.Add Method (Excel).

    Here is a sample for your reference:

    Sub Macro5()
        Dim ws As Worksheet
        Dim Iteration As Integer
        Set ws = Worksheets("Sheet2")
        Iteration = 1
        Dim l As ListObject
        Set LO = ws.ListObjects.Add(xlSrcRange, ws.Range("A1:C4"), , xlYes)
        LO.Name = "equipment" & Iteration
        Set LastRow = LO.ListRows.Add
        Worksheets("Sheet3").Range("A17:C17").Copy
        LastRow.Range.PasteSpecial xlPasteValues
    End Sub

    The code copy a range from another sheet into current ListObject as a new row.

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 3:27 AM
    Moderator
  • Hi George,

    I would like to say thanks a lot for you time. I wouldn't mind sharing my solution since i have been fighting with the issue for a while. but i do not know the best way to do that since i can't attach an excel file here.

    Also i found the issue as it is the style applied on the table object. in the code below when i comment the first line (workshhets(copyto5)............) and add new row then the data are updated correctly. but if the style is running a new row is added on the main sheet, this  will not appear on the second sheet

    If temp = False Then
        
            'equipment table
             Worksheets(copyto5).ListObjects.Add(xlSrcRange, CurrentTable, , xlYes).Name = "equipment" & Iteration
            Set LO = Worksheets(copyto5).ListObjects("equipment" & Iteration)
            
              
        Else 'if table do exist, set it to variable
            
            Set LO = Worksheets(copyto5).ListObjects("equipment" & Iteration)
    		
         
        End If

    thanks for your reply

    Friday, September 26, 2014 6:51 AM
  • Hi Paul,

    It seems the new row was not in the ListObject so that the format missed.

    >>but i do not know the best way to do that since i can't attach an excel file here.<<

    You can upload the excel sample through OneDrive. Share files and folders and change permissions and give us the link.

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 7:27 AM
    Moderator
  • Thanks a lot George for you time.

    The following  is the link of the two excel files i am using.

    https://onedrive.live.com/redir?resid=9A5063C6EBF0D0AE!105&authkey=!AN8UpW5pbbCeN1k&ithint=folder%2cxlsm 

    instructions:

    the main excel file is the Tank data template file which hold all the data. when you open it there is a mian sheet call tank data (with all the data). all the sheets on its left with the "#" sign are created when the macro is runned.

    To be specific lets take the case of #equipment. when you open the vba editor (module1). there is a function called 'CopyMasterData' which copy all the data from the main sheet. Inside the function, scrolling still the end you will see a part of the code "Equipment Table"  with 7 lines of stars (***********).   

    few lines down there is another small part commented with 4 lines of starts and is where i found the issue. 

    thank for your help

     


    • Edited by paulkengne Friday, September 26, 2014 8:32 AM
    Friday, September 26, 2014 8:32 AM
  • Hi Paul,

    Thanks for your information.

    According to my investigation, for example in sheet "cleaning process template", the table is not a ListObject:

    Thus there is no style if you insert new row.

    You need to add a ListObject with the data.

    Sheets("cleaning process template").ListObjects.Add(xlSrcRange, Range("A2:F2"), , xlYes).Name = "Table3"

    After that, all data in this table will have style.

    Regards,

    George.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 9:02 AM
    Moderator
  • I actually get your point George, but still a bit loss.

    since the Global_template is acting here just as a template, i am not using the same style when creating the #sheets in Tank data Template.

    All the tables 'style is rendered when creating new table(#equipment). did you inspected the #equipment sheet in Tank Data template?

    No Data are to be insert into cleaning process template

    BR Paul

     

     
    • Edited by paulkengne Friday, September 26, 2014 9:34 AM
    Friday, September 26, 2014 9:33 AM