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, Jenny.
Good job on the debugging :P

And good try suggesting the change :P
and it's not - 2 because variables stored in the beginnering for LRbal1 and LRbal2 are not changing. This means that LRbal1 and LRbal2, despite they've been coded to reference to the last cell of the worksheet, are not changing when the worksheet has changed. I think it'll be more understandable if I provided an example:

Dim a As Long, x As Long
x = 3 'x value is 3
a = x 'a value is same as x value so a = 3
x = 4 'x = 4 but a value doesn't change so a = 3

So as you can see even tho the 'a' references to 'x' when 'x' changes, 'a' does not update. If you want it to update, you would have to put 'a = x' again after x has been updated.

Anyways, about the code..
Sadly, I expected the code to crash at rewriting the formula code but I'm glad to hear that all the copying is working ok. I'll try to figure it out by asking a specific question on this board. Then I'll post a working code. (I am HORRIBLE with formulas...)

 

[kpark91]

Sorry for the double post but does sum have to be shown through formula?
Can't it just run in the background and just update the cell with values?

I know there probably is a Q&A program so you may not be able to deal with cells directly through VBA cuz then you would have be Q&A the code.

 

[zookeepertx]

Hi, Jenny.
Good job on the debugging :P

Thanks! I was pretty pleased! (of course, I'm really easy to amuse, too, so.... )

And good try suggesting the change :P
and it's not - 2 because variables stored in the beginnering for LRbal1 and LRbal2 are not changing. This means that LRbal1 and LRbal2, despite they've been coded to reference to the last cell of the worksheet, are not changing when the worksheet has changed. I think it'll be more understandable if I provided an example:

Dim a As Long, x As Long
x = 3 'x value is 3
a = x 'a value is same as x value so a = 3
x = 4 'x = 4 but a value doesn't change so a = 3

So as you can see even tho the 'a' references to 'x' when 'x' changes, 'a' does not update. If you want it to update, you would have to put 'a = x' again after x has been updated.

Oh! Good to know! I had no clue. And thanks for the visual aid; I really do pick up on things a LOT quicker that way!

Anyways, about the code..
Sadly, I expected the code to crash at rewriting the formula code but I'm glad to hear that all the copying is working ok. I'll try to figure it out by asking a specific question on this board. Then I'll post a working code. (I am HORRIBLE with formulas...)

LOL- I doubt there's much to do with Excel that you're truly horrible at. I'm pretty good with straightforward formulas - like just putting them directly into a spreadsheet - but not something "abstract" like this!

Sorry for the double post but does sum have to be shown through formula?
Can't it just run in the background and just update the cell with values?

I know there probably is a Q&A program so you may not be able to deal with cells directly through VBA cuz then you would have be Q&A the code.

No, as long as the sum is correctly totalling the whole column, I don't care if it's a formula there or not. I have no idea what "run in the background" means, though. Also - Q&A program??

Jenny

 

[kpark91]

Hi, Jenny.

Try this code. It puts in the formula in the sum cell :P

Sub kpark91July132011()
    Dim LRdata1&, LRdata2&, LRbal&, j&, dataWS As Worksheet, balWS As Worksheet, col$
    Set dataWS = ThisWorkbook.Worksheets("ALL")
    Set balWS = ThisWorkbook.Worksheets("balWS")
    LRdata1 = dataWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of dataWS
    LRbal1 = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of balWS data
 
    'Copy last 4 rows of dataWS and paste it to the end of balWS
    dataWS.Range("A" & LRdata1 - 3 & ":AH" & LRdata1).Copy balWS.Range("A" & LRbal1 + 3)
    For j = Range("D1").Column To Range("Q1").Column
        col$ = getColLetter(j)
        balWS.Cells(LRbal1 + 2, j).Formula = "=SUM(" & col & "$8:" & col & LRbal1
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        col$ = getColLetter(j)
        balWS.Cells(LRbal2 + 2, j).Formula = "=SUM(" & col & "8:" & col & LRbal2
    Next j
End Sub
Function getColLetter(colNum As Integer)
    getColLetter = Left(Cells(1, colNum).Address(1, 0), InStr(1, Cells(1, colNum).Address(1, 0), "$") - 1)
End Function

I hope this works or I will make the program automatically calculate the sum and put the values inside the cell.

and as for the Q & A program (Quality Assurance), I meant to ask do you need to assure the quality of a program by looking at its code? which is of course a hassle like any other Q & A

 

[zookeepertx]

Hmmm, that gives an error msg: "Compile error: ByRef argument type mismatch" and has selected the letter j in the line "col$ = getColLetter(j)

It might just be easier, now that the total rows are pasting correctly, to update the formulas manually. I don't want to take up ALL of your time, especially after you've made this whole magnificent macro. (Unless you're like me & just HATE to let go of a puzzle till it's solved, LOL)

Thank you!

Jenny

 

[kpark91]

Hi Jenny. I'm in fact a person who will hang on until a problem is solved as long as it's in my interest and in my field;
I remember trying to solve a math problem which took me 6 months to solve.
During that time, I was going crazy haha.

Anyways try this,

Sub kpark91July132011()
    Dim LRdata1&, LRdata2&, LRbal&, j%, dataWS As Worksheet, balWS As Worksheet, col$
    Set dataWS = ThisWorkbook.Worksheets("ALL")
    Set balWS = ThisWorkbook.Worksheets("balWS")
    LRdata1 = dataWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of dataWS
    LRbal1 = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of balWS data
 
    'Copy last 4 rows of dataWS and paste it to the end of balWS
    dataWS.Range("A" & LRdata1 - 3 & ":AH" & LRdata1).Copy balWS.Range("A" & LRbal1 + 3)
    For j = Range("D1").Column To Range("Q1").Column
        col = getColLetter(j)
        balWS.Cells(LRbal1 + 2, j).Formula = "=SUM(" & col & "$8:" & col & LRbal1
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        col = getColLetter(j)
        balWS.Cells(LRbal2 + 2, j).Formula = "=SUM(" & col & "8:" & col & LRbal2
    Next j
End Sub
Function getColLetter(colNum As Integer)
    getColLetter = Left(Cells(1, colNum).Address(1, 0), InStr(1, Cells(1, colNum).Address(1, 0), "$") - 1)
End Function

The error was saying that the function's parameter datatype and the variable's datatype I'm inputting are different so there is a compile error. :P
I'm making all kinds of mistakes haha.
It's not taking too long for me so don't worry about it.
It's literally taking me maximum of 5 mins to code each question.

 

[zookeepertx]

Oh, so you're as bad as I am. Maybe even worse... 6 months?? Wow!

Okay, tried that & got the "Runtime error '1004': Application-defined or object-defined error" on the line:

balWS.Cells(LRbal1 + 2, j).Formula = "=SUM(" & col & "$8:" & col & LRbal1

Hey, I had an idea! (It hurt, too! ) I went to the dataWS & changed the sum formulas to read =SUM(D$8:D88), =SUM(E$8:E88), etc. If I then remove some coding, leaving only

Sub kpark91July132011()
    Dim LRdata1&, LRdata2&, LRbal&, j&, dataWS As Worksheet, balWS As Worksheet
    Set dataWS = ThisWorkbook.Worksheets("ALL")
    Set balWS = ThisWorkbook.Worksheets("balWS")
    LRdata1 = dataWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of dataWS
    LRbal1 = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of balWS data
 
    'Copy last 4 rows of dataWS and paste it to the end of balWS
    'I'm unsure if it pastes the formats/cell colors as well
    dataWS.Range("A" & LRdata1 - 3 & ":AH" & LRdata1).Copy balWS.Range("A" & LRbal1 + 3)
End Sub

then the sum formula works correctly when it pastes on the balWS !

I've been playing on a shorter, test version of the workbook, but then, I decided to try it on a copy of a more real-length one. But when I tried that, it said "Run-time error '9': Subscript out of range" highlighting

Set dataWS = This Workbook.Worksheets("ALL")

Speaking of making us crazy...

Jenny

 

[kpark91]

Hm I wonder why it worked xD

Anyways the reason why you're getting an error is because you left a space between "This" and "Workbook". They are one word.

Ok. Forget the formulas here is a background calculation which will be up in a minute.

 

[kpark91]

Try

Sub kpark91July132011()
    Application.ScreenUpdating = False
    Dim LRdata1&, LRdata2&, LRbal&, j&, dataWS As Worksheet, balWS As Worksheet, col$, i&, sum#
    Set dataWS = ThisWorkbook.Worksheets("ALL")
    Set balWS = ThisWorkbook.Worksheets("balWS")
    LRdata1 = dataWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of dataWS
    LRbal1 = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of balWS data
 
    'Copy last 4 rows of dataWS and paste it to the end of balWS
    dataWS.Range("A" & LRdata1 - 3 & ":AH" & LRdata1).Copy balWS.Range("A" & LRbal1 + 3)
    
    For j = Range("D1").Column To Range("Q1").Column
        sum = 0
        For i = 8 To LRbal1
            sum = sum + balWS.Cells(i, j).Value
        Next i
        balWS.Cells(LRbal1 + 2, j).Value = sum
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        sum = 0
        For i = 8 To LRbal2
            sum = sum + balWS.Cells(i, j).Value
        Next i
        balWS.Cells(LRbal2 + 2, j).Value = sum
    Next j
    Application.ScreenUpdating = True
End Sub

 

[zookeepertx]

Hmmm, no, that gives me the same "Runtime error '9':" at line

Set dataWS = ThisWorkbook.Worksheets("ALL")

Do you not think it would work to have the dataWS sum formula be =SUM(D$8:D88), so the "$" should always make it start at row 8? Or could that be a problem down the road?

And, in case you're not ready to yell at me yet , 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?

Thank you!

Jenny