VBA - Ignore last row cannot solve it.

[Togora]

Hi all,

The code below is working just as I want it to apart for one issue which is it also copies the last row (Totals row), I do not want that.

The code was found on the net and adjusted to fit my needs. From item 5 onwards it is a copy of items 1 to 4.

I have researched how to fix this but everything I have tried either breaks the code or does nothing. I am sure it is a two second tweak but my near non-existent VBA code knowledge is stopping me from finding it

Any help would be appreciated

Thanks in advance.

Sub Clear_Existing_Data_Before_Paste()


Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long


  Set wsCopy = Workbooks("Alternative_Mess_Accounts.xlsx").Worksheets("Week_1_Stock_Sheet")
  Set wsDest = Workbooks("CopyTestBook.xlsx").Worksheets("Sheet1")
  
    'Code below is to Copy Column A:D from current worksheet to a new workbook A:D
    
    '1. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
      
    '2. Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    
    '3. Clear contents of existing data range
    wsDest.Range("A5:D" & lDestLastRow).ClearContents


    '4. Copy & Paste Data - last row for original code
    wsCopy.Range("A5:D" & lCopyLastRow).Copy _
      wsDest.Range("A5")
      
    'Code below is to Copy Column AI to J
      
    '5. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
      
    '6. Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
    
    '7. Clear contents of existing data range
    wsDest.Range("J5:J" & lDestLastRow).ClearContents


    '8. Copy & Paste Data - last row for original code
    wsCopy.Range("AI5:AI" & lCopyLastRow).Copy _
      wsDest.Range("J5")


End Sub

 

[MARK858]

Maybe

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row - 1

 

[Togora]

You genius! That worked exactly as I needed it to.

I made the same change to the inner brackets just after Count, because the advice online suggested that was a solution, of course it wasn't. So I was in the correct area.

Again thanks for that and have a great day.

 

[MARK858]

You're welcome taking 1 off Rows.Count would just mean it would start to look up from the 2nd to last cell in the column.
Where it would work is if you had data in row 1 and then used

ActiveSheet.UsedRange.Rows.Count - 1

.

 

[Togora]

Thanks for that.

Can I ask a subsidiary question?

The code above was intended to copy across data at the end of the month but each month has a different number of weeks. So in order to overcome this and work within my very limited abilities, my intention was to add a button to each of the sheets. This would permit me to know, with confidence, that I could get the required data transferred into a new workbook.

I was wondering. Can this code be amended to work via one button and check column A in each of the sheets (five in total) to see which is the last week, it would also need to ignore the total row at the bottom of the column.

Each of the sheets have the same tab format which is Week_1. Week_2, etc up to five but I would want to hard code the sheets as sheet1, sheet3, sheet5, sheet7, sheet9, there is a second sheet for each week hence the odd numbering also if tab names are changed it would not stop things working.

Is this possible with a few tweaks? If not then that's fine and I will just go with my first idea.

Thanks in advance.

 

[MARK858]

odd numbering also if tab names are changed it would not stop things working.

I am afraid that you can't do that with the sheets tab name, you can with the sheets codename (the name outside the brackets when you look up the sheet in the properties window).
There is a but though, it is relatively easy to do if the code is in the Alternative_Mess_Accounts.xlsx workbook (which it can't be as it is a xlsx) but a pain if it isn't (enough of a pain that I'm not coding for it today).
The alternative is to use the sheets index but you need to be certain that the sheets don't move.

So we need to know where the code is and the codenames or Index numbers (the number of position the sheets are in counting from the left).

 

[Togora]

Hi Mark858,

Thanks for getting back to me on this.

The workbook I used above is only for testing out code samples prior to introducing it into another test workbook, This second workbook contains all the code thus far with most of the sheets expected to be in the final version but again it is designed to do more testing. Once I am happy with how page and code works it is transferred into the final workbook. Long winded yes but that's what floats my boat.

Back to your reply. In short I don't quite know exactly where each sheet will be. Well, I sort of do but as with everything I do this can change so don't want to get your help this early. But it is great to see what I asked is possible.

But if it is OK I will leave this for now until nearer the time.

Thanks again,