Posts by Costyngh

VBA Code To Select Next Sheet:

Sub SelectNextSheet()
'PURPOSE: Select the next visible sheet in the spreadsheet
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet

'Store currently selected sheet
  Set sht = ActiveSheet

'Loop to next sheet until visible one is found
  On Error Resume Next
    
    Do While sht.Next.Visible <> xlSheetVisible
      If Err <> 0 Then Exit Do
        Set sht = sht.Next
    Loop
    
    'Activate/Select next sheet
      sht.Next.Activate
  
  On Error GoTo 0

End Sub

VBA Code To Select Previous Sheet:

Sub SelectPreviousSheet()
'PURPOSE: Select the previous visible sheet in the spreadsheet
'SOURCE: www.TheSpreadsheetGuru.com/the-code-vault

Dim sht As Worksheet

'Store currently selected sheet
  Set sht = ActiveSheet

'Loop to next sheet until visible one is found
  On Error Resume Next
    
    Do While sht.Previous.Visible <> xlSheetVisible
      If Err <> 0 Then Exit Do
        Set sht = sht.Previous
    Loop
    
    'Activate/Select next sheet
      sht.Previous.Activate
  
  On Error GoTo 0

End Sub

Sub RemovePartsOfTable()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects("Table1")

'Remove 3rd Column
  tbl.ListColumns(3).Delete

'Remove 4th DataBody Row
  tbl.ListRows(4).Delete

'Remove 3rd through 5th DataBody Rows
  tbl.Range.Rows("3:5").Delete

'Remove Totals Row
  tbl.TotalsRowRange.Delete

End Sub


Sub Creeare_sheet_nou()
'
' Creeare_sheet_nou Macro
'

'
    Sheets.Add After:=ActiveSheet
End Sub

Option Explicit
Sub Button1_Click()
   'this is definetly a copy for ozgrid
    Dim r As Range
 
    Set r = Sheet1.Cells(1).CurrentRegion
    
    r.Copy
    With Sheet2
        .[a1].PasteSpecial xlValues
        .ListObjects.Add(xlSrcRange, .Range(r.Address), , xlYes).Name = "Table1"
        .ListObjects("Table1").TableStyle = "Table Style 4"
        .Columns.AutoFit
    End With
    With Application
        .CutCopyMode = False
        .Goto Sheet2.[a1]
    End With

End Sub