Copying the Visible Rows

If you want to make it easy to create a new worksheet containing a copy of the filtered data, you can place an ActiveX command button at the top of the worksheet and enter the following Click event procedure in the worksheet module. This procedure copies the visible cells in Table1:

Private Sub CommandButton1_Click() Dim wksNew As Worksheet Dim sWksName As String Dim sMonth As String Dim sYear As String Dim wksDummyWks As Worksheet

'Get Date values sMonth = Me.ComboBoxl.Value sYear = Me.ComboBox2.Value

'Check that month has not been copied

On Error Resume Next

sWksName = Format(DateValue(sYear & "-" & sMonth & "-1"

), "mmm yyyy")

Set wksDummyWks = Worksheets(sWksName)

If Err.Number = 0 Then

MsgBox "This data has already been copied"

Exit Sub

End If

On Error GoTo 0

'Add new worksheet and copy visible cells from Tablel

Set wksNew = Worksheets.Add

Me.ListObjects(1).Range.SpecialCells(xlCellTypeVisible)

.Copy _

Destination:=wksNew.Range("A1")

wksNew.Columns("A:G").AutoFit

'Name worksheet

wksNew.Name = sWksName

End Sub

The Click event procedure first calculates a name for the new worksheet in the format mmmyyyy. It then checks to see if this worksheet already exists by setting a dummy object variable to refer to a worksheet with the new name. If this does not cause an error, the worksheet already exists and the procedure issues a message and exits.

If there is no worksheet with the new name, the event procedure adds a new worksheet at the beginning of the existing worksheets. It copies the visible cells in Table1 to the new sheet and AutoFits the column widths to accommodate the copied data. The procedure then names the new worksheet.

0 0

Post a comment