transfering information to another sheet

[i8ig]

Is there a simple macro to take all the information in a row and transfer it to another sheet based on a date in one of the columns?

 

[ravishankar]

Hi
paste the following codes in the macro window (Alt F11)

sub list()
b = cells(1,1)
x= worksheets("sheet1").cells(rows.count,1).end(xlUp).row
c= 1
for a  = 1 to x
if cells(a,1) = b then
worksheets("sheet1").rows(a).copy
worksheets("sheet2").rows(c).pastespecial
c = c+1
endif
next a
end sub

enter the date in cell A1 and run the macro. It will copy all rows with given date in col A to sheet 2
Ravi

 

[i8ig]

Here is the macro that is already in the sheet. the date column is 'M' and the spread sheet is named by the month i.e. August, the next Sept. dates are entered as 8/02/07. My question is where should I insert this new macro and based on the above information do I need to change any of the parameters in the new macro?
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    If Target.Value = "Med" Then
        Rows(Target.Row).Interior.ColorIndex = 4
        Cells(Target.Row, 10) = "=IF(RC[3]="""","""",RC[3]-3)"
    Else
        If Target.Value = "Tasc" Then
            Rows(Target.Row).Interior.ColorIndex = 6
            Cells(Target.Row, 10) = "=IF(RC[1]="""","""",RC[1]-2)"
            Cells(Target.Row, 11) = "=IF(RC[2]="""","""",RC[2])"
        Else
            If Target.Value = "Nbar" Then
                Rows(Target.Row).Interior.ColorIndex = x1ColorIndexNone
                Cells(Target.Row, 22) = "=IF(RC[-1]="""","""",RC[-1]+7)"
                Cells(Target.Row, 21) = "=IF(RC[-1]="""","""",RC[-1]+7)"
                Cells(Target.Row, 20) = "=IF(RC[-7]="""","""",RC[-7]+14)"
                Cells(Target.Row, 12) = "=IF(RC[1]="""","""",RC[1]-5)"
                Cells(Target.Row, 11) = "=IF(RC[1]="""","""",RC[1]-2)"
                Cells(Target.Row, 10) = "=IF(RC[1]="""","""",RC[1]-2)"
            Else
                Rows(Target.Row).Interior.ColorIndex = xlColorIndexNone
            End If
        End If
    End If
End If
End Sub

 

[ravishankar]

Hi
Press Alt and F11 simultaneously. goto Insert menu and choose module. paste the codes given below in the window you get.

sub list() 
b = cells(1,1) 
x= worksheets("sheet1").cells(rows.count,13).end(xlUp).row 
c= 1 
for a  = 1 to x 
if cells(a,13) = b then 
worksheets("sheet1").rows(a).copy 
worksheets("sheet2").rows(c).pastespecial 
c = c+1 
endif 
next a 
end sub

change sheet1 to sheet where you want searched say aug or sep. change sheet2 to sheet name where you want the data pasted. run the macro.
Ravi