VBA Help

[Jarodjp42]

I have a task that I have to repeat on a weekly basis and it is very time-consuming. I a newbie when it comes to VBA so any help is gladly appreciated! I am trying to select a range of cells and special paste values. I have been using the record macros button to get the code I need; however, when I add new clients to the list the code is not dynamic.

My current data range is A10:AX586. I need a code that will allow me to select that range copy and paste special values only. Here is the code I am currently using.

Range("A10").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

The problem I am running into is that the last 3 rows in my data set are totals and need to stay formulas. How can I rewrite this code to select all the data dynamically but exclude the last 3 rows? I have tried to work offset into my code but I am not sure how to write it. Any help is greatly appreciated. Thanks in advance!!

 

[Joe4]

Does this do what you need?

Sub MyCopy()

    Dim lr As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Copy over range with values, except for last 3 rows
    Range("A10:AX" & lr - 3).Value = Range("A10:AX" & lr - 3).Value
    
End Sub

 

[Trevor G]

Try this:

Sub RegionNotLast3Rows()
With Selection.CurrentRegion
.Cells(10, 1).Resize(.Rows.Count - 12, .Columns.Count).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

 

[Jarodjp42]

Does this do what you need?

Sub MyCopy()

    Dim lr As Long
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Copy over range with values, except for last 3 rows
    Range("A10:AX" & lr - 3).Value = Range("A10:AX" & lr - 3).Value
   
End Sub

@Joe4 thank you for reply to my post. When I tried using the code you provided I kept getting an error message. I believe It was happening when I tried typing in .End as it was recognizing it as a command. Can you please let me know how to fix this. Thanks!

 

[Joe4]

@Joe4 thank you for reply to my post. When I tried using the code you provided I kept getting an error message. I believe It was happening when I tried typing in .End as it was recognizing it as a command. Can you please let me know how to fix this. Thanks!

Don't try typing the code, use Copy and Paste instead. That way it will ensure you don't make any typos.
One thing people often mess up on is this "xlUp" part. Note that is a lower case "L" after the "x", not the number "1".

 

[Jarodjp42]

@Joe4 thank you for reply to my post. When I tried using the code you provided I kept getting an error message. I believe It was happening when I tried typing in .End as it was recognizing it as a command. Can you please let me know how to fix this. Thanks!

Sorry @Joe4, I am not sure what was happening the first time. But I was able to get it to work the way I want. Thanks so much!

 

[Joe4]

You are welcome.

 

[Jarodjp42]

@Joe4 when I run the macro you provided me in my spreadsheet I am getting an error message on this line.

Range("A10:AX" & lr - 3).Values = Range("A10:AX" & lr - 3).Value

It is telling me, "the object doesn't support this property or method." I was wondering if you could share some insight into why I am getting this.

Does it have anything to do with me having data above row 10 and the lr is causing the issue? I have data in rows 1-6, 8 & 9. However, I am only wanting the count range to look at data from A10:A586 excluding the last 3 rows.

Btw, I am trying to get my code to go sheet by sheet to convert formulas to values. This is the code for the first sheet I am using. Then I use that same code and replace the sheet name.

Dim Lr As Long

'Selects Specific Sheet
Sheets("SVCS Combined").Select

' Find last row in column A with data
Lr = Cells(Rows.Count, "A").End(xlUp).Row

' Copy over range with Values, except for last 3 rows
Range("A10:AX" & Lr - 3).Values = Range("A10:AX" & Lr - 3).Value

Any help would be much appreciated! Thanks

 

[Joe4]

Btw, I am trying to get my code to go sheet by sheet to convert formulas to values

That is exactly what this line is supposed to do:
Range("A10:AX" & lr - 3).Values = Range("A10:AX" & lr - 3).Value

Do you have any merged cells or protected cells in the range?
What is the exact error number & message that is being returned?

Tell me what the message box returns when you run this:

Sub MyCopy()

    Dim lr As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    MsgBox "Last Row is: " & lr    

'   Copy over range with values, except for last 3 rows
    Range("A10:AX" & lr - 3).Value = Range("A10:AX" & lr - 3).Value
    
End Sub

 

[Jarodjp42]

@Joe4 I think I figured out what I was doing wrong. The message box says 600 btw.

But I realized in my code I was using .Values instead of .Value

After making that change, the code works perfectly!

Would having merged cells make any difference in this scenario? It doesn't seem like it because I do have merged cells in A1 & A9. Just thought I would ask so if it does I can learn about it.

Thanks so much for the help!