I need a macro to move rows from one sheet to another based on the row
meeting two criteria.

Sheet1!("dump-hr") contains large quantity of data (pasted in) I would like
to move rows that meet specific criteria in columns "N" and "P" to
sheet2!("blm10"). The criteria for column "N" is "blm" and for column "P"
it's "10". Hopefully the rows would be moved to the next available row on
Sheet2!("blm"), then Sheet2!("blm") would need to be saved.
Sheet1!("dump-hr") would then be deleted of data, and more data pasted in to
start the process over.

Is this possible?

Thanks in advance!

M.A.Tyler.

Make a copy of your workbook to test this on first just to be sure. If the
sheet names as shown on the tabs are not dump-hr and blm10, change the code
to use the actual sheet names as shown on the tabs.

Copy the routine below and put it into a regular code module. To do that,
open your workbook and use [Alt]+[F11] to get into the VB Editor. Choose
Insert | Module from the menu of the VB Editor. Paste the code into the
module and close the VB Editor.

It will copy all rows on the dump-hr sheet with any form of 'blm' in column
N (as blm, BLM, Blm, etc) and the value 10 in column P. It will stop copying
when it comes to an empty cell in column N. If the blm10 sheet is empty it
will start putting the copied information on row 2, otherwise it will start
at the first row on that sheet with an empty cell in column N.

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "dump-hr" ' change if needed
Const dstSheetName = "blm10" ' change if needed
Dim Roffset As Long
Dim dstRow As Long

Worksheets(srcSheetName).Select
Range("N1").Select
Do Until IsEmpty(ActiveCell.Offset(Roffset, 0))
'case is important we make sure of match to BLM,
'and if P# is text vs number then
'enclose 10 in quotes as = "10" below
If UCase(Trim(ActiveCell.Offset(Roffset, 0))) = "BLM" And _
ActiveCell.Offset(Roffset, 2) = 10 Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop
'empty out the dump-hr sheet
ActiveSheet.Cells.Clear
Set srcRange = Nothing
Set dstRange = Nothing
End Sub


"M.A.Tyler" wrote:

I need a macro to move rows from one sheet to another based on the row
meeting two criteria.

Sheet1!("dump-hr") contains large quantity of data (pasted in) I would like
to move rows that meet specific criteria in columns "N" and "P" to
sheet2!("blm10"). The criteria for column "N" is "blm" and for column "P"
it's "10". Hopefully the rows would be moved to the next available row on
Sheet2!("blm"), then Sheet2!("blm") would need to be saved.
Sheet1!("dump-hr") would then be deleted of data, and more data pasted in to
start the process over.

Is this possible?

Thanks in advance!

M.A.Tyler.

WOW! That's fantastic, Thank you very much.

However I have a new problem, to narrow the data further I set up another
sheet. It uses data in from the "dump-hr" sheet. the formula's are all set-up
using "dump-hr", but they now need to use the data from the sheet you helped
me create, "blm10". Is it possible to write a new formula to change all the
cells from "dump-hr" to "blm10"? at once? The range is from C3:Q100. would
hate to have to change them all individually.

Thanks again.

M.A.Tyler

"JLatham" wrote:

Make a copy of your workbook to test this on first just to be sure. If the
sheet names as shown on the tabs are not dump-hr and blm10, change the code
to use the actual sheet names as shown on the tabs.

Copy the routine below and put it into a regular code module. To do that,
open your workbook and use [Alt]+[F11] to get into the VB Editor. Choose
Insert | Module from the menu of the VB Editor. Paste the code into the
module and close the VB Editor.

It will copy all rows on the dump-hr sheet with any form of 'blm' in column
N (as blm, BLM, Blm, etc) and the value 10 in column P. It will stop copying
when it comes to an empty cell in column N. If the blm10 sheet is empty it
will start putting the copied information on row 2, otherwise it will start
at the first row on that sheet with an empty cell in column N.

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "dump-hr" ' change if needed
Const dstSheetName = "blm10" ' change if needed
Dim Roffset As Long
Dim dstRow As Long

Worksheets(srcSheetName).Select
Range("N1").Select
Do Until IsEmpty(ActiveCell.Offset(Roffset, 0))
'case is important we make sure of match to BLM,
'and if P# is text vs number then
'enclose 10 in quotes as = "10" below
If UCase(Trim(ActiveCell.Offset(Roffset, 0))) = "BLM" And _
ActiveCell.Offset(Roffset, 2) = 10 Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop
'empty out the dump-hr sheet
ActiveSheet.Cells.Clear
Set srcRange = Nothing
Set dstRange = Nothing
End Sub


"M.A.Tyler" wrote:

I need a macro to move rows from one sheet to another based on the row
meeting two criteria.

Sheet1!("dump-hr") contains large quantity of data (pasted in) I would like
to move rows that meet specific criteria in columns "N" and "P" to
sheet2!("blm10"). The criteria for column "N" is "blm" and for column "P"
it's "10". Hopefully the rows would be moved to the next available row on
Sheet2!("blm"), then Sheet2!("blm") would need to be saved.
Sheet1!("dump-hr") would then be deleted of data, and more data pasted in to
start the process over.

Is this possible?

Thanks in advance!

M.A.Tyler.

I'm certain that it is - pretty much anything you can do from the keyboard
you can do with VB code. But I'm not sure what you mean about 'change all
the cells ... all at once'. What is it in the cells that needs changing?
Part of formulas? Specific Values? Since I've never seen either page I have
no clue as to what is on or in them other than that column N is full of 'blm'
and P is full of 10.

Or do you need to move/copy all cells from the dump-hr sheet to the blm10
sheet in one swell-foop?

"M.A.Tyler" wrote:

WOW! That's fantastic, Thank you very much.

However I have a new problem, to narrow the data further I set up another
sheet. It uses data in from the "dump-hr" sheet. the formula's are all set-up
using "dump-hr", but they now need to use the data from the sheet you helped
me create, "blm10". Is it possible to write a new formula to change all the
cells from "dump-hr" to "blm10"? at once? The range is from C3:Q100. would
hate to have to change them all individually.

Thanks again.

M.A.Tyler

"JLatham" wrote:

Make a copy of your workbook to test this on first just to be sure. If the
sheet names as shown on the tabs are not dump-hr and blm10, change the code
to use the actual sheet names as shown on the tabs.

Copy the routine below and put it into a regular code module. To do that,
open your workbook and use [Alt]+[F11] to get into the VB Editor. Choose
Insert | Module from the menu of the VB Editor. Paste the code into the
module and close the VB Editor.

It will copy all rows on the dump-hr sheet with any form of 'blm' in column
N (as blm, BLM, Blm, etc) and the value 10 in column P. It will stop copying
when it comes to an empty cell in column N. If the blm10 sheet is empty it
will start putting the copied information on row 2, otherwise it will start
at the first row on that sheet with an empty cell in column N.

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "dump-hr" ' change if needed
Const dstSheetName = "blm10" ' change if needed
Dim Roffset As Long
Dim dstRow As Long

Worksheets(srcSheetName).Select
Range("N1").Select
Do Until IsEmpty(ActiveCell.Offset(Roffset, 0))
'case is important we make sure of match to BLM,
'and if P# is text vs number then
'enclose 10 in quotes as = "10" below
If UCase(Trim(ActiveCell.Offset(Roffset, 0))) = "BLM" And _
ActiveCell.Offset(Roffset, 2) = 10 Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop
'empty out the dump-hr sheet
ActiveSheet.Cells.Clear
Set srcRange = Nothing
Set dstRange = Nothing
End Sub


"M.A.Tyler" wrote:

I need a macro to move rows from one sheet to another based on the row
meeting two criteria.

Sheet1!("dump-hr") contains large quantity of data (pasted in) I would like
to move rows that meet specific criteria in columns "N" and "P" to
sheet2!("blm10"). The criteria for column "N" is "blm" and for column "P"
it's "10". Hopefully the rows would be moved to the next available row on
Sheet2!("blm"), then Sheet2!("blm") would need to be saved.
Sheet1!("dump-hr") would then be deleted of data, and more data pasted in to
start the process over.

Is this possible?

Thanks in advance!

M.A.Tyler.

It would be part of the formulas in another sheet named "blm10000". It
narrows the information again, but I set it up to look at the cells in
"dump-hr" not "blm10". "blm10000" is set up like a chart, so the formula in
each cell had to be written individually. Each formula refers to "dump-hr"
and now should be refering to "blm10". Is it possible to change the
"dump-hr" reference in each cell to "blm10"? Without doing it one at a time?

"JLatham" wrote:

I'm certain that it is - pretty much anything you can do from the keyboard
you can do with VB code. But I'm not sure what you mean about 'change all
the cells ... all at once'. What is it in the cells that needs changing?
Part of formulas? Specific Values? Since I've never seen either page I have
no clue as to what is on or in them other than that column N is full of 'blm'
and P is full of 10.

Or do you need to move/copy all cells from the dump-hr sheet to the blm10
sheet in one swell-foop?

"M.A.Tyler" wrote:

WOW! That's fantastic, Thank you very much.

However I have a new problem, to narrow the data further I set up another
sheet. It uses data in from the "dump-hr" sheet. the formula's are all set-up
using "dump-hr", but they now need to use the data from the sheet you helped
me create, "blm10". Is it possible to write a new formula to change all the
cells from "dump-hr" to "blm10"? at once? The range is from C3:Q100. would
hate to have to change them all individually.

Thanks again.

M.A.Tyler

"JLatham" wrote:

Make a copy of your workbook to test this on first just to be sure. If the
sheet names as shown on the tabs are not dump-hr and blm10, change the code
to use the actual sheet names as shown on the tabs.

Copy the routine below and put it into a regular code module. To do that,
open your workbook and use [Alt]+[F11] to get into the VB Editor. Choose
Insert | Module from the menu of the VB Editor. Paste the code into the
module and close the VB Editor.

It will copy all rows on the dump-hr sheet with any form of 'blm' in column
N (as blm, BLM, Blm, etc) and the value 10 in column P. It will stop copying
when it comes to an empty cell in column N. If the blm10 sheet is empty it
will start putting the copied information on row 2, otherwise it will start
at the first row on that sheet with an empty cell in column N.

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "dump-hr" ' change if needed
Const dstSheetName = "blm10" ' change if needed
Dim Roffset As Long
Dim dstRow As Long

Worksheets(srcSheetName).Select
Range("N1").Select
Do Until IsEmpty(ActiveCell.Offset(Roffset, 0))
'case is important we make sure of match to BLM,
'and if P# is text vs number then
'enclose 10 in quotes as = "10" below
If UCase(Trim(ActiveCell.Offset(Roffset, 0))) = "BLM" And _
ActiveCell.Offset(Roffset, 2) = 10 Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop
'empty out the dump-hr sheet
ActiveSheet.Cells.Clear
Set srcRange = Nothing
Set dstRange = Nothing
End Sub


"M.A.Tyler" wrote:

I need a macro to move rows from one sheet to another based on the row
meeting two criteria.

Sheet1!("dump-hr") contains large quantity of data (pasted in) I would like
to move rows that meet specific criteria in columns "N" and "P" to
sheet2!("blm10"). The criteria for column "N" is "blm" and for column "P"
it's "10". Hopefully the rows would be moved to the next available row on
Sheet2!("blm"), then Sheet2!("blm") would need to be saved.
Sheet1!("dump-hr") would then be deleted of data, and more data pasted in to
start the process over.

Is this possible?

Thanks in advance!

M.A.Tyler.

You should be able to do that right from the keyboard:

Select all of your cells, use Edit | Replace
Click the [Options] button so you can see them and enter
dump-hr
for the Find
blm10
for the Replace with
and make sure that the option for [Within] is set to 'Sheet' and that the
[Look In] option is set to 'Formulas' ... then click [Replace All] and poof!
Done deal.


"M.A.Tyler" wrote:

It would be part of the formulas in another sheet named "blm10000". It
narrows the information again, but I set it up to look at the cells in
"dump-hr" not "blm10". "blm10000" is set up like a chart, so the formula in
each cell had to be written individually. Each formula refers to "dump-hr"
and now should be refering to "blm10". Is it possible to change the
"dump-hr" reference in each cell to "blm10"? Without doing it one at a time?

"JLatham" wrote:

I'm certain that it is - pretty much anything you can do from the keyboard
you can do with VB code. But I'm not sure what you mean about 'change all
the cells ... all at once'. What is it in the cells that needs changing?
Part of formulas? Specific Values? Since I've never seen either page I have
no clue as to what is on or in them other than that column N is full of 'blm'
and P is full of 10.

Or do you need to move/copy all cells from the dump-hr sheet to the blm10
sheet in one swell-foop?

"M.A.Tyler" wrote:

WOW! That's fantastic, Thank you very much.

However I have a new problem, to narrow the data further I set up another
sheet. It uses data in from the "dump-hr" sheet. the formula's are all set-up
using "dump-hr", but they now need to use the data from the sheet you helped
me create, "blm10". Is it possible to write a new formula to change all the
cells from "dump-hr" to "blm10"? at once? The range is from C3:Q100. would
hate to have to change them all individually.

Thanks again.

M.A.Tyler

"JLatham" wrote:

Make a copy of your workbook to test this on first just to be sure. If the
sheet names as shown on the tabs are not dump-hr and blm10, change the code
to use the actual sheet names as shown on the tabs.

Copy the routine below and put it into a regular code module. To do that,
open your workbook and use [Alt]+[F11] to get into the VB Editor. Choose
Insert | Module from the menu of the VB Editor. Paste the code into the
module and close the VB Editor.

It will copy all rows on the dump-hr sheet with any form of 'blm' in column
N (as blm, BLM, Blm, etc) and the value 10 in column P. It will stop copying
when it comes to an empty cell in column N. If the blm10 sheet is empty it
will start putting the copied information on row 2, otherwise it will start
at the first row on that sheet with an empty cell in column N.

Sub MoveRowsToBLM10()

Dim srcRange As Range
Dim dstRange As Range
Const srcSheetName = "dump-hr" ' change if needed
Const dstSheetName = "blm10" ' change if needed
Dim Roffset As Long
Dim dstRow As Long

Worksheets(srcSheetName).Select
Range("N1").Select
Do Until IsEmpty(ActiveCell.Offset(Roffset, 0))
'case is important we make sure of match to BLM,
'and if P# is text vs number then
'enclose 10 in quotes as = "10" below
If UCase(Trim(ActiveCell.Offset(Roffset, 0))) = "BLM" And _
ActiveCell.Offset(Roffset, 2) = 10 Then
Set srcRange = Worksheets(srcSheetName). _
Rows(Roffset + 1 & ":" & Roffset + 1)
'find available row on dest sheet
dstRow = Worksheets(dstSheetName).Range("N" _
& Rows.Count).End(xlUp).Row + 1
Set dstRange = Worksheets(dstSheetName). _
Rows(dstRow & ":" & dstRow)
dstRange.Value = srcRange.Value
End If
Roffset = Roffset + 1
Loop
'empty out the dump-hr sheet
ActiveSheet.Cells.Clear
Set srcRange = Nothing
Set dstRange = Nothing
End Sub


"M.A.Tyler" wrote:

I need a macro to move rows from one sheet to another based on the row
meeting two criteria.

Sheet1!("dump-hr") contains large quantity of data (pasted in) I would like
to move rows that meet specific criteria in columns "N" and "P" to
sheet2!("blm10"). The criteria for column "N" is "blm" and for column "P"
it's "10". Hopefully the rows would be moved to the next available row on
Sheet2!("blm"), then Sheet2!("blm") would need to be saved.
Sheet1!("dump-hr") would then be deleted of data, and more data pasted in to
start the process over.

Is this possible?

Thanks in advance!

M.A.Tyler.