VBA overwrite table in sheet1 with table in sheet2

[RandomUserCode]

I have a schedule in sheet1 and a schedule in sheet2. I want to "subtract" the table range in sheet2 with the table range in sheet1. Sheet2 is the "skeleton" of the schedule, and the determined values in specific cells. Sheet1 is the "skeleton table", just with cells added afterwards. So i want to reset the table in sheet1, to its "skeleton", which is seen in sheet2.

Made this code, but cant make it work:

Function RangeMinus(r1 As Range, r2 As Range) As Range

Dim rCell As Range

For Each rCell In r1
    If Intersect(rCell, r2) Is Nothing Then
        If RangeMinus Is Nothing Then
            Set RangeMinus = rCell
        Else
            Set RangeMinus = Application.Union(RangeMinus, rCell)
        End If
    End If
Next rCell

End Function

Sub test()

    Dim rMinus As Range

    Set rMinus = RangeMinus(Sheets(2).Range("B2:F10"), Sheets(1).Range("B2:F10"))
    MsgBox rMinus.Address(0, 0)

End Sub

The schedule tables looks like this:

 

[RoryA]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA overwrite table in sheet1 with table in sheet2

I have a schedule in sheet1 and a schedule in sheet2. I want to "subtract" the table range in sheet2 with the table range in sheet1. Sheet2 is the "skeleton" of the schedule, an...

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RandomUserCode]

A gentle reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

VBA overwrite table in sheet1 with table in sheet2

I have a schedule in sheet1 and a schedule in sheet2. I want to "subtract" the table range in sheet2 with the table range in sheet1. Sheet2 is the "skeleton" of the schedule, an...

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

My bad, will just paste the link if i cross-post a question some other time, TY

 

[RandomUserCode]

I have a schedule in sheet1 and a schedule in sheet2. I want to "subtract" the table range in sheet2 with the table range in sheet1. Sheet2 is the "skeleton" of the schedule, and the determined values in specific cells. Sheet1 is the "skeleton table", just with cells added afterwards. So i want to reset the table in sheet1, to its "skeleton", which is seen in sheet2.

Made this code, but cant make it work:

Function RangeMinus(r1 As Range, r2 As Range) As Range

Dim rCell As Range

For Each rCell In r1
    If Intersect(rCell, r2) Is Nothing Then
        If RangeMinus Is Nothing Then
            Set RangeMinus = rCell
        Else
            Set RangeMinus = Application.Union(RangeMinus, rCell)
        End If
    End If
Next rCell

End Function

Sub test()

    Dim rMinus As Range

    Set rMinus = RangeMinus(Sheets(2).Range("B2:F10"), Sheets(1).Range("B2:F10"))
    MsgBox rMinus.Address(0, 0)

End Sub

The schedule tables looks like this:

Error message can be seen in this picture:

 

[RoryA]

If r1 and r2 are on different sheets, why are you using Intersect (which won't work) when they can't possibly overlap?

 

[RandomUserCode]

If r1 and r2 are on different sheets, why are you using Intersect (which won't work) when they can't possibly overlap?

I tried in one sheet, but changed it to two different sheets. Dont know how to do it in two different sheets

 

[RoryA]

I don't understand how this code relates in any way to the described situation. It sounds like all you need to do is copy the skeleton table over the table on sheet1.

 

[RandomUserCode]

I don't understand how this code relates in any way to the described situation. It sounds like all you need to do is copy the skeleton table over the table on sheet1.

Yeah that is about right. I want to copy sheet2 and overwrite sheet1 (in the same range) on a specific time/day. Can it be made easier than the code prevented?

 

[RoryA]

Yes:

Sheets(2).Range("B2:F10") Sheets(1).Range("B2")