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]

Hello, zookeeper.
This is just a partial solution, not a full solution yet. I'm just seeing if it works.

Sub kpark91July132011()
    Dim LRdata&, LRbal&, j&
    LRdata = dataWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of dataWS
    LRbal = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row of data of balWS
    
    '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("D" & LRdata - 3 & ":AH" & LRdata).Copy balWS.Range("D" & LRbal)
    For j = Range("D1").Column To Range("Q1").Column
        balWS.Cells(LRbal + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal, j)
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        balWS.Cells(LRbal + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal, j)
    Next j
End Sub

If it doesn't work, please tell me what error it is and where it errrs at by pressing debug button.

Lastly, you'll need to specify balWS and dataWS in the code if you're using the sub by itself.

 

[zookeepertx]

Good morning! I went to the practice spreadsheet that I’m using to test stuff on & tried this a couple of ways.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
First I tried running just the new code: I changed the names of the worksheets to “dataWS” and “balWS” to (hopefully) match the coding, but got a “Run-time error 424: Object Required” at the line “LRdata = dataWS.Range("D" & Rows.Count).End(xlUp).Row” . Then, just because I wasn’t sure what I was doing, I changed the worksheet names to “LRdata” and “LRbal” and tried it again, but got the same error. <o></o>
<o></o>
So, then I pasted the new code into the original macro, so it should happen right after the macro creates the balWS and puts all the data onto it. I put the dataWS name back to “ALL” so the original macro would recognize it & then tried running the macro. This gave me a “Compile error: Invalid qualifier” on “dataWS” in the line LRdata = dataWS.Range("D" & Rows.Count).End(xlUp).Row .<o></o>
<o></o>
I got to thinking (I know… scary, right? ): What if, on the ALL worksheet, I just put a word – maybe Total or Sub-total – in the first cell of the row I had bolded before. Would that make it easier to reference the row just above that to start the range to copy?<o></o>

Jenny

 

[kpark91]

Good morning, Jenny.
Thanks for the suggestion.
Although it may be easier to code, it will definitely be ALOT slower than our current method.

The reason, the code I've given you isn't working, is because you have not referenced what dataWS and balWS are!
I've specified that you should declare the variables yourself but it's ok :P

here is the whole code then
Run it AFTER the code I've given you in a previous thread:

You should change "dataWSName" and "balWSName" to the names of your worksheet which has your data and balanced data respectively in the code.

Sub kpark91July132011()
    Dim LRdata&, LRbal&, j&, dataWS As Worksheet, balWS As Worksshet
    LRdata = dataWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of dataWS
    LRbal = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row of data of balWS
    Set dataWS = ThisWorkbook.Worksheets("dataWSName")
    Set balWS = ThisWorkbook.Worksheets("balWSName")
    
    '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("D" & LRdata - 3 & ":AH" & LRdata).Copy balWS.Range("D" & LRbal)
    For j = Range("D1").Column To Range("Q1").Column
        balWS.Cells(LRbal + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal, j)
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        balWS.Cells(LRbal + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal, j)
    Next j
End Sub

 

[zookeepertx]

The reason, the code I've given you isn't working, is because you have not referenced what dataWS and balWS are!
I've specified that you should declare the variables yourself but it's ok :P

Oh, duhhh! In your previous post, when you said "Lastly, you'll need to specify balWS and dataWS in the code if you're using the sub by itself." I didn't know what that meant. I thought I could achieve the same effect by changing the worksheet names to dataWS & balWS (thinking that would make them match the coding). I'm just a dummy; obviously, I need someone to lead me around by the hand!

You should change "dataWSName" and "balWSName" to the names of your worksheet which has your data and balanced data respectively in the code.

I just did this & tried again, but got an error "Run-time error 91: Object variable or With block variable not set." on the line

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

Maybe its got to do with the fact that, on the dataWS, the previous month's data ends on a different row than this month's data. They may be offset by a large number of rows, which can (& usually does) end up leaving blank rows on previous month's side between the last data and the rows I'm looking to copy from there.

Thanks!

Jenny

 

[kpark91]

Oh, duhhh! In your previous post, when you said "Lastly, you'll need to specify balWS and dataWS in the code if you're using the sub by itself." I didn't know what that meant. I thought I could achieve the same effect by changing the worksheet names to dataWS & balWS (thinking that would make them match the coding). I'm just a dummy; obviously, I need someone to lead me around by the hand!

You're not a dummy xD Anybody new to VBA or programming in general has to start somewhere!

I just did this & tried again, but got an error "Run-time error 91: Object variable or With block variable not set." on the line

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

That is very weird. Are you sure you replaced "dataWSName" with your worksheet name WITH the double quotation around it?
So, if my worksheet name is Sheet1
I need to replace "dataWSName" with "Sheet1" making the code look like

Set dataWS = ThisWorkbook.Worksheets("Sheet1")

Maybe its got to do with the fact that, on the dataWS, the previous month's data ends on a different row than this month's data. They may be offset by a large number of rows, which can (& usually does) end up leaving blank rows on previous month's side between the last data and the rows I'm looking to copy from there.

Thanks!

Jenny

The code should not and does not care if there's a large offset between the balanced WS's row and data WS's row because I'm using two seperate variables to store those numbers.

 

[zookeepertx]

That is very weird. Are you sure you replaced "dataWSName" with your worksheet name WITH the double quotation around it?
So, if my worksheet name is Sheet1
I need to replace "dataWSName" with "Sheet1" making the code look like

Set dataWS = ThisWorkbook.Worksheets("Sheet1")

Yep, that's what it looked like. After running the original macro, my dataWS was still named ALL and the macro-created sheet was called balWS. So I made the code:

    Set dataWS = ThisWorkbook.Worksheets("ALL")
    Set balWS = ThisWorkbook.Worksheets("balWS")

The code should not and does not care if there's a large offset between the balanced WS's row and data WS's row because I'm using two seperate variables to store those numbers.

Oh, no - I meant that on the dataWS (ALL) columns A-Q are a different length than columns R-AH. I didn't know if that might be causing the macro to be confused.

I've studied this code & am curious about the verbiage in the code. I've tried reading about it, but I can't make sense of it that way. For example: in this line of code -

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

what does Rows.Count do? What does .End(xlUp).Row do?

Thank you for your patience!

Jenny

 

[kpark91]

No wonder it's erroring! I siwtched up the two lines!!

Sub kpark91July132011()
    Dim LRdata&, LRbal&, j&, dataWS As Worksheet, balWS As Worksheet
    Set dataWS = ThisWorkbook.Worksheets("ALL")
    Set balWS = ThisWorkbook.Worksheets("balWS")
    LRdata = dataWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of dataWS
    LRbal = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row of data of balWS
    
    '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("D" & LRdata - 3 & ":AH" & LRdata).Copy balWS.Range("D" & LRbal)
    For j = Range("D1").Column To Range("Q1").Column
        balWS.Cells(LRbal + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal, j)
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        balWS.Cells(LRbal + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal, j)
    Next j
End Sub

Oh, no - I meant that on the dataWS (ALL) columns A-Q are a different length than columns R-AH. I didn't know if that might be causing the macro to be confused.

Oh... That might be a problem then. but I'm kinda in the middle of something right now so I will be able to update the code after I get home.

what does Rows.Count do? What does .End(xlUp).Row do?

dataWS.Range("D" & Rows.Count).End(xlUp).Row returns the row number.
Rows.count -> returns the very last row of the worksheet which is 1048576 in xl2007
End(xlUp) -> range 'selected' is moved up until it is not empty
Row -> return the row of the range

Therefore, together, the code tells the computer to start from the very bottom of the worksheet toward the top and find the very first filled in cell and return its row.
This means that you can find the very last row in a worksheet of any columns.

 

[zookeepertx]

No wonder it's erroring! I siwtched up the two lines!!

Strangely, that makes me feel a little better!

Oh... That might be a problem then. but I'm kinda in the middle of something right now so I will be able to update the code after I get home.

No problem at all. It's not a big hurry right now.

dataWS.Range("D" & Rows.Count).End(xlUp).Row returns the row number.
Rows.count -> returns the very last row of the worksheet which is 1048576 in xl2007
End(xlUp) -> range 'selected' is moved up until it is not empty
Row -> return the row of the range

Therefore, together, the code tells the computer to start from the very bottom of the worksheet toward the top and find the very first filled in cell and return its row.
This means that you can find the very last row in a worksheet of any columns.

OHHH, that makes sense! Too COOL; thanks for the translation!

Jenny

 

[kpark91]

Try

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
    LRdata2 = dataWS.Range("U" & Rows.Count).End(xlUp).Row 'Get last row in column U of dataWS
    LRbal1 = balWS.Range("D" & Rows.Count).End(xlUp).Row 'Get last row in column D of balWS data
    LRbal2 = balWS.Range("U" & Rows.Count).End(xlUp).Row 'Get last row in column U 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 & ":Q" & LRdata1).Copy balWS.Range("A" & LRbal)
    dataWS.Range("R" & LRdata2 - 3 & ":AH" & LRdata2).Copy balWS.Range("R" & LRbal)
    For j = Range("D1").Column To Range("Q1").Column
        balWS.Cells(LRbal1 + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal1, j)
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        balWS.Cells(LRbal2 + 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal2, j)
    Next j
End Sub

 

[zookeepertx]

I think we're closing in on it! I tried that & played with it a bit. It will now paste the 4 rows from the dataWS over to the correct row of the balWS, with the correct cell colors & formatting.

Here's how it looks now:

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)
    For j = Range("D1").Column To Range("Q1").Column
        balWS.Cells(LRbal1 - 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal1 - 4, j)
    Next j
    For j = Range("U1").Column To Range("AH1").Column
        balWS.Cells(LRbal2 - 2, j).Formula = "=SUM(" & balWS.Cells(8, j) & ":" & balWS.Cells(LRbal2, j)
    Next j
End Sub

But when it goes to fix the formulas, it gives a "Runtime error '1004': Application-defined or object-defined error" on the line:

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

When it gives that error, if I drag the little yellow arrow (in the module window) down 1 step to "Next j" & hit F8, it goes back up to the same line of code. F8 again & get the error. So, just for fun, I kept doing that & it eventually moved on to

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

I've studied & studied on it & the only thing I could think was that maybe the "LRbal1 + 2" should be "- 2", but I tried that & get the exact same error.
Thanks!
Jenny