Union Ranges On Two Different Sheets

Mar 3, 2008

I'm trying to union two different ranges that exist on two different sheets and then copy the unique values into a third range with just the unique values. I was going to then use the unique range as my rowsource in a listbox. This is the code I was working with so far but i'm getting a compile error (method range of object failure).

Private Sub UserForm_Initialize()
Dim range4 As Range
Dim range3 As Range
Dim range2 As Range
Dim range1 As Range
Set range1 = Worksheets(1).Range("MyRange")
Set range2 = Worksheets(2).Range("MyRange2")
Application.Union(Range("range1"), Range("range2")).Select
Selection = range3
range3.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=range4, Unique:=True
Me.lstone.RowSource = range4
End Sub

View 9 Replies


ADVERTISEMENT

With For Multiple Ranges (not Using Union)

Dec 29, 2008

Quick question: When using "With" is it possible to specify 2 ranges, or will I need to do 2 different with blocks? The reason I say not using Union is because I need my ranges seperate. I run 1 set of commands on Range 1, and 1 set of commands on both Range 1 and 2.

View 3 Replies View Related

Union Named Ranges

Apr 29, 2009

(Using Office 2003 on XP Pro) I have two named ranges that I want to union into one big named range so that I can use the big named range in a validation table. Unfortunately the big range does not appear on the list of named range so I switched to VBA to try and lick this but really I was hoping a non-VBA solution exists. Exploring possible VBA solutions, here is what I have so far:

View 6 Replies View Related

Selecting Ranges On Different Sheet Using Union

Nov 7, 2013

I am writing a UDF to find the average of two ranges, which start at the StartDate and then go back a certain number of days, that are on another sheet. When I use Union to try and unite my two ranges the resulting range just equals the first range.

VB:
Public Function ModifiedAverage(StartDate As Variant, SheetTenor As String, History As Double)
Dim DateRow, NbCols As Double
DateRow = Application.WorksheetFunction.Match(StartDate, Sheets(SheetTenor).Range("A:A"), 0)
NbCols = Application.WorksheetFunction.CountA(Sheets(SheetTenor).Range("6:6"))

[Code] ....

View 1 Replies View Related

Problem With Union Method: Combining Two Ranges

Oct 7, 2008

I have the following two ranges in the same spreadsheet: A1:A1000 and B1:B1000. Both contain data that I would like to manipulate. Let's assume I would like to add "1" to every cell in the two ranges. This is what I have so far:



Sub Test2()
Dim wb As Workbook
Dim ws1, ws2 As Worksheet
Dim rng1a, rng1b, rng_union As Range
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("testsheet")
Set ws2 = wb.Worksheets("result")
ws1.Select
Set rng1 = Range(Cells(1, 1), Cells(1000, 1))
Set rng2 = Range(Cells(1, 2), Cells(1000, 2))

Set rng_union = Union(rng1, rng2)...................

Function ADDONE(rng)
n = rng.Cells.Count
ReDim rng_add(1 To n)
For i = 1 To n
If rng(i) = "" Then GoTo NextIndex
rng_add(i) = rng(i) + 1.............
End Function
I have to somehow combine the two ranges because the data needs to be manipulated simultaneously. Instead of having the range of results returned to me (see worksheetfunction.transpose), I could have calculated the variance, average etc... of the results, too. I'm using the transpose function to show that the operations in function ADDONE are only conducted for the first range of cells (rng1) that I have joined with the union method. I presume that Union might not be the correct method but I don't know ehich other method to use.

View 9 Replies View Related

Ranges Within Sheets

Apr 27, 2007

I'm trying to operate on sheets using an array. It is not placing the value found with Countif() into the correct cell in J5.

Dim lCount As Long
Dim mysheet As Worksheet
For Each mysheet In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
With mysheet
For lCount = 1 To WorksheetFunction.CountIf(Range("F4:F150"), "1")
Next lCount
Range("J5").Select
ActiveCell.FormulaR1C1 = lCount - 1
End With
Next

View 2 Replies View Related

Create Sheets From Ranges

Oct 16, 2009

I have this file, I would like to create a sheet for each different supplier code (column D), and each sheet named with the supplier code.

View 3 Replies View Related

Sum Different Ranges Across Sheets/Worksheets

May 26, 2008

I have three sheets: On sheet 1, I have a list of products ( Down ) with Bought and sold info by time period ( Across ). On sheet 2, I have the same list of products from sheet 1 ( May not be in the same order ) with " Attributes " associated to them ( e.g. male, female etc ). On Sheet 3: I would like to add the Bought and sold info by time period on sheet 1 by attribute as per sheet 2

View 2 Replies View Related

Macro To Compare Two Ranges In Two Different Sheets

Apr 17, 2014

I am relatively new to VBA macros. I am having two sheets, in one sheet I have a non-contiguous 20 rows range and in the other sheet I have a 20 row contiguous range. I need a macro which will compare data between the two ranges(one to one compare) meaning 1st row of the first range should compare with 1st row of the second range and if it matches then it should populate the adjacent column in the second sheet with true or false accordingly.

View 2 Replies View Related

Copying Sheets Containing Named Ranges With VBA

Apr 9, 2014

This should be a fairly simple question, I've already got the code for copying sorted, I'm just curious about several of the named ranges within the sheet and what happens to them when I copy it all over.

I'm working from a spreadsheet made by my predecessor and they've got a template sheet with numerous named rages all scoped to the workbook, on the new template I've created, it has lots of ranges scoped to the sheet itself. The new template works just fine as it is but I just want to try and make sure that when I change over the templates it will continue to work fine and the current template's named ranges are going, so far I have actually copied everything and are running tests, but you can't test for everything, so if the named ranges on the new template being only scoped to the sheet will affect it at all?

View 2 Replies View Related

Sort Ranges Across Multiple Sheets

Jan 24, 2010

Sub DynaSort()
Dim wsSheet As Worksheet
iRow = ActiveSheet.Columns("A").End(xlDown).Row
For Each wsSheet In Worksheets
Select Case wsSheet.CodeName
Case "Sheet2", "Sheet3", "Sheet4"
wsSheet.sort.SortFields.Clear
Range("A3:I" & iRow).Select
wsSheet.sort.SortFields.Add Key:=Range("F2:F" & iRow) _
, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
wsSheet.sort.SortFields.Add Key:=Range _
("H2:H" & iRow), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _ ...................

The problem that I has is that I cannot put focus on a cell after the sort. Xl keeps the columns selected and then when I'm trying to put in the next data Excel selects all the rows in Sheet1 also. I know how to get rid of it and continue, the users on the other hand are not that experienced with excel. fun thing, even thou the, Range.value is inside the IF it putt "pucko" in sheet1. I have a code that copies the data and then put some several functions in each sheet, after that I call the sort routine.

View 8 Replies View Related

Mirror Two Ranges Of Data On Different Sheets

Jun 7, 2007

Is there a way to mirror two ranges of data on different sheets - i.e. if I update one sheet, it updates on the second, without using formulas, macros.

View 3 Replies View Related

Add To Selection By Right Clicking And Union

Jul 15, 2014

I'm trying to make a code so that when optionbutton1 is checked, it allows you to right click to select a cell, then right click on a different cell and select that cell as well as the previous cell, etc. Here's What I have so far.

[Code] .....

View 6 Replies View Related

Matching Ranges In Different Sheets And Then Copying Certain Cell Value

Apr 25, 2009

There are two sheets: Sheet1 and Sheet2

Columns in Sheet 1 are the following:
company PERMNO number, dates, market capitalization (no data in this one)

Columns in Sheet 2 are the following:

company PERMNO number, ticker, dates, market capitalization

In Sheet2, there is a whole range of dates (between 1990 and 2004) for every single company, whereas in Sheet2 there is one or 3-4 dates for a every single company.

I would like to match the exact date/dates in Sheet1 in the range of dates in Sheet2 and then if they match, copy the corresponding cell value from the market capitalization column in Sheet2 to the empty market capitalization column in Sheet1. I also want that the company PERNO numbers match.

In short: if PERMNO numbers match, match the date/dates in Sheet1 within the range of dates for the same company in Sheet 2 and copy the market capitalization value to Sheet1.

Example:

Sheet1:
A B C
1. PERMNO DATES MARKETCAP
2. 13123 199803
3. 13123 199904
4. 65456 200005
5. 44550 200104
6. 44550 200211......

View 9 Replies View Related

Copy Over Rows To Sheets Based On Value According To Ranges

Oct 9, 2009

I am having trouble with IF, ELSE and END IF statements. In Column H I am trying to copy over rows to sheets based on value according to ranges. I am trying to use the code below but everything seems to get copied in to the first sheet "0-500". d= worksheet name.

View 6 Replies View Related

Copy Sheets From Another Workbook Without Named Ranges

Nov 16, 2009

I have VBA code that copies over several sheets from a workbook, but I'd rather not have all the named ranges come through. Whenever I run the macro it gives me this notice: "A formula or sheet you want to move or copy contains the name 'rngRegion2', which already exists on the destination worksheet..." The destination sheet doesn't originally have this range already, it runs into problems because the sheets I'm copying have rngRegion2 defined on different sheets. I'd like to try and avoid this problem all together by not copying named ranges if possible.

If thats not a viable option, is there code that I can use to tell it to automatically select "Yes - To use the name as defined in the destination sheet" as opposed to prompting the user to select yes/no?

View 2 Replies View Related

VLookup To Work For Multiple Ranges On Different Sheets

Feb 23, 2012

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Sheet8.Range("C16:Y1000")) Is Nothing Then Exit Sub
Dim rw As Integer
Dim arw As Integer
rw = 16

[Code] .....

As you can see, my code is located in the sheet8 worksheet object. Now, I have a few questions about this. Because I am located in the sheet8 worksheet object does that mean my code can only work in sheet8, i.e., the following won't work because I am in a Sheet8 worksheet object?

Sheet10.Range("B12) = ..... ....... .....

This is not returning a value in Sheet10? My question is how do I make my code return a value in Sheet10?

View 6 Replies View Related

Clear Contents Of 2 Ranges On Multiple Sheets

Mar 29, 2012

I need to clear the the text in the same cell ranges on multiple worksheets. on a regular basis.

F7:K13
Q7:Q13

Is there a simple way to do this?

View 7 Replies View Related

Filtering Ranges And Copying Results To New Sheets?

Jul 5, 2014

I want the following script to:

1) Find and define a range ("DateRng") based on cell values. This is controlled by the first block and the script does this job correctly.

2) Find cells

View 1 Replies View Related

Form Range From Ranges On Multiple Sheets

Jul 20, 2009

Is it possible to form a single Range object from ranges on multiple sheets. So for example, would it be possible to set a Range object equal to cells A1:D146 from Sheet 1 and A1:B49 from Sheet 2 and if so, what would be the syntax? For the first I have:
Sheets("SelectData").Range("A1:D146").SpecialCells(xlCellTypeVisible)
but I wouldn't know how to proceed from there.

View 9 Replies View Related

Macro: Print Ranges From Multiple Sheets

Dec 6, 2006

I am trying to write what I thought would be a simple macro to print out specific areas of my worksheets. I have shown the code below; the line causing the problem I have highlighted in RED. I am getting the following error message: "Select method of range class failed".

Reading other posts here. I think this may have something to do with the macro being assigned to a command button in one worksheet (AY114) and I am trying to get the macro to run on both the worksheet that the command button is in (AY114) as well as another worksheet (AY062).

Sub CommandButton1_Click()
Range("J2").Select
Sheets("AY114").Select
Range("A4:J53").Select
ActiveSheet.PageSetup.PrintArea = "$A$4:$J$53"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=45
Range("A56:M151").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$M$151"......................

View 4 Replies View Related

Union Result Does Not Include Second Range?

Aug 15, 2014

I'm writing a function which includes the need to merge 2 ranges into 1 new Range object. The problem is that adding a breakpoint just after this occurs, and viewing the immediates, the new range only actually contains the first input range

For testing, and displaying here I've placed the 2 sub-Ranges into temp objects

[Code]....

The breakpoint is on the next line (not shown), so I can view all the Locals

tempyR1 and tempyR2 both are Range objects containing the correct Range data as expected from the code
However, objExcelRange only contains exactly the same as tempyR2

I'm using the Locals to check as this new combined Range will undergo further processing before being placed onto a sheet, and the combined Range will actually be generated dynamically depending on what options the user picks from a second sheet within the WorkBook - which could in fact be created from between 2 and 5 sub-Ranges

View 7 Replies View Related

Trying To UNION Same Cell And Blank Cells Possible?

Nov 12, 2012

I'm trying to select the same cell and a blank cell multiple with a UNION(). Is this possible?

Here's a summary of my code:

Code:
Dim c
Dim Rng As Range
Dim blankC As Range

Set blankC = Range("XFD1048576")

Code] ......

Ultimately, I'm trying to copy various cells and reorganize them including blank cells to conveniently use UNION for a quick copy and paste as opposed to explicitly defining where to paste everything.

View 9 Replies View Related

Checking If Selected Cells Is A Union

Aug 24, 2013

I would like to check if selected cells is a union of several ranges or one solid range. I am sure there is a way, but did not manage to find it. How to do it?

View 2 Replies View Related

Union Selection Without Looping Through Cells?

Apr 6, 2014

how one can union a selection without looping through the cells? I need to write a noncontinuous range into an array, and it can't be sorted as I'm using specialcells(xlCellTypeVisible).

View 1 Replies View Related

Loop Through Rows Of A Union Range

Aug 19, 2006

'How do I use a FOR NEXT loop on a union to just list all cell values in Col A?

'Just need a little help getting a handle on ranges and the union of ranges.
' This is my first time posting a question so any patience is appreciated.

Dim r1 As Range
Dim r2 As Range
Dim myMultipleRange As Range
Dim xCell As Range

Set r1 = Sheets("Sheet1").Range("A1", Range("A65536").End(xlUp))
Set r2 = Sheets("Sheet1").Range("B1", Range("B65536").End(xlUp))
Set myMultipleRange = Union(r1, r2)

r1.Select

For Each xCell In r1
'Ive been using this type of code to access anything I need from a sheet.
Debug.Print r1(xCell.Row, 1), r1(xCell.Row, 2), r1(xCell.Row, 3)
Next xCell...............

View 9 Replies View Related

Object Required: Union Method

Nov 9, 2006

columnA contains account numbers sorted in ascending order i need to use the union method to select all rows where the account number changes so that later i can insert rows between different account numbers in one action. i tried to use the following code but an error occurs stating 424 object required ....

View 7 Replies View Related

Union Function For Multiple Worksheets

Jun 1, 2007

I have two worksheets sheet1 & sheet2 in Workbook3 both get data from two different workbook1 & workbook2 using Microsoft Queries. Column Headers are same but contentes and no. of rows are different in these sheets. I would like to combine data from these sheets into sheet3 of Workbook3 so that i can create pivot table on that. I tried using Union Method, But it gave me "Run time error '1004'. Method 'Union' of object '_Application' failed in the following line

Set myMultipleRange = Application.Union(r1, r2)

Union method works fine when combining ranges from the same worksheet.

Multiple consolidation in Pivot table is not desirable to me as i have to group data using two columns and i could not get what i wanted using two page fields.

I have created named ranges for each sheet data. Is there an alternative to Union method for combining these named ranges from two worksheets into third sheet through VB code?

View 5 Replies View Related

Printing Scattered Ranges In Different Work Sheets By One Click

Apr 9, 2007

I have four sheets workbook, and i'd like to set a command button that prints scattered tables ( ranges) on my workbook but still have a pattern.

I attached a simple wokbook that explains how ranges are positioned in my workbook, i hope that it is possible to print these tables in the sequence shown in the file by a single command.

View 5 Replies View Related

Union Range Comprised Of Two Cells On Worksheet

May 19, 2014

I am trying to create a union range that is comprised of two cells that are no where near each other on a worksheet. I keep getting the object required error. Everything else works perfectly.

Dim myRng As Range
Set myRng = Application.Union((Range("IndData1")), (Range("IndData2"))).Activate
Do Until ActiveCell = ""
Selection.Copy

[Code] .....

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved