Need to copy data from an inconsistent row to another sheet

[zookeepertx]

I got GREAT macro assistance here a month or so ago from KPark & it’s working terrific! (Saved me about a day and a half last week on month-end work.) I’m wondering if someone can tell me how to add one more thing to it, that I forgot about before.

This is for a report comparing the previous month’s data with this month’s data. Both sides get shored up so that they end up with the same number of rows, by inserting a row on one side if there’s not already one to match that row on the other side. What the macro does is to create 2 new, temporary worksheets, then put all of the previous month’s data on the first one (newWS1) & all of this month’s data on the second one(newWS2). Then it loops through, comparing the 2 sheets & when it finds that one of them has a row with no match on the other, it inserts a new row that is blank except for the file # and the dept #. After it gets both sheets even, it creates another new worksheet (balWS), copies all the header rows from the original sheet to the balWS and copies the information from newWS1 to balWS below the headers, starting in column A and from newWS2 to balWS starting in column R. Then it deletes the 2 temporary worksheets.

What I’d like to have happen next is to copy the last several rows from the original worksheet (containing formulas totaling the columns, along with some other information) & paste it below the data on the balWS. My problem is that all of this data varies in length from one month to the next, so I never know what row# the totals are going to be on or what row# they need to be pasted on. How do I tell it to copy the rows that start after the end of the original data & how do I tell it to paste those rows after the end of the balWS data, however long each of those worksheets are? Also, the Total formulas need to change to include all rows in their column from row 7 to the last row.

Sorry if I’ve been really confusing; I’m notorious for that, LOL! Thanks in advance for any help!

 

[kpark91]

Hi, putting $ in front of 8 would be a good idea.
I can't think of screwing up unless you insert rows before row 8. Even then, I'm not too sure if excel will adjust itself.

is there any way this process could be tagged on at the end of the original macro from last month, so it would happen with that process?

LOL I knew this might happen so I made the code not interfering as much as possible xD

all you have to do is take out the
Dimming of balWS and dataWS and setting them in the code.
and add the code today at the end of the original code.

If you think that's annoying to do, just put

Call kpark91July132011

at the end of the original code. It just means it will run the sub called kpark91July132011

Edit: Lastly,

Set dataWS = ThisWorkbook.Worksheets("ALL")</PRE>

This line is erroring because you don't have a worksheet called "ALL" in your workbook. Check again if it is still named ALL.

 

[zookeepertx]

Hi, putting $ in front of 8 would be a good idea.
I can't think of screwing up unless you insert rows before row 8.

Nope, I shouldn't be inserting any rows on that tab

LOL I knew this might happen so I made the code not interfering as much as possible xD

Figured you were probably already ahead of me on that, LOL!

all you have to do is take out the
Dimming of balWS and dataWS and setting them in the code.
and add the code today at the end of the original code.

That would be a perfect world!! But I just tried & it said "Compile error: Invalid qualifier" at dataWS in line

LRdata1 = dataWS.Range("D" & Rows.Count).End(xlUp).Row

If you think that's annoying to do, just put

Call kpark91July132011

at the end of the original code. It just means it will run the sub called kpark91July132011

So, I did that & it stopped just before the second sub, because the balanced sheet created by the macro wasn't named balWS; it was just named "Sheet 1" (or 2... I don't remember). So, I changed the name & then it finished.

This line is erroring because you don't have a worksheet called "ALL" in your workbook. Check again if it is still named ALL.

Yeah, there's a sheet called ALL; that's why it was confusing. I THOUGHT it should be okay.

Jenny

 

[kpark91]

Sorry! xD My post wasn't clear enough for merging the two subs.

There were two ways of achieving "merge between subs"
1) Copy + paste the code into original, deleting the 'set commands and dim commands for worksheets'
2) Just call the second sub without any changes made to the second sub. So, the second sub would set the worksheets as well, which would be a hassle later on if you want to add anything more.

 

[zookeepertx]

OMG- it's PERFECT!! It works beautifully! I've tried it on copies of the workbook from 3 different months & it WORKS!

Yesterday, I was doing the merge just like you indicated this morning, but couldn't figure out the problem. But a fresh look today helped me out: When I looked back up at the beginning of the original macro I found these lines

    LR1 = Worksheets(dataWS).Range("A" & Rows.Count).End(xlUp).Row
    LR2 = Worksheets(dataWS).Range("R" & Rows.Count).End(xlUp).Row

This made me wonder if the new lines didn't need the "Worksheets()" around the "dataWS" so I tried it & it worked! That line is now

    LRdata1 = Worksheets(dataWS).Range("D" & Rows.Count).End(xlUp).Row

I am just SO happy, I'm gonna have to go lay down now, LOL! (But first, I have to find somebody to come watch this macro run a couple of times )

You are a ROCK STAR!! You could ask anyone around here; they're all just amazed with this. They thought I was all impressive, but I told them I didn't make the macro, YOU did; I just tweaked it a little.

I just have to incorporate the new changes on the macros I'd adapted for the other 2 reports, then it'll be a perfect world.

Thank you, THANK YOU!! I can't say it enough! (You guys seriously do need a "happy dance" emoticon for people like me, LOLOL!)

Jenny