# Creating Macro That Can Grab Varying Amounts Of Data And Put Them In New Worksheets

May 1, 2013

I have one worksheet with four columns of data. Column A is a well name, RA-0001, column B is the measured depth of the well from 0 feet to however far down it goes, anywhere from 4000 to 15000 feet, column C is the inclination of the well, column D is the Azimuth.

I have 500 wells from RA-0001 to RA_0500 or so all in this one worksheet, all the wells have varying Measured Depths associated to their well name. I need to create a macro that can separate the wells and either put them in a new worksheet for each well, ie. a worksheet named RA-0001, RA-0002, ..... ect. OR, and this would be nicer, a macro that can actually save all these individual wells as (Formatted Text (Space Delimited)) files with the associated well name.

Here is an example of what it looks like. The columns do not have a subject line to state what information is in each column because I dont need it in that format.

RA-0001
0
0.00
0.00

RA-0001
100
0.91
5.56

[Code] .......

Even just knowing how to create a simple macro that would take all the data from each well so I could manually copy and save them as new files.

## Creating Macro To Calculate Columns With Varying Length

May 2, 2012

I have a column of data where the seperate entities are divided by blank rows. for example: i need to calculate sum(G3:G15) then skip the blank row (G16) and calculate the next group, say sum(G17:G50) and export that import to a new worksheet.

The issue is that the column lengths vary from day to day so the blank rows will not be in the same position.

## Dividing Dollar Amounts Unevenly Into Varying Numbers Of Cells/columns

Dec 10, 2009

I have dollar amounts that I need to distribute amongst varying numbers of columns and not have have the total distributed be over or under the original amount by any number of cents. When I simply divide the dollar amount by the number of columns, the total of those columns can sometimes be more or less than the original dollar amount by a few cents.

e.g.
Dollar # of A B C All Columns
Amount Columns Total
--------------------------------------------------------------------
\$25.05 2 \$12.53 \$12.53 \$25.06
\$11.47 3 \$ 3.82 \$ 3.82 \$ 3.82 \$11.46
\$25.05 divided into 2 columns gives \$12.53 in Column A and \$12.53 in Column B. Total of Columns A and B is \$25.06. Over by a penny.

\$11.47 divided into 3 columns gives \$3.82 in Columns A through C. Total of Columns A through C is \$41.46. Under by a penny.

I know I could simply always add or take away the pennies from one column, but I would prefer the process to be random or formulated in such a way that the Column to which the extra pennies are added to or taken away from differs in order to be "fair to each column".

## Writing Macro To Grab All Data From Word And Just Paste In Excel

May 8, 2014

I need in XL for a macro to run, open my word file, copy the entire thing and then paste it back in XL.

So leaving it general, Word.doc and Excel.xlsx

It's all because my company's POS system will only export to Word but everything we do is in XL.

## Creating Macro To Print Specific Worksheets In Workbook

May 15, 2014

I have an excel workbook with about 40 worksheets. I have a formula in A1 of each sheet that returns a 1 or a 0 depending on whether or not the name of the worksheet is in a list.

What I am trying to do is create a macro that will print all worksheets that have a 1 in A1.

## Flexibility- Allowing My Macro To Run In A More Global Environment With Varying Data-set Lengths

Aug 27, 2008

I'm having difficulty allowing my macro to run in a more global environment with varying data-set lengths.

For example:

Though the following "copy" scenario works for me when my data set is contained within cells B2:B35:
Range("A2:A35").Select
Selection.Copy

It does not allow me to work with a set of data that extends beyond this--say to B50 or B60. Is there any way to allow this command, and others, to be a bit more flexible with my data set lengths?

## Summarise A Varying Quantity Of Worksheets?

Sep 29, 2008

I have monthly workbooks which each have 5 weekly sheets and one summary sheet. I intend to copy the summary sheets to a new "total period analysis" workbook.

In this second annual workbook I intend to total all the monthly sheets together then analyse it. BUT as the books for the months to come haven't been written yet, I can't include them in my formulas. How can I write formulas to include the sheets that WILL be included without getting #ref errors? Can it be done. Or, is there a better way to do this? Some VBA to add the new sheets to the formulas maybe? I don't know VBA.

## Creating List Of Data From Multiple Worksheets

May 13, 2009

I am trying to create a list of residents, unit types, and unit numbers. I have 3 problems with the following code.

1) The code ignores sheets that have no value in the specified cell. I need it to return a blank cell for those in order to keep the data in each column matched with the sheet it came from.

2) The code includes data from hidden sheets. I only want to list data from unhidden sheets. And more specifically, I want to omit data from unhidden sheets that have their tab colored black.

3) The code includes data from the sheet named "Totals" which I thought I was telling it to ignore.

## Add Standard Set Of New Worksheets In Varying Multiples To Workbook

Feb 16, 2012

I would like to be able to add a standard set of new worksheets in varying multiples to my workbook from time to time.

How would I:

1. COPY the entire contents of a worksheet I've named 'Last' (content may be added to from time to time - so 'copy all' rather than a set range, so that:

2. Each worksheet created after 'Last' is named in turn 'Vendor1', 'Vendor2' etc

Sometimes I will need to create, say 10 further w/s 'Vendor1' to 'Vendor10' other times maybe 50 w/s. or more.

After these new w/s are created they will be manually renamed to the name of the actual vendor - so this naming convention of Vendor1 etc, will not cause problems with existing named worksheets.

However, the position in the sequence of 'Last' will obviously change as new w/s are added so the VBA would need to take this into account.

## Grab Data From One Sheet And Put It Into Another

Nov 6, 2009

I have two documents with very similar information. Some rows are duplicates. However, there is one column of information missing from the 1st spreadsheet. Let's call them Sheet1 and Sheet2.

How do I tell Excel:
In Sheet1, IF A2 matches any field in Sheet 2, column A

Then: copy the data from the same row, in a different column (Sheet1 A3) and put it into a specific corresponding cell (Sheet2 A3)

Basically I'm trying to grab a product ID from sheet2 and put it in the matching cell, for that specific product. I have all teh product IDs in column A in sheet 2. The product IDs field in Sheet 1 is blank. I need to populate it with teh correct information from Sheet 2. But there are many moreproducts in sheet 2 than in sheet 1, so I can't just sort and do a copy paste. It needs to think "if the product exists in sheet 2, grab the info from sheet 2 and put it into sheet 1 for that product id."

## Macro To Open Up Word, Grab A Label Template

Aug 16, 2003

Included in the macro, I would like for the macro to open up Word, grab a label template, that I have stored (have to navigate there), and then merge the data from the excel file into the Word template.

## Grab Data From All Files With Certain Name In A Folder

Jun 28, 2009

Is it possible to have a spreadsheet that when opened grabs data from every file with "*invoice" in the title in folder 2009 and all its sub directories and puts them into a list in the opened file?

The data needed is to be from sheet1, cells B16, C18, G18 from all the files
and i would like B16 put into A2, C18 into B2 and G18 into C2
Then the next sheets data B16 to A3, C18 B3 and G18 to C3 etc etc until all files with invoice in the title have been added.

B16 is a name
G18 is a postcode

This means there can be no duplicates so if opened twice there will only ever be 1 of the same address and postcode.

## Exiting A Range To Grab Additional Data

May 8, 2006

I've set a range as DD14 to G014, then using a For Next Loop asked the program to cycle through each cell in the range. Upon findng a populated cell, it stores several bits of data from that cell (already done) and then move up one cell to record the cell value (time) and again move one cell up to record the date.

Now I did get this to work using a clunky bit of code that used activecell.offset to move the focus up the two columns and a While .... Wend loop - but using a set range and cycling through each cell it does not appear to be possible - or am I wrong?

Set MyRange(1) = Range("D14:ER14")
Set MyRange(2) = Range("D23:GN23")
For MyCounter = 1 To 2
For Each Cell In MyRange(MyCounter)
If Cell.Value = "" Then Goto Skip_Cell
MyPosition = InStr(1, Cell.Value, ",")
If MyPosition = 0 And Len(Cell.Value) >= 3 Then
Tool_Number = Left(Cell.Value, 3).....................

## Varying Validation Drop-downs Based On Varying Cell Values

Aug 7, 2013

I am attempting to have cells in Column 'U' deliver different drop-down menus based on the corresponding value in column 'D'. I have created 7 named lists:

List_117G
List_152
List_JMET
List_XBAND
List_PACWIND
List_VORTEX
List_ROVER

Those lists will be called up based on 7 values in column “D”:
“G”
“152”
“J”
“X”
“D/E”
“V”
“R”

So far I have only been able to get this to work for the first category “G”. When I change the value of column “D” from “G” to “152” I no longer get a drop-down. Here is the formula I am using in the List function of validation.

=IF(D6="G",List_117G,IF(D6="152",List_152,IF(D6="J",List_JMET,IF(D6="X",List_XBAND,
IF(D6="D/E",List_PACWIND,IF(D6="V",List_VORTEX,IF(D6="R",List_ROVER,)))))))

## Combine Rows Of Varying Length And Varying Number Of Occurrences

Jun 28, 2012

There are 11,000 rows and 4,000 are unique. The goal is to merge the data down to the 4,000 records. Each of the unique entries shows up 1 to 15 times.

In trying to solve this, I wonder if I should break this down into the different # of occurances and implement specific solutions. e.g., There are

5700 entries that show up 2x
504 that show up 3x
24 that show up 12x

## Grab Data From Table If Fulfills Two Chained Conditions Without Using Concatenated Key

May 26, 2014

How to grab data from a table providing it fulfills two chained conditions without using a concatenated key. For example:

I have this table and I want to get the combination Name + Color. :

A
B
C

1
John
White

2
John
Red

[Code] ........

The database is something like:

A
B
C
D

1
John
Red
3254.30

[Code] ......

I ended up using VLOOKUP() and a concatenated key like Name&Color but it slow down the sheet significantly as I have many records and also does not seem the most elegan solution. I tryied using OFFSET() nested with MATCH()but couldn't get it. Also tried something with ARRAY FORMULAS but I am not very proficient at them.

## VBA - Call Macros With Varying Names And On Varying Modules

Oct 2, 2013

I want to call a macro with a varying name that is within a module with the same name.

I have a module called Test1 and within this, a macro name called test1
I have a module called Test2 and within this, a macro name called test2
etc

On another module called Test8 (with the macro called Test8), this Test8 macro will call either Test1 or Test2 or Test3 etc depending on what I choose in an excel spreadsheet. So on sheet1, cell A1, there is a drop down with the options Test1 or Test2 or Test3 etc.

The following works to run the macro test1 from module test1 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test1.Test1
End Sub

The following works to run the macro test2 from module test2 (when it does not vary i.e. i physically put in the name of the macro myself):

Sub Test3()
Test2.Test2
End Sub
etc

However, if I try it so that the calling of the macro varies as below , it does not work:

Sub Test3()
MacroToCall = Sheets("Sheet1").Range("A1").Value
MacroToCall.MacroToCall
End Sub

## Merging Similar Data And Amounts

Feb 22, 2013

Column A: List of document numbers
Column B: List of amounts

A B
111 \$5.00
222 \$10.00
111 \$5.00
222 \$15.00

I need to merge all equal document numbers so that it shows the full amount on that document number. It needs look like this in the end:

A B
111 \$10.00
222 \$25.00

I realize a pivot table will do this but the formatting of a pivot table will not work with what I am ultimately trying to do.

## Macro For Rows Of Varying Lengths

Jul 13, 2007

I paste in information in Columns A-E with varying lengths for each report that I need to generate (They generally range anywhere from 10-500 rows). I have formulas already in place in the first row of Columns F-L that need to be copied down as far as columns A-E go. I recorded a macro for this when I try to run the macro for another report with more or less row entries it copies down as far as the original recording was set. How do I make it so the rows will copy down just as many as I need and not some arbitrary number of rows? This is my first post so if any clarification is needed that's no problem.

## Extract Amounts (with Currencies) From Raw Data Text

Jul 10, 2014

I have a payment Excel sheet with thousands of lines Every line (one cell) has row payments data from mainframes like the following

Lhfskldhflshlshdflsd 234 234 sdljflsdjfkl 345.000,56 EUR lshflhshlfsld 2342 sdffgsd
Fsdlflsdf -384823 jsdlfsdlfjsdlfjldfjsldjf 43 50.000,56 EUR fsdgdfgdsfdg 23432
Jlflsdjflsjdfld 569 USD fjsldjflslfhsdlhdlshg 3-448 jlfsdkljfklsd
Etc.

Is there a way (UDF or Macro) to extract from every line(cell) (and put them in the cell to the right) these raw data only the amounts with the Currencies.

PS. (most of the times the amounts mentioned before are the biggest number in every line!)

## Sorting And Counting Large Amounts Of Data

Jan 1, 2009

want to be able to take a large quantity of data, sort all the like data together, and then quantify the number of each like data. I need the equations to do that.

## Functional Way To Break Up Very Large Amounts Of Data

Mar 28, 2012

I am looking for a functional way to break up very large amounts of data. I am looking to break them up by an ID number and then by date. The date function needs to break up data from a hire date to the closest date to a year without going over and proceed to do that for multiple years. I am hoping that the function can just add a blank row between the split data. The file that I have not comatins three years.

## Given Weekly Data Coming Up With Monthly Amounts

Sep 5, 2009

I am to the end of my wits - or maybe it's impossible to do the following with formulas?

I have the data like this:
Column A: Date (which is basically the date for the beginning of weeks)
Column B: Month of the date in Column A
Column C: Year of the data in Column A
Column D: Weekly data.

A: DateB: MonthC: YearD: Weekly data
12/11/200612200619
12/18/200612200644
12/25/200612200650
1/1/20071200741
1/8/20071200737
1/15/20071200741
1/22/20071200741
1/29/20071200741
2/5/20072200732
2/12/20072200736
2/19/20072200740
2/26/20072200735

Maybe it's because it's Friday night, but I just can't invent how to do the following:

Create a new column E that would contain the monthly sum of ColumnD across all weeks of this month - but entered only against the first week of that month (that is currently in Column A)
I.e., in my example it should be:
113
empty
empty
201
empty
empty
empty
empty..................

## Pivot Table For Large Amounts Of Data

Apr 7, 2007

if there was a way I can put it in to some kind of pivot table, because the hardest part for people when they read his list is it's so large it's hard to find data easily.

So this is how he formatted his data... I was wondering what would be the best way to get this list in to a possible pivot table. This is a consilidated example, there are plenty more columns, but this will give you an idea of my problem.

A2007 A2006 A2006 B2006 B2006 B2007
Feb Jan Feb Jan Feb Jan
630 Labor Cost 1000 7500 3000 4500 800 5000
624 Equipment Cost 900 50 40 300 20 1400

Now, the only thing I can think of is to make columns, but then I'd have to recopy all the task names (which there is about 700) for each of the different years (A= Actual, B= Budget, F= Forecast). Is there any other way that you can think of to do this with out making it so complicated? Any help or suggestions would be great. I really want some format that allows you to click the total and it goes to what makes up that total.

## Averaging Daily Set Of Data With Each Day Containing Different Amounts Of Data

Feb 4, 2014

I have attached a tiny part of a massive data set I am working on. As you can see in column 2, the data is roughly every 15min for 5 days. The data I am interested in averaging is color coordinated in column 3 (if you scroll down you can see a different color for each day's data set.)

In column 5 the dates are summarized into days as opposed to the 15min breakdown. In column 6 is the problem. How do I get the averages of the relevant data in column 6 in such a way that I can drag the formula down and the next cell will automatically calculate the average for the NEXT day, REGARDLESS of how many temp readings there are, as this data fluctuates from day to day.

Excel Problem 2.xlsx‎

## Creating Macro To Filter And Copy Data

Jun 19, 2014

I have a spreadsheet. This has columns begining with A to S (with data in it)

It has rows beginning on row 5 and goes down to 85 but it gets bigger every day.

I want to create a macro with several phases

Phase 1 - Select the rows I specify, ie pop up box to decide on the rows
Phase 2 - Filter down and select the rows in this selection where column K has a value (unselecting the rest)
Phase 3 - Copying Columns A, B, D, E, F, K, from the selection
Phase 4 - Pasting these columns into A, B, C, D, E, F, of the sheet the macro sits on....

## Converting Vertical Data To Horizontal For Varying Data - Large Amount

Jan 20, 2009

I have a one column spreadsheet. The column contains this data:

1 Name
3 City
4 State
5 Zip
6 Telephone
7 Fax
8 URL
9
10
11 Name
13 City
14 State
15 Zip
16 Phone
17 URL
18
19 Name
20 Address ... and so on

Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.

I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.

I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.

## Macro To Subtotal Varying Lists Of Numbers

Apr 17, 2014

I am fairly new to excel and I am having trouble trying to get a sub total function to work for me. I need the subtotal to for a list of about 20 columns in one line with a varying amount of rows. So the subtotal must stop when there is a blank at the top. I can get the macro to find the totals A on the spreadsheet and then offset to beneath the columns of numbers but the sub total formulas takes in every number. Here is the example:

456.00
8,798.00

45,465.00
789,789.00

45,465.00
789,789.00

45,646.00
7,897.00

Totals A

[Code] ........

## Macro, Autofill On Varying Numbers Of Rows

Jan 13, 2003

I am putting together a macro to import a CSV file onto excel. I will be manipulating columns of data (concatinating some, dividing by 100 etc.)

What I am having trouble with is that each file imported will have different number of rows.
I tried, in recording a macro, to use autofill (entered formula and double-click fill handle to drop the fourmula into each cell) hoping this would show me how to do it but alas, it just coded the range as the starting and stopping cells (didn't show a count formula or some such.

## Transpose Macro Varying Rows From 1 Column

Aug 22, 2009

I've trawled a few forums for a while now trying to find a macro that matches my specifications, or one I can adapt but so far have been unsuccessful, so here goes:

I have one column of data (col. A) of customer names and addresses each with an empty row in between them. The lines of data relating to each customer vary from 6 rows to 14 rows, then an empty row, then the next customer and so on - for several thousand rows.

I am wanting to create a macro that will transpose each customer to either their own row in sheet2, or to column B and then delete column A at the end.

All I can find is macros for a specific number of rows rather than varying. One way would be to have a code that 'pads' out those customers with fewer than 14 lines by inserting rows to make them 14, and then a code to transpose 15 rows at a time.