# transfering information to another sheet

#### i8ig

##### Board Regular
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?

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### ravishankar

##### Well-known Member
Hi
paste the following codes in the macro window (Alt F11)
Code:
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

##### Board Regular
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
Code:
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

##### Well-known Member
Hi
Press Alt and F11 simultaneously. goto Insert menu and choose module. paste the codes given below in the window you get.
Code:
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

Replies
0
Views
125
Replies
1
Views
322
Replies
5
Views
336
Replies
9
Views
159
Replies
3
Views
566

1,191,119
Messages
5,984,758
Members
439,909
Latest member
daigoku

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back