Copy filtered table VBA

[KieranO]

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]

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]

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.

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.

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]

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]

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