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

 

[DR RENO]

DR RENO


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.

Runtime Error 1004
Cannot complete operation: A table cannot overlap with a Pivot Table report, query results, a table, merged cell, or an XML mapping.

Both of these sheets contain essentially the same table. One is current day system data, and the destination sheet takes that to create a prior day. The current day will be populated via pull from the db which allows me to get a day on day change.

FYI, I tried to incorporate your post to vcoolio, which is definitely a valid concern because the Hypothetical sheet could have more rows, but I'm getting a 'RUNTIME ERROR 424 OBJECT REQUIRED' which is what I'm currently trying to figure out. By the way this macro is being ran from a UserForm in case that could issues. Here is my current code:

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 & lr].PasteSpecial xlValues


Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

 

[footoo]

Can't comment further without knowing the structure and content of your sheets.

Re the "current code", replace this :

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

With this :

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

 

[DR RENO]

Can't comment further without knowing the structure and content of your sheets.

Re the "current code", replace this :

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

With this :

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

Your replacement code gave me a runtime error of 1004.

My code originally worked fine, but since it is pasting the entire sheet to Hypotheticals, I needed to shorten the range because I put in modified some formulas in Hypotheticals. So what was happening is the copy paste ends up overwriting my modified formula. Since my origin sheet (Car open contract) columns A:M are system generated values and N:V are columns that I have input formulas, my thought was if I shorten the range to A1:M it would leave the columns in Hypothetical sheet with formulas in tact. It seems like it's really close and vcoolio's formula essentially does just that. The only thing though is as you noted, if Car Open Contract sheet has less rows than Hypothetical sheet, I will not have a 1:1 relationship between sheets.

 

[footoo]

Your replacement code gave me a runtime error of 1004.

Can't see why that happens. It is the same as vcoolio's code (which you say works), except that it pastes all (like your original code) instead of just values.

Regarding the number of rows, can you not just add a line at the beginning of the macro to clear the contents of "Hypotheticals" column A:M ?