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

  • GoCavs
    replied
    tried it...works

    thanks again!!

    Leave a comment:


  • GoCavs
    replied
    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

    Leave a comment:


  • GCExcel
    replied
    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

    Leave a comment:


  • GoCavs
    replied
    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!

    Leave a comment:


  • GCExcel
    replied
    You're welcome!

    Leave a comment:


  • GoCavs
    replied
    Oh wow. I really appreciate that. Code works perfectly.

    Thank you GC!!!!!

    Leave a comment:


  • GCExcel
    replied
    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.

    Leave a comment:


  • GoCavs
    replied
    Thanks GC!!!

    Leave a comment:


  • GCExcel
    replied
    Hi GoCavs,

    I can help you with this.

    GC

    Leave a comment:

Working...
X