VBA to create, rename and paste into a new sheet

Question

I'm new to VBA.

I'm looking to create some code that will filter a table on one sheet copy this then paste into a new sheet, whilst that new sheet has been renamed with today's date, then hiding the existing sheet again. This is need weekly and possibly daily.

So far I have

Sub test2() ' ' test2 Macro ' ' ActiveSheet.ListObjects("Pipeline").Range.AutoFilter Field:=1, Criteria1:= _ "<>" Range("Pipeline[[#Headers],[FC]]").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Dim SheetName As String SheetName = Format(Date, "dd-mm-yyyy") 'Change the format as per your requirement Sheets.Add , Worksheets(Worksheets.Count) ActiveSheet.Name = SheetName End Sub

This is filtering the range in the table and copying, and also creating the new worksheet. But how do I then past into the new sheet then hide the sheet again. Ideally I suppose the code needs to unhide the sheet to begin with. The sheet name is FC_Pipeline.

Any help is greatly appreciated.
Thanks Ted

Zsmaster · Accepted Answer · 2017-06-12 13:21:31Z

This is to hide and create a new sheet with today's date

Sub Makro() Dim currentSheet As String currentSheet = ActiveSheet.Name Dim SheetName As String SheetName = Format(Date, "dd-mm-yyyy") Sheets.Add After:=ActiveSheet ActiveSheet.Name = SheetName Sheets(currentSheet).Visible = False End Sub

Zsmaster · Accepted Answer · 2017-09-01 09:34:34Z

I think the code would be like this

Sub test2() Dim Ws As Worksheet, newWs As Worksheet Dim SheetName As String Set Ws = ActiveSheet ActiveSheet.ListObjects("Pipeline").Range.AutoFilter Field:=1, Criteria1:="<>" SheetName = Format(Date, "dd-mm-yyyy") 'Change the format as per your requirement Sheets.Add , Worksheets(Worksheets.Count) ActiveSheet.Name = SheetName Set newWs = ActiveSheet Ws.Range("Pipeline[#All]").SpecialCells(xlCellTypeVisible).Copy newWs.Range("a1") Ws.Visible = xlSheetHidden End Sub

I stepped through the code and it works up til this point Ws.Range("Pipeline[#All]").Copy newWs.Range("a1") Ws.Visible = xlSheetHidden It appears to be pasting the whole sheet and not the selected part as per this code ActiveSheet.ListObjects("Pipeline").Range.AutoFilter Field:=1, Criteria1:= _ "<>" Range("Pipeline[[#Headers],[FC]]").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy

Collectives™ on Stack Overflow

VBA to create, rename and paste into a new sheet

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related