I cannot help but think that we are corrupting the formulas over in that
blm10000 sheet. But I'm blind because I cannot see what is going on over
there in a 'before' and 'after' state.
Think about this: you have formulas on that sheet referring to the dump-hr
formula. I know this because of your request to change 'dump-hr' to 'blm10'
after all of the data movements. I'm thinking at the time you make that
request, that you should be looking at a lot of #REF entries on the blm10000
sheet. Why? Because we have deleted everything on the dump-hr sheet BEFORE
you changed the formulas.
There's NOTHING in the code that should even cause a 'calculate' event to
occur. It is the stuff in the blm10000 sheet that is trying to adjust to the
changes in the dump-hr sheet that is going nutz!! on you.
Now - that I know that there are no formulas or anything on the 'dump-hr'
sheet, then there are two ways to approach this that are emminently superior
to what we've done so far:
#1 - either simply rename 'dump-hr' to 'blm10' and then delete the rows that
we don't want. This will auto-update references to 'dump-hr' in the blm10000
sheet. Then have the code simply create a new, empty dump-hr sheet.
#2 - same type of deal: we use code to cut from 'dump-hr' into 'blm10' and
then delete rows we don't want. The cut'n'paste should auto-update
references of 'dump-hr' to 'blm-10'.
a potentially non-destructive setup would be to continue moving from
'dump-hr' to 'blm10', but not to do it in a compact manner where we are
filling from the top down, but rather to do a direct transition where stuff
on any given row from 'dump-hr' is copied to the same row on the 'blm10'
sheet, and then you do an auto-filter to display non-blank entries to view
the code all compacted together. But then I don't know what happens the next
time you try to bring in new data onto the dump-hr sheet, because the
possibility of overwriting older data seems very likely to me.
It's a 6 of 1, 1/2-dozen of the other type of situation.
But the basic problem still remains: we are deleting rows that formulas on
the 'blm10000' sheet may be referrencing, and I would expect you to be seeing
a lot of #REF entries on it anyhow - if it could ever finish calculating.
I sure hope you did as I suggested and have been doing all of this in a copy
of your original book.
Is there any chance of you emailing the book to me as an email attachment?
email address is (remove spaces) HelpFrom @ jlathamsite.com I'll treat info
as confidential and proprietary. But seeing that darned blm10000 page is
really going to help me figure out what's going on. Plus, having those 17K
rows of original data will be awesome for testing things. If there's no
chance, fine, we'll go it alone without it, but its going to make figuring
out why the performance is going downhill a hell of a lot more difficult to
diagnose and correct.
M.A.Tyler said:
"dump-hr" is void of any formulas, it is used to temporarily hold info until
the target info is extracted onto "blm10"."blm10 has no formulas either,
again it just holds data narrowed by the macro you wrote. The calculations
are in blm10000, and more or less narrow the data further based on column's
"H" and "M", once sorted it will provide an average, via an =average(if(
ARRAY) formula. If we were to eliminate the data in "blm10", blm10000 would
be empty cells. The cells in blm10000 that don't have any matching criteria
do give a #DIV/0 error, but the cells supported by data seem like their
working.
:
OUCH! Hold on to something solidly or sit down - you may not like this.
I realized while writing all of that previous post that there is what
appears to me to be a major problem with all of this. It has to do with
revising those formulas on the blm10000 sheet (I was also thinking of
automating that, and that's when this dawned on me).
Lets say you have a formula on blm10000 like this:
='dump-hr'!A2
but first bad condition: 'dump-hr'!A2 is not a "blm" and 10 row and so won't
get moved. Later we're going to change that to 'blm10'!A2 and that cell is
probably going to have a completely different value in it than 'dump-hr'!A2
had.
Even if that doesn't shoot us in one foot, I think this next part might:
Assume that 'dump-hr'!A2 does have 'blm' and 10 and does get moved, BUT row
1 from dump-hr did not get moved. Now what was in 'dump-hr'!A2 is now in
'blm10'!A1
but we change the formula over on blm10000 and it now reads ='blm10'!A2
instead of 'blm10'!A1 which is where we actually moved the data to.
Does this make sense to you?
This is going to be especially disruptive if you have references to cells on
dump-hr in blm10000 that do math such as ='dump-hr'!A1 + 'dump-hr'!B19 /
'dump-hr'!A4
You need to examine the blm10000 sheet before and after the processing to
move to the blm10 sheet and using Find | Replace to see what effect this is
having on the actual results.
If things are set up very simply, just echoing cell values with no math
done, over in the blm10000 sheet, then maybe no big deal at all - you'll have
a lot of 0 entries on blm10000, but if you've got more complex operations
going on in it, we may have to deal with that.
And not wanting to send you home for the weekend with a major headache, I do
have a potential solution. Change the process:
instead of moving data from 'dump-hr' to 'blm10', we would delete the
'blm10' sheet (if it exists) in the workbook and make a copy of the 'dump-hr'
sheet, naming that copy {surprise!} blm10. Then we go to the blm10000 sheet
and change all instances of 'dump-hr' to 'blm10' and then go back to the
blm10 sheet and simply delete the rows we DON'T want to keep, and finally
return to 'dump-hr' to erase the information on it.
The only potential problem I see here, which we'd have to figure out how to
deal with, is that I got the impression that you keep getting new information
into 'dump-hr' and need to add that below any existing information on
'blm10'. Can't just blow away sheet 'blm10' after 'blm10000' has been set up
to reference it - that really screws things up.
Or am I making a really steep-sided mountain out of an ant-hill here?
:
That worked well also!
However it seems there is a new problem. After pasteing more data to
"dump-hr" and subsequently running the macro, it took a long time to
calculate the cells. In fact it finished calculating, but never completed the
move. The sheets I'm pasteing have 700-2000 rows and run from A to CW in
terms of columns. Would this amount of data be considered overwhelming? these
sheets don't seem to take up very much space on my disc, but there seems to
be a significant memory problem? is there anyway to improve performance?
Instead of moving entire rows would it help to move smaller blocks of data?
The columns of data I'm using to create my data base are W:AC and AP:BB. So
if it would help by only moving those columns in the pertainant rows, that
would be fine.
:
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.
:
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?
:
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?
:
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
:
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
:
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.
