Formatting a range of data as a Table

[bosko2]

Hi All,

Just a quick one, I have the below VBA Code where a user selects their data using drop downs on an opening sheet titled 'Report Builder'. The code takes the filtered data from a Master table titled 'Actions' (this master table is hidden and protected), and performs a 'Paste Special' onto a blank worksheet titled 'My team's actions'. I have attempted to perform some minor formatting on the new sheet, without much luck.

Specifically, can the new range of data on the 'My team's actions' be formatted as a table once the paste special has ceased?

Set DbExtract = ThisWorkbook.Sheets("Actions")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's Actions")
   
    With Sheets("Report Creator")
   If .Cells(2, 1) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value
   End If
   If .Cells(2, 2) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
   End If
   .Select
End With

    Sheets("Safety Accountability Dashboard").Select
    Sheets("Actions").Visible = True
    Sheets("Actions").Select
    Sheets("My team's actions").Visible = True

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial
   
    Sheets("Actions").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("My team's actions").UsedRange.AutoFilter
    Selection.WrapText = True
    Sheets("My t[COLOR=rgb(0, 0, 0)]eam's actions").Select
    ActiveSheet.Rows("1:1").Select
    Selection.Font.Bold = True[/COLOR]
    Selection.HorizontalAlignment = xlLeft
   
    Sheets("Report Creator").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Safety Accountability Dashboard").Select


End Sub

Any assistance would be greatly appreciated,
bosko2

 

[Norie]

You can easily convert a range to a table like this.

    With DuplicateRecords
        .ListObjects.Add xlSrcRange, .Range("A1").CurrentRegion
    End With

 

[bosko2]

@Norie where would I put this in my code?

Apologies for such minor questions

 

[Norie]

Right after you've pasted the data into 'My team's actions'.

DuplicateRecords.Cells(1, 1).PasteSpecial

With DuplicateRecords
    .ListObjects.Add xlSrcRange, .Range("A1").CurrentRegion
End With

 

[bosko2]

Right after you've pasted the data into 'My team's actions'.

DuplicateRecords.Cells(1, 1).PasteSpecial

With DuplicateRecords
    .ListObjects.Add xlSrcRange, .Range("A1").CurrentRegion
End With

So sorry dude, I'm having trouble with integrating it into my VBA; so I thought that I will post the entire VBA to give you more context:

Sub Report_Builder()


Sheets("My team's investigations").Range("A1:BF9999").ClearContents
Sheets("My team's actions").Range("A1:BF9999").ClearContents

With Sheets("Report Creator")
   If .Cells(2, 1) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 14, .Cells(2, 1).Value
   End If
   If .Cells(2, 2) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 15, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Investigations").Range("A1").AutoFilter 16, .Cells(2, 3).Value
   End If
   .Select
End With


Sheets("Report Creator").Select

Sheets("Safety Accountability Dashboard").Visible = True

Sheets("Safety Accountability Dashboard").Select



    Dim DbExtract, DuplicateRecords As Worksheet
    Set DbExtract = ThisWorkbook.Sheets("Investigations")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's investigations")

    Sheets("Safety Accountability Dashboard").Select
    Sheets("Investigations").Visible = True
    Sheets("Investigations").Select
    Sheets("My team's investigations").Visible = True

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial
    
    Sheets("Investigations").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("My team's investigations").UsedRange.AutoFilter
    Selection.WrapText = True
    ActiveSheet.Rows("1:1").Select
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlLeft

    Set DbExtract = ThisWorkbook.Sheets("Actions")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's Actions")
    
    With Sheets("Report Creator")
   If .Cells(2, 1) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value
   End If
   If .Cells(2, 2) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
   End If
   .Select
End With

    Sheets("Safety Accountability Dashboard").Select
    Sheets("Actions").Visible = True
    Sheets("Actions").Select
    Sheets("My team's actions").Visible = True

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
    DuplicateRecords.Cells(1, 1).PasteSpecial
    
    Sheets("Actions").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("My team's actions").UsedRange.AutoFilter
    Selection.WrapText = True
    Sheets("My team's actions").Select
    ActiveSheet.Rows("1:1").Select
    Selection.Font.Bold = True
    Selection.HorizontalAlignment = xlLeft
    
    Sheets("Report Creator").Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Safety Accountability Dashboard").Select


End Sub