0

I have 3 worksheets, Inventory Count, Issuance Count and Stock. They look something like this: enter image description here enter image description here enter image description here

So, I am trying to concatenate the Sample numbers in Inventory and Issuance count, and then trying to apply advanced filter to get unique sample numbers in the stock sheet. Here is my code:

Sub Stock1()
Dim wbData As Range
Dim wbData2 As Range
Dim unionData As Range
Dim lastrow As Long
Dim lastrow2 As Long
lastrow = WorksheetFunction.CountA(ThisWorkbook.Worksheets("Issuance").Range("B:B")) + 2
lastrow2 = WorksheetFunction.CountA(ThisWorkbook.Worksheets("Inventory").Range("B:B")) + 2
Set wbData = ThisWorkbook.Worksheets("Inventory").Range("B3:B" & lastrow)
Set wbData2 = ThisWorkbook.Worksheets("Issuance").Range("B3:B" & lastrow2)

Set unionData = Union(wbData, wbData2)

Set wbExtract = ThisWorkbook.Worksheets("Stock").Range("B1")

unionData.AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=wbExtract, Unique:=True

'Dim lastrow3 As Long
'lastrow3 = WorksheetFunction.CountA(ThisWorkbook.Worksheets("Stock").Range("B:B"))

End Sub

But this is giving me the error in the union line and it says: "Method union of object Global failed error".

Please help, if there is a better other way to achieve the task, I am open to do that as well. Thanks in advance

Improve this question
6
  • 2
    You can't combine ranges from different sheets into one. However, take a look at this thread. stackoverflow.com/questions/25801941/… Commented May 27, 2020 at 7:18
  • I seem to understand the problem now, but I cant seem to be able to use the solution you mentioned to my case Commented May 27, 2020 at 7:50
  • One: You need a list from which to filter unique sample IDs. Two: You can't do it with Application.Union. Three. Which way do you want to try now? If you don't have a better idea, copy both ranges onto one sheet and filter the result. The problem you present to us isn't one of writing code but one of which target to set for the writing of code. Commented May 27, 2020 at 8:28
  • So can I create a hidden sheet, copy data from both sheets onto there, and then apply the advanced filter? I need to figure out how to append one data next to another Commented May 27, 2020 at 8:39
  • Use code like 'WsData.Copy Destination:=Worksheets("Sheet2").Cells(1, "A")` Then assign the second range WsData and With Worksheet("Sheet2"): WsData.Copy Destination:= .Cells(.Rows.Count, "A").End(xlUp).Offset(1): End With and now both ranges are joined in column A of Sheet2 underneath each other. Commented May 27, 2020 at 9:30
Related questions
1
Method range of object _global failed
1
VBA - preventing Runtime Error 1004 - Method 'Union' error when using macro multiple times
1
Method 'Union' of object '_Global' failed on cells that are on the same sheet
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.