# Macro To Produce List From Specific Range

Dec 2, 2009

I have a workbook with 2 worksheets, Sheet1 & Sheet2.
Sheet1 has a list of products on it, with a code, pack size and cost.

Sheet2 has got 8 columns in it.

The first column will be A , B , C

The second will be D, E, F

Third will be G, H, I

Going right through to column 8 which will be V, W X

In the second column in each on I will be have a (sell by date) So B, E, H, through to W will have sell by dates in them.

What I am looking for is a macro that will give me a list of short sell by dates when I click the button. So if today’s date is 02/12/09 when the button is clicked it will give me a list of all the product that have lets say a month sell by date left, I would like to be able to change the month to anything I want, just in case I want to do a longer check on the sell by date.

When it finds a short date I would like it to check against the code, so in the first column the code will be in A, it will match that code up against the code in Sheet1 and give me a list of the Code, and the Product from sheet1 and the sell by date that’s in Sheet2. The problem I have is when data is put into the second column on sheet2 which is D.E.F, I would want it just to check that column and not the A,B,C from column 1.

## Produce A List Of Combinations From A Range Of Numbers?

May 9, 2014

Am looking for an easy way to produce a list of combinations (maximum 6 numbers) from a range of numbers listed in 6 different columns:

Example
Column A contains : 1,2,3
Column B contains : 7,8,11,15
Column C contains : 12,16,18,19
Column D contains : 17,30,31
Column E contains : 30,31,32,33,34
Column F contains : 37,39,40
The rules are:

In each combination of 6 numbers, numbers should always be taken from ALL 6 columns. In each combination, numbers cannot repeat.

show me a formula to arrive the results.

## Produce A List Of Text, After Selecting From Validate List

Jun 5, 2009

i want to do is, have a list that picks from a color and then beside it, it will show the possible fruit that is that color for example

Red
apple
cherry
strawberry

OR

Green
watermelon
grapes

The number of fruit will differ by the color so i know i need a counter there, but i am not sure how to list the fruit after i've picked the color

## Produce A Report In Specific Layout From Varying Data

Sep 18, 2009

My last post here was brilliant, I was very close to figuring the problem out myself (I'm learning) and the help I got here really was useful to help me finally get what i wanted to work.
Unfortunately, this request isn't so simple, as I haven't a clue what to even search the forum for to help me with this.

I have attached a simplified example to try and explain what i would like to happen.

Sheet1 holds information which is regularly updated, so the number of rows can vary, as the number of customers vary.

I want to produce a management report from this data, with a specific layout.
I have put the layout into Sheet2 - the cells highlighted in yellow are there simply as a reference to show the cell from which column would go where.

I produced the top report, by filling the cells with =and then clicking on the cell on sheet1 that I wanted displayed there.
I thought that if I then selected the table and filling down, it would produce the same layout table for all the other rows - but I was wrong.

Another problem is that, this report is needed to be entirely automatic, so I don't want to fettle filling down cells for the number of rows. I will need it to count the number of rows in Sheet1 and produce a report in the specific layout for each company in sheet 2.

It may be an option to use a button create the report, to save the report into a new workbook with automatic file name containing the date and also print a copy out automatically on the default printer.

I don't know how to go about this task at all, so I would really appreciate some help, even if it is pointers for what i should be searching the forum for!

## Produce Lists From Master List

Oct 24, 2011

I have a set of unique customer names in a column A, say A1 to A200 in my data sheet. These customers have route numbers assigned to them (defined by the client) column B - B1 to B200. The route no.s are 1,2,3,4,5,6,7, & 8. The column lists can be of varying lengths but no more than 500 rows. I want to relate the routes to 8 separate sheets in the workbook. In each sheet I want to extract a column of customers based on the route number. So if the first customer in column A is route '1' (column B) it populates the first entry in sheet 'Route 1' with the customer name as so on for the rest of the sheets.

## How To Produce List Of Unique Values

Jan 21, 2012

I was trying to produce a list of unique values from a much larger list. I'm using this array function -

{=IFERROR(INDEX(Sheet2!\$B\$2:\$B\$100,MATCH(0,COUNTIF(\$B\$1:B1,Sheet2!\$B\$2:\$B\$100))), "")}

- which is marvellous and gives me exactly what I want. However my test book only has this dragged down 10 rows and already it takes 20-30 seconds to calculate. The real workbook is likely to stretch to several hundred rows, and to make matters that little more complex where I have Sheet2!\$B\$2:\$B\$100 this will relate to a completely separate book on a company network.

Is there any more efficient method of creating this list or will I just have to bear with the calculation speed?

## Combine Values From Two Columns To Produce Unique Value List

Jun 28, 2013

I have two columns of data and would like to generate a new list showing unique combinations of those values (see attached sheet).

unique list.xls

## Produce List Of Row Data Based On User Selected Cell Criteria?

Apr 7, 2014

I have a workbook that has three worksheets. The ASU Database sheet contains part numbers and descriptions including specific details that are selected from pull down data validation lists that are named on the third worksheet. I want to have a front end on the first worksheet that has similar data validation lists for the user to select from to create a search criteria that will create a worksheet with only the rows that meet this criteria. I am not sure what method to use to accomplish this.

## VLOOKUP Query (produce A Dropdown List Of These Items For Use Within The Pricing Sheet Worksheet )

Mar 27, 2009

I have attached a sample workbook, (Pricing Sheet - Major) within this work book there is a worksheet entitled Price Book which has commonly-used materials, unit prices and labour rates. What he wishes to do is use the contents of the Price Book worksheet within a worksheet called Pricing Sheet to enable him to prepare quotations.

I had the idea to first sort the items in the Price Book worksheet and then produce a dropdown list of these items for use within the Pricing Sheet worksheet and used the VLOOKUP function to obtain the values for unit prices and labour within the Price Book and use them in the Pricing Sheet worksheet.

## Excel 2003 :: Expanding List To Use In Mail Merge Program To Produce Tags For Direct Mailing

Sep 25, 2012

I have an Excel 2003 list with four columns as shown below:

Zipcode
CRRT
Count
Bundles

85710
C004
693
14

85710
C005
867
18

85710
C006
1021
21

I want to "expand" this list to use in a Mail Merge program to produce tags for our direct mailing. Each bundle contains 50 letters, so in the first line on the above spreadsheet, there will be 13 bundles of 50 and 1 of 43. Currently, I can cut and paste to create the following table, but there has to be an easier way...

Zipcode
CRRT
Count
bcount
Bundle
ibundle

85710
C004
693
50
1
14

[Code] ........

So as I pull this into a mail merge I will get a tag that shows the zipcode, the crrt, the total pieces for that crrt, then number of the bundle for example " 1 of 14" and the quantity in that bundle, for example "50 of 693", then the next bundle tag will print, 3 to a page. I know that here has to be an easier way either in the mail merging process itself or with EXCEL? Am even open to build these with ACCESS to create the tags. The beginning database contains anywhere from 350 to 800 lines of original data, so as you can see the cut and paste is VERY time consuming.

## Macro Produce Result In Sheet

Jun 20, 2009

I have the following data which is download as csv, i need a macro to produce result in sheet2, notice if there are duplicate stmt or ledg for the same case No then the units need to be consolidated as shown in sheet2

Outstanding Holdings Case ReportAs At Thursday 4 June, 2009 16:15AccountSourceCase No.Ledg StmtValue DateMessage FeedSecurity CodeDescriptionCcy CodeUnitsExceptionUnsettle UnitsCase TypeKnown SecurityMatch NoCase AgeAge BreakAgent Code:DERIVHOLD ABERDEENJPMC.AEIABIDERIV562272Ledg4-May-09JPMC.AEIABIXT0906AUS 10YR 0309 BOND FAUD-440Hold Singleton CaseYes305779830LATEST CASE NOTES:5-May-09FuturesJPMC.AEIABIDERIV569967Ledg1-Jun-09JPMC.AEIABIYT0906AUS 3YR 0609 BOND FUAUD7-70Hold Singleton CaseYes2599402LATEST CASE NOTES:2-Jun-09FuturesItem Count : 2JPMC.AEIABIFA1569598Ledg29-May-09JPMC.AEIABIQTC0813QUEENSLAND TREASURY CORP 140813 6.00 GBAUD150000015000000Hold Net DifferenceYes3598143JPMC.AEIABIFA1569598Stmt29-May-09305496516891QUEENSLAND TREASURY CORP 6+ BDS 14/ AUG/2013..........

## Macro To Produce Purchase Order Data

Oct 17, 2008

i have a purchase order sheet

firstly i have attached said spreadsheet
1st sheet is named purchase order
2nd sheet is named purchase order numbers

upon opening the spreadsheet i would like on sheet purchase order to automatically input the next available number from column a in sheet purchase orders ( in this case next available is st010 and put that into cell k12 on sheet purchase order

i then want to enter the relevant data in sheet purchase order cells
b16 , b23 , g23 , i23 and d28

i then want to click a button with macro attached to transfer that data in to sheet purchase order numbers

## Macro That Returns Specific Results From A Longer List

Aug 4, 2008

I am trying to write a macro that returns specific results from a longer list based on an entry in a cell.

For example if I have “Colours” typed in cell A1 in Worksheet 1 and there is a list on Worksheet 2:

Colours Black
Colours Yellow
Colours Green
Animals Dog
Animals Cat
Animals Tiger

Then I would like the blow answers returned in cell B1 on worksheet 1
Black
Yellow
Green

## Macro Code To List Files In Specific Folder

Apr 15, 2008

I am unable to install a disk catalog and have been trying with no success to try and get something in excel that will look at a directory and display the contents of that folder with the file attributes etc

## Excel 2010 :: Produce Unique List Of Key Values With Second Values Concatenated Together

Sep 15, 2013

I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)

The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.

Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b

My system is Windows 8 Excel 2010.

## Macro Not To Run In Specific Range

Mar 5, 2014

I am using the below macro to delete row:

HTML Code:Â

Sub REMOVE()
Set Rng = ActiveCell
ActiveCell.EntireRow.Delete
End Sub

However I would like not to apply that macro when I am in the row 12:26! btw, I am running my script by pressing CTRL+D.

Last thing would it also be possible to "extend" the exception to another range ? (name manager -> row 84:276 called "NEXT") if possible to define two name manager for the exception to implement to the existing macro.

## Run Macro In Specific Sheet Range?

Jun 16, 2014

I have my template workbook with sheet tab as following:

Summary/Report/Contact 1/Contact 2/Contact 3/Contact 4/Contact 5/Note/

And I have one macro to run and only want to run in the specific sheet as below:

Summary/Report/Contact 1/Contact 2/Contact 3/Contact 4/Contact 5/Note/

The sheet name for contact 1 to 5 is varies means it can be change to any name based on different job. How should I write my macro in order to do that with one macro?

## Macro To Clear Range In Specific Sheets

Apr 27, 2014

Looking to code a loop to go to sheets whose names begin with "day" and a number and clear a specific range. How would i code this without affecting the other sheets in the book?

## Macro To Delete Specific Range Of Rows

Jul 12, 2014

I currently had to start from scratch as my formula kept raising the debug screen. I know the answer is simple but I cant figure out what it is. I have column A, rows 9 through 1000 with data. I want to delete the entire row of any "x" value present in column A rows 9 through 1000. I am new to VBA. So simple is good.

## Place Chart On Specific Range Macro

Aug 1, 2007

I'm working on an assignment that pulls information for different countries and I'd like to know how to go about placing the charts on a specific area. Any help would be greatly appreciated!

The concept:

With each click of a country, the country's information will be pulled from a 'data dump' that I created in the same file. The map and flag are also shown. Some of that information is just using lookups, or a combination of lookups and code, and the hardest part are the GRAPHS - which I'm having a really hard time placing and adjusting accordingly.

The problem:

As of now the charts are automatically filled as the user clicks a new country, but all of the charts are placed in weird locations, but I'd like for them to be placed in the specified ranges above.

I would like charts in the following ranges:

A20:B30 (Immigrant Group)
D20:E30 (Ethinc Group)
F20:G30 (Languages)
F13:G19 (Religion)
D10:E17 (Age structure)

This is the major part of my project that I need to complete. Any help would be greatly appreciated, PLEASE. =)

Right now I have ranges that aren't accurate, but regardless of how I try to show my range, the function does not work:

Sub CreateCharts()
Dim counter As Integer, chartname As String, xvals As String, offset As Integer, Range As String

'Delete all charts
Dim oc As ChartObject
For Each oc In ActiveWorkbook.ActiveSheet.ChartObjects
oc.Delete
Next oc

## Macro To Auto Hide Rows In Specific Range

Jun 10, 2014

I have the following macro to autohide some rows with no data, however its remove rows starting from column A.

[Code] .....

How do i fix this to only hide the rows in the range (H4:V50)?

## VBA Goal Seek Macro To Calculate Specific Range Only

Nov 20, 2008

Is there a way to use goal seek in VBA that won't trigger a full model calculation?

I would like to (somehow) perform goal seek, but only recalculate range("A1:B50") say on a single worksheet (where both the input value and the final calculated value is included in that range), as opposed to calculating the whole model inbetween each iteration.

Also, all dependencies between the input value and the final calculated result are contained in that range.

## Macro To Copy Range Hidden Paste In Specific Location

Mar 26, 2014

I have recorded the below macro and I am not very happy with it. enhance the macro and add the following:

I need to copy the range "ROW" and past it below the range "MENU". Also the range "ROW" (ROW = copy of 3 line with some cells merged) is hidden.

I need to unhide and hide back the rang ROW. Right know I have to leave the range appearing otherwise my macro doesn't work.

HTML Code:Â

' ActiveWindow.LargeScroll Down:=2
Range("ROW").Select
Range("ROW").Activate
Application.CutCopyMode = False
Selection.Copy
' ActiveWindow.LargeScroll Down:=-2
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Range("A7:A9").Select
End Sub

## Search Specific Text In Column Range (Macro Or Formula)

Jun 30, 2014

I need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".

Case is not sensitive.

Words
Title
Displayed?

Christmas
Coworker has chronic hiccups
Yes

Excel
I Love Excel
Yes

Cartman
I Like Turtles
No

Ninja
Oh Christmas Tree
Yes

Tiger
Case of the Mondays
No

Chronic
Cute Monkeys
No

## Macro To Find Matching Text In A Range And Select Specific Cell

Oct 18, 2013

I have a client list in a table in my excel sheet. I currently enter the clients in a sheet that looks similar to a form. After the clients info is in this Form I have a button with a macro to copy the client info into a table on another sheet. This works perfectly.

Now I have another form that will retrieve the customers info in a click of a button in order to make changes to the client. Ei:I now have a second phone number and I want to update that client's info. What I want to do from here is take the info in Cell B2 (clients full Name), Do a vLookup in the "Saved Clients Table" in a Range within my table range. (Range: Saved Clients and the client's full name is in \$A:\$A and called "Clients Full Name" and once that name is found I need to select the cell in column C and the corresponding row.

This will allow me to copy the info in my form, Paste it in Transpose to the cell that is selected. (I already have that code)

Here are screenshots of what I am trying to accomplish. [URL] .......

## Specific Cells Populate With Specific Numbers When A Value Within A Range Is Entered

Oct 9, 2009

Here is what i am trying to achieve. If the date 2/20/2010 is located at F53 & the cell next to it at H53 is populated with a number between 1 & 16, then i want the cell at J11 (42 rows further up) to auto populate with the number 1. When this occurs the cells beneath this, from J12 to J52 should also auto populate with the with consecutive numbers from 2 to 42. Would also like to see the cells with numbers 1 to 28, automatically format to orange & the cells containing numbers 29 to 42 automatically format to yellow. I plan to have this condition repeat several times later in the year, at dates that are to be decided. When these dates are decided i want to be able to enter a number from 1 to 16 & next to the date & all of the above automatically occurs.

## For EACH Cell In Specific Range Meeting Specific Criteria - Call Sub

Jul 1, 2014

Looking to have a macro call a subroutine every time it finds a cell meeting specific criteria.

Code in plain english would look like this:

For EACH cell in range A1:BZ500 meeting the following criteria:

Cell value is a date

AND

Cell's date is at least a week or more in the future

AND

Cell background (Fill) = RGB color code: (R:191 G:191 B:191)

DO the following:

Call repeatingsub

## Macro Loop To Extract Specific Values And Min/Max Values From Column/Rows Range

Jun 3, 2009

Hi, I'm very new to writing Excel Macro's and wanted to know if I could do the following. Conceptually, I understand what I need done and think it should be fairly straightforward.

There's 2 main events in this loop (I hope that's the correct terminology):

Input 1) User defines the beginning cell to start the loop. In this case, A2.

Input 2) User defines the range of columns/rows to display. The formula for rows that I've thought of is 4r. So if a user wants 20 rows below cells A2, they simply input 5 for r. The number of columns is a constant 5. So if r=5, then I'd want the range to be A2:E22......

## Macro To Copy Specific Cells From Row From Source &amp; Stop When Next Row Cell = Specific Value

Nov 7, 2008

I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)

After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.

Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.

Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select

## Find Specific Text In Cell That Matches Range Then Display Specific Text?

Aug 5, 2013

I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.