# Copy filtered table VBA



## KieranO (Jan 5, 2023)

I need to copy a filtered table to the end worksheet when the code is ran (The last sheet name changes weekly) & not knowing much about coding this is what i have pieced together from all over the internet which i appreciate may be terrible but is giving me a syntax error on the destination line of code:


```
Sub Copy_SA()

Dim lo As ListObject

Dim NumberOfAreas As Long

Dim LastSheetName As Worksheet



Set lo = Sheet2.ListObjects(1)

Set LastSheetName = ActiveWorkbook.Sheets(Sheets.Count).Name



With lo.ListColumns(1).Range

NumberOfAreas = .SpecialCells(xlCellTypeVisible).Cells.Count - 1

Debug.Print NumberOfAreas

End With

lo.Range.SpecialCells(xlCellTypeVisible).Copy

Destination:=Sheets(LastSheetName).Range("B13")

Application.CutCopyMode = False



End Sub
```


Could anybody offer any help or a direction to look in?


----------



## DanteAmor (Jan 5, 2023)

Try this:


```
Sub Copy_SA()
  Dim lo As ListObject
  Dim LastSheetName As* Worksheet   *'As Worksheet to store the sheet *object*
 
  Set lo = sheet2.ListObjects(1)
 * Set LastSheetName *= ActiveWorkbook.Sheets(Sheets.Count)   'Set the sheet as *object*
 
'The Copy and Destination statement go on the same line:
  lo.Range.SpecialCells(xlCellTypeVisible).Copy Destination:=*LastSheetName*.Range("B13")   'Use the *object*
  Application.CutCopyMode = False
End Sub
```

Or this


```
Sub Copy_SA_2()
  Dim lo As ListObject
  Dim LastSheetName As* String  * 'As Strint to store the sheet *name*
 
  Set lo = sheet2.ListObjects(1)
  *LastSheetName = *ActiveWorkbook.Sheets(Sheets.Count)*.Name  *'Stores the name of the sheet
 
  lo.Range.SpecialCells(xlCellTypeVisible).Copy Destination:=*Sheets(LastSheetName)*.Range("B13")   'Use the sheet name inside Sheets()
  Application.CutCopyMode = False
End Sub
```

Both codes work, check the differences.


----------



## KieranO (Jan 6, 2023)

DanteAmor said:


> Try this:
> 
> 
> ```
> ...





DanteAmor said:


> Try this:
> 
> 
> ```
> ...



Thank you, it works as required now which is great but also for taking the time to explain - it seems like i was storing LastSheetName as a object, trying to set it as an object but also store it as a namean then calling for it throughout the code as a name, but it was actually trying to be a object, would that be right?


----------



## KieranO (Jan 6, 2023)

Would anyone be able to help with this next part?  Using the code given above i've adapted it a little so i can filter the table > paste to last sheet > 
re-filter the table > paste that underneath the previous pasted info.  In between these 2 i am trying to merge a row of cells and insert a heading however because there is not a fixed range i'm struggling on how to insert the text with a offset from the last row.

Code given by DanteAmor which works great

```
Sub Copy_SA()
  Dim lo As ListObject
  Dim LastSheetName As String   'As String to store the sheet name
 
  Set lo = Sheet2.ListObjects(1)
  LastSheetName = ActiveWorkbook.Sheets(Sheets.Count).Name  'Stores the name of the sheet
 
  lo.Range.SpecialCells(xlCellTypeVisible).Copy Destination:=Sheets(LastSheetName).Range("B13")   'Use the sheet name inside Sheets()
  Application.CutCopyMode = False
  
  
End Sub
```

Adapted piece of code to paste with a offset from last used row

```
Sub Copy_U100()

  Dim lo As ListObject
  Dim LastSheetName As String   'As String to store the sheet name
 
  Set lo = Sheet2.ListObjects(1)
  LastSheetName = ActiveWorkbook.Sheets(Sheets.Count).Name  'Stores the name of the sheet
 
  lo.Range.SpecialCells(xlCellTypeVisible).Copy _
  Destination:=Sheets(LastSheetName).Range("B" & Rows.Count).End(xlUp).Offset(11)   'Use the sheet name inside Sheets()
  Application.CutCopyMode = False
  
  
End Sub
```

This is what i have so far; im trying to select the last sheet > the range columns will always be fixed between B & K however the rows will never be the same so i want to select columns B:K and then the row that is the offset and insert a generic heading in that cell

```
Sub U100_Heading()

 Sheets(LastSheetName).Range("B:K" & Rows.Count).End(xlUp).Offset(13).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
End Sub
```


----------



## KieranO (Jan 6, 2023)

So a method i've thought of is having the heading already formatted on a different sheet then before i paste my table i copy/paste my heading first with a larger off set then my table which will have a smaller off set and i've produced this:

```
Sub Copy_U100()

  Dim lo As ListObject
  Dim LastSheetName As String   'As String to store the sheet name
 
  Set lo = Sheet2.ListObjects(1)
  LastSheetName = ActiveWorkbook.Sheets(Sheets.Count).Name  'Stores the name of the sheet
 
  Sheets("Sheet2").Range("W11:AG11").Copy _
  Destination:=Sheets(LastSheetName).Range("A" & Rows.Count).End(xlUp).Offset(15)
 
  lo.Range.SpecialCells(xlCellTypeVisible).Copy _
  Destination:=Sheets(LastSheetName).Range("B" & Rows.Count).End(xlUp).Offset(2)   'Use the sheet name inside Sheets()
  Application.CutCopyMode = False
  
  
End Sub
```
 
But now im getting a out of range error


----------

