Copying cells with a macro
 

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!

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(befo=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





"MAWII" wrote in message
...
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!