Announcement

Collapse
No announcement yet.

$30 USD: VBA copy formatting from a row to several rows in Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • $30 USD: VBA copy formatting from a row to several rows in Table



    Pre-payment has been sent.

    I have a table (Table1) located in the 'Snapshot' tab of the attached file. Table1 begins on row 11 (header row on row 10). I am looking for a macro that will copy the Format of the 2nd row of the table (row 12) and apply that format to the remaining rows of Table1. The reason for this is due to having another macro that imports new data into the table and when it does, some rows end have a different formatting than the existing rows (Wrapped Text, column A not bolded). I normally use the Format Painter option and manually adjust the data. Was hoping to accomplish this with a macro. Attached sample file for reference.

    Thank you and let me know if you have any ?'s
    Attached Files

  • #2
    Hi GoCavs,

    I can help you with this.

    GC

    Comment


    • #3
      Thanks GC!!!

      Comment


      • #4
        Hi GoCavs,

        This is a very simple macro, here it is:

        Code:
        Sub FormatTable()
           Dim lo As ListObject
           
           Application.ScreenUpdating = False
        
           Set lo = ThisWorkbook.Sheets("Snapshot").ListObjects("Table1")
           lo.DataBodyRange.Resize(1).Offset(1, 0).Copy
           lo.DataBodyRange.PasteSpecial xlPasteFormats
           Application.CutCopyMode = False
           lo.DataBodyRange.Cells(1, 1).Select
           
           Application.ScreenUpdating = True
        
        End Sub
        No need to send $, it took me less than 5 minutes.

        Comment


        • #5
          Oh wow. I really appreciate that. Code works perfectly.

          Thank you GC!!!!!

          Comment


          • #6
            You're welcome!

            Comment


            • #7
              One more question...I tried running code from a button in another worksheet and it seems to get hung up on the following line:

              Code:
              lo.DataBodyRange.Cells(1, 1).Select
              Is there a way to adjust the code to run from other than the sheet where the table resides? Thank you!

              Comment


              • #8
                You can remove that line of code. It was simply to select the first cell in the table. It's not mandatory
                If you want to keep it add before:

                Code:
                Sheets("YourOtherSheet").Activate

                Comment


                • #9
                  So if my table is in a sheet named 'Snapshot', the code would be ??

                  Code:
                   Sub FormatTable()
                   Dim lo As ListObject
                   Application.ScreenUpdating = False
                  Set lo = ThisWorkbook.Sheets("Snapshot").ListObjects("Table1")
                   lo.DataBodyRange.Resize(1).Offset(1, 0).Copy
                   lo.DataBodyRange.PasteSpecial xlPasteFormats
                   Application.CutCopyMode = False
                   Sheets("Snapshot").Activate
                   lo.DataBodyRange.Cells(1, 1).Select
                  
                   Application.ScreenUpdating = True
                  End Sub

                  Comment


                  • #10


                    tried it...works

                    thanks again!!

                    Comment

                    Working...
                    X