Copying cells with a macro

[Guest]

I'm working on a spreadsheet that performs some calculations and I have a
button that creates and renames a new worksheet if pressed. I need/would
like for the same button to also copy some information from this original
worksheet into the new sheet if certain conditions exist. For instance, if
the button is pressed, a new sheet is opened, renamed and original data is
checked to see if the column B value=1. If it does, the next few columns
within that row are copied to the new sheet. There may/will also be multiple
entries with a 1 in column B and I need all of those entries copied to the
new worksheet.
Thanks!

 

[Bernie Deitrick]

MAW,

Try something like the macro below. You can modify it to suit your specific
needs. Assumes your data table starts in cell A1.

HTH,
Bernie
MS Excel MVP

Sub Export1s_to_SeparateSheets()
'Export is based on the value in column B
Dim mySht As Worksheet
Dim myCurSht As Worksheet
Dim KeyCol As Integer

KeyCol = 2
Set myCurSht = ActiveSheet
Set mySht = Worksheets.Add(before:=Worksheets(1))
mySht.Name = "New Sheet"
With myCurSht.Range("B1").CurrentRegion
.AutoFilter Field:=KeyCol, Criteria1:=1
.SpecialCells(xlCellTypeVisible).Copy _
mySht.Range("A1")
mySht.Cells.EntireColumn.AutoFit
.AutoFilter
End With

End Sub