VBA - Range Clear Contents And Copy And Paste From Different Worksheet Not Working

[DR RENO]

Still rather new with VBA, but this problem as me feeling like I've regressed to day 1. I have a worksheet (Historicals) that I'm clearing contents from so that I can copy another worksheet and 'Paste Special' back on 'Hypotheticals' sheet. I'm trying to accomplish this via a range, A1:M1036, because I have different formulas in Historicals columns N to U, but when macro runs it pastes over N through U with formulas from other sheet. Tried several different modifications, but end up with the same result. I started to wonder if there was some hidden code that I forgot about and needed to check for, but that is not the case.

If someone could point out this obvious simple fix, I would be eternally grateful. And so would the wall that I keep banging my head against

 Sub CopyPaste_Historicals()

     
   Dim lLR As Long


Application.ScreenUpdating = False


Sheets("Hypotheticals").Cells.ClearContents
Worksheets("Hypotheticals").Range("A1:M10360").Clear




With Sheets("CAR Open Contract")
    lLR = .Range("A1:M" & .Rows.Count).End(xlUp).Row
        .UsedRange.Copy
        
        
End With


With Sheets("Hypotheticals").Range("A1")


    .PasteSpecial _
        Paste:=xlPasteAll, _
        Operation:=xlNone, _
        SkipBlanks:=False, _
        Transpose:=False
End With






Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

 

[footoo]

You are copying the whole of the used range from "Car Open ContracT" (highlighted in red) :

[COLOR=#ff0000]With Sheets("CAR Open Contract")[/COLOR]
    lLR = .Range("A1:M" & .Rows.Count).End(xlUp).Row
[COLOR=#ff0000]        .UsedRange.Copy[/COLOR]

 

[Leith Ross]

Hello DR RENO,

While the macro recorder is useful, it is not the best way to learn VBA. The number one rule for VBA is learn from good examples. Here is your code with corrections added to it. The understanding of objects and how to use them is key to becoming a good VBA programmer.

Sub CopyPaste_Historicals()


    Dim LR      As Long
    Dim dstRng  As Range
    Dim srcRng  As Range
    


        Application.ScreenUpdating = False


        With Worksheets("Hypotheticals")
            Set dstRng = .Range("A1:M10360")
            dstRng.Clear
        End With


        With Sheets("CAR Open Contract")
            Set srcRng = .Range("A1:M1")
            LR = .Cells(Rows.Count, "A").End(xlUp).Row
            If LR < srcRng.Row Then Exit Sub
            Set srcRng = srcRng.Resize(RowSize:=LR - srcRng.Row + 1)
        End With


        srcRng.Copy dstRng.Resize(RowSize:=srcRng.Rows.Count)


        Application.ScreenUpdating = True


End Sub

 

[footoo]

Leith Ross

With Sheets("CAR Open Contract")
    Set srcRng = .Range("A1:M1")
    LR = .Cells(Rows.Count, "A").End(xlUp).Row
[COLOR=#ff0000]    If LR < srcRng.Row Then Exit Sub[/COLOR]
    Set srcRng = srcRng.Resize(RowSize:=LR - srcRng.Row + 1)
End With

The LR cannot be less than srcRng.Row since srcRng.Row is 1

Suggest :

Sub CopyPaste_Historicals()
Sheets("Hypotheticals").Cells.ClearContents
With Sheets("CAR Open Contract")
    .Range("A1:M" & .Cells(Rows.Count, "A").End(xlUp).Row).Copy Sheets("Hypotheticals").[A1]
End With
End Sub

 

[footoo]

Maybe this is sufficient :

Sub CopyPaste_Historicals()
Sheets("CAR Open Contract").[A:M].Copy Sheets("Hypotheticals").[A:M]
End Sub

 

[vcoolio]

Hello Dr. Reno,

I had put this together with some explanations for you a few hours ago but had to go out so I'll post it anyway even though you have enough answers to your issue.

With this part of your code:-

Sheets("Hypotheticals").Cells.ClearContents

Every cell in the worksheet will have its contents cleared.

With this part of your code:-

Worksheets("Hypotheticals").Range("A1:M10360").Clear

You are attempting to clear the Range("A1:M10360") but its contents are no longer there because you have already cleared it with the line of code above.
Using "Clear" alone will remove absolutely everything from a cell or range or sheet (values, formats, formulae etc.).

With this part of your code:-

With Sheets("CAR Open Contract")
    lLR = .Range("A1:M" & .Rows.Count).End(xlUp).Row
        .UsedRange.Copy

You are copying the entire "UsedRange" viz. your entire data set. Hence, you don't need the 1LR declaration as it won't mean a thing in this case.
Thus, as you have copied the entire data set, this data set will paste over everything in the "Hypotheticals" sheet not just over the "A:M" range as you would like.

So, perhaps the code modified as follows will work for you:-

Sub CopyPaste_Historicals()

        Dim lr As Long
         Dim ws As Worksheet: Set ws = Sheets("Hypotheticals") '----> Check  the sheet name spelling to match exactly with the tab name.
         Dim sh As Worksheet: Set sh = Sheets("Car Open Contract") '---->  Check the sheet name spelling to match exactly with the tab name.
        lr = sh.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

        sh.Range("A1:M" & lr).Copy
        ws.[A1].PasteSpecial xlValues

Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

As you are pasting to cell A1 each time in the "Hypotheticals" sheet, you won't need to clear contents of the Range("A:M"). It will be overwritten.

I hope that this helps.

Cheerio,
vcoolio.

 

[DR RENO]

Hello Dr. Reno,

I had put this together with some explanations for you a few hours ago but had to go out so I'll post it anyway even though you have enough answers to your issue.

With this part of your code:-

Sheets("Hypotheticals").Cells.ClearContents

Every cell in the worksheet will have its contents cleared.

With this part of your code:-

Worksheets("Hypotheticals").Range("A1:M10360").Clear

You are attempting to clear the Range("A1:M10360") but its contents are no longer there because you have already cleared it with the line of code above.
Using "Clear" alone will remove absolutely everything from a cell or range or sheet (values, formats, formulae etc.).

With this part of your code:-

With Sheets("CAR Open Contract")
    lLR = .Range("A1:M" & .Rows.Count).End(xlUp).Row
        .UsedRange.Copy

You are copying the entire "UsedRange" viz. your entire data set. Hence, you don't need the 1LR declaration as it won't mean a thing in this case.
Thus, as you have copied the entire data set, this data set will paste over everything in the "Hypotheticals" sheet not just over the "A:M" range as you would like.

So, perhaps the code modified as follows will work for you:-

Sub CopyPaste_Historicals()

        Dim lr As Long
         Dim ws As Worksheet: Set ws = Sheets("Hypotheticals") '----> Check  the sheet name spelling to match exactly with the tab name.
         Dim sh As Worksheet: Set sh = Sheets("Car Open Contract") '---->  Check the sheet name spelling to match exactly with the tab name.
        lr = sh.Range("A" & Rows.Count).End(xlUp).Row

Application.ScreenUpdating = False

        sh.Range("A1:M" & lr).Copy
        ws.[A1].PasteSpecial xlValues

Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

As you are pasting to cell A1 each time in the "Hypotheticals" sheet, you won't need to clear contents of the Range("A:M"). It will be overwritten.

I hope that this helps.

Cheerio,
vcoolio.

vcoolio,

Thank you so much for you post. The breakdown explanations helped me to not only notice missing the obvious, but gave me a much better understanding than I had previously of when the 1LR declaration is needed and when it's not. The code you provided worked perfectly. I truly appreciate you taking the time to help me in such detail. Have a great weekend.

footoo and Leith Ross, I appreciate the time you both took to try and come up with my solution. I tried each of your entries and they all were close, but unfortunately they also had a section that wasn't coming in as needed. I was actually trying to see if I could combine what you both provided to solve my issue right before vcoolio sent across his post. This is a great site filled with terrific people like each of you, who are willing to give your time to help people trying to better understand VBA.

 

[footoo]

As you are pasting to cell A1 each time in the "Hypotheticals" sheet, you won't need to clear contents of the Range("A:M"). It will be overwritten.

Only if the data already on "Hypertheticals" consists of fewer rows than the new data.

 

[vcoolio]

@footoo:-

True. But it appears that Dr.Reno has a fixed range. Plus he now has an understanding of how to clear the the old data and use LR.

@ Dr. Reno:-

You're welcome. Glad that we could help.
Thanks for the feed back also.

Cheerio,
vcoolio.

 

[footoo]

DR RENO

I tried each of your entries and they all were close, but unfortunately they also had a section that wasn't coming in as needed

Would be interested to know what didn't meet what is required.
It seems to me that the macro per post # 5 should do what you want.