Pull Specific Cells From The Large Workbook

Jul 14, 2007

I have a sheet with 200 sheets in called workbook x , I need to create a summary new workbook with only data on 1 sheet.

I want to pull specific cells from the large workbook x to 1 single sheet on a summary workbook:

A6 I want to be surname taken from B1 of workbook x
B6 will be first name taken from C1 of workbook x
Then I will copy data from M1 on mastersheet to cell C6 on new workbook
Then I want to copy Row A11 up to T11 and paste on the new workbook (Summary) in D6 staying in the row 6
Not sure if this is the tricky bit I want to repeat as above B11 to T11 but paste in the next empty cell of the same row of D in my summary
I need to repeat this action upto A23 to T23

Then I need it to move on to the next sheet in X and repeat all the steps above but do it on the next row down row 7 and so on

View 9 Replies


Pull/Extract Number Before Specific Character In Cells

Jun 10, 2008

What formula or function truncates a text field that is in the following consistent format:

number. name
ie. 3. Super Bella
and 150. Taoist

I wish to truncate the field so I have just the integer without the dot, space or word(s).

Tried Len, Left & Replace but they require a specific number of characters. Since the number can be 1 to 3 digits long, this doesn't work.

View 3 Replies View Related

Using LARGE Formula With Index / Match To Pull Values When There Are Duplicates

Sep 13, 2012

I am attempting to use the LARGE formula to pull the top 3-5 percentages out of a field of 50-100, while using the INDEX/MATCH function to pull the corresponding "descriptor" that is associated with those top 3-5 percentages. (I need to do this across multiple data sets, but I can't get past this 'duplicate' issue) However, I am running into the problem when there are 2 percentages that are identical (WH 14 and WH 16 in pasted text below), then the INDEX/MATCH function only pulls the 1st "descriptor" and doesn't continue down to the Duplicate. how to tell excel to move to the next set of duplicate data and match the 'descriptor' to that data?

I have attached a file that should show what I am trying to do. These are the formulas I am using right now, pulled down into the 3 cells below them to get the top 3.





Warehouse S/S %
WH 1 50.00%
WH 2 57.14%
WH 3 0.00%
WH 4 50.00%
WH 5 100.00%
WH 6 60.00%
WH 7 33.33%
WH 8 66.67%
WH9 60.00%
WH 10 63.64%
WH 11 78.57%
WH 12 55.56%
WH 13 42.86%
WH 14 71.43%
WH 15 61.54%
WH 16 71.43%

Attached File: Book1.xlsx‎

View 9 Replies View Related

Specific Cells Imported To New WorkBook

Oct 13, 2009

I work with a machine that takes readings and exports them into an unorganized Excel file along with irrelevant data. What I need to do is create a macro that will import only specific data and place it where I want it.

I only need to go from File A to File B, so no complex data compilation from multiple workbooks [at this time!] What may be an issue, however, is that there is no set number of data entries. It could be anywhere from 1 to 500 depending on the sample. What I'm hoping is that since the samples are added in a set pattern that it will possible to accomplish this. I've attached an excel file with two sheets. Sheet one is to represent the data file that is extracted from the machine and sheet two is a mockup template demonstrating how I want the data organized in the new file after import

View 4 Replies View Related

Pull Data From One Closed Workbook Into Active Workbook With Command Button

Feb 27, 2014

I've been using the following code to bring in individual cell values from one closed workbook to an active one. I would like to modify this is possible to bring in multiple cells at once and also pull them into a different worksheet in the active workbook. Basically, my command button is on Sheet1 but I'd like the data to pull into a cell on Sheet2.

Private Sub CommandButton1_Click()
With Range("Q9")
.Formula = "='C:Users[Workbook Name.xlsm]Worksheet Name'! N27"
.Value = .Value
End With

View 6 Replies View Related

Workbook Too Large After Copy Graphics From Other Workbook

Sep 11, 2009

Gosh, the workbook I'm copying the graphs from is only 4 mb in size but after I copy and paste those graphs to another workbook (empty one) it became 122mb in size

Does any of you know why this has happened and how to copy the original size graphs to new workbook?

View 6 Replies View Related

VBA To Color Cells - Search Workbook For Specific Values?

Mar 22, 2013

I am trying to create a VBA to:

1. Search workbook for a specific values and then to color that cell with a corresponding color.

2. Search workbook for a specific values and then color other cells underneath (the next 3 merged rows after the cell containing the value) with a corresponding color.

View 3 Replies View Related

Copy Specific Cells From Workbooks And Paste Onto Main Workbook

May 25, 2014

I have around 200 excel spreadsheets/workbooks with identical ranges but each with different data. There is a total of 5 columns and 225 rows in each spreadsheet/workbook. Looks something like this:

Workbook 1:

Title 1
Title 2
Title 3
Title 4
Title 5


[Code] ....

The Cells I need to copy are in BOLD. I am trying to paste them onto another workbook as follows

Main Workbook:

Workbook #
File 2
File 3
File 4
Title 5
Title 5

[Code] ....

As you can see, each workbook has identical A2 and Title 5 columns, so they only need to be copied once onto the Main workbook where data from the B and E columns are different for each Workbook. So not only I need to copy and paste from a Workbook onto the Main Workbook, but the code has be able to paste it onto a new row in the Main Workbook (where each row in the Main Workbook will correspond to the data copied from Workbooks 1-200.

I am new to VBA but I tried a code where I would copy and paste one cell at a time onto the same workbook and not onto the Main Workbook, and then how to copy and paste from one workbook to another, but as you can imagine that would take a long time:


Sub dAmacro()
Range("G1") = "Workbook #"
Range("H1") = "File 2"
Range("I1") = "File 3"

[Code] .....

It seems like I need some kind of loop, where it would copy and paste a set of cells and repeat the entire process until it reaches the end of the Workbook 1 while making sure when doing same thing for workbook 2, that the data pastes onto a new row onto the Main Workbook. I feel I have the logic down, but its in the syntax where I am failing.

View 9 Replies View Related

Lookup Specific Cells In Multiple Worksheets To Bring Into One Workbook

Aug 13, 2013

I have a workbook that's setup like a form, with 30+ worksheets in it (lets say this file is called "source.xlsx"). I have information in specific cells I want pulled out in each worksheet and brought in to a new workbook called "Destination.xlsx" and listed in a row.

View 2 Replies View Related

Macro To Copy Rows Containing Specific Blank Cells To Another Workbook

Jan 30, 2013

I have spreadsheet of data, I need to extract any rows that have blanks cells in columns F or P or T.

If possible I would like a macro I could run that would cut all of the rows that meet the above criteria and paste them in to a separate sheet.

View 2 Replies View Related

Creating Specific List From Large Dataset?

Jan 2, 2014

We collect loan payments for 36 months from customers.

Column A lists 1000+ customers.

Column J lists the date we received payment 1 ... Column Q lists the amount we received on payment 1.

Column R lists the date we received payment 2 ... Column Y lists the amount we received on payment 2.

Column Z lists the date we received payment 3 ... Column AG lists the amount we received on payment 3.

This repeats for all 36 payments.

New customers are loaded in each month, so be aware that Column J, Column R, Column Z (and so on) have dates from 2011 and 2012 and 2013.

We'd like to create a list of all customers that have not made a payment for the current month as of a certain day (say the 12th). So this month, on January 12th, we'd like to search our data for all customers that don't have a payment listed between January 1st - January 12th.

View 14 Replies View Related

Extracting Specific Data From Large Spreadsheet

May 22, 2014

I have a large spreadsheet which contains multiple rows of rate data by employee. I have sorted by effective date with most recent date on the top to get the most recent rate of pay, however, is there a way or formula to be able to identify or extract the previous rate of pay after that? I have included a sample below. There are different #'s of rows of rate of pay based on how long someone has been in company, as well as different rates of pay and effective dates.

Employee #
Rate of Pay
Eff Date

John Smith
$ 99,000.00

[Code] ..........

View 3 Replies View Related

Pull Value From Closed Workbook Where Workbook Name Is In A Cell

Nov 24, 2007

I want to open an excel file whose name is a value in a spreadsheet, and then pull a value from the spreadsheet I queried into my existing workbook. Here is the syntax i have so far:

to note: I'm calling the workbook in which the macro is located testproject.xls, and I'm using [integer].xls as my list of files that the macro will query. Ultimately, this code will go in a loop.

Set currentCell = Worksheets("Sheet1").Range("A1")
varCellvalue = currentCell.value
Workbooks.Open "path_of_file" & varCellvalue & ".xls"
ActiveCell.FormulaR1C1 = "=[3.xls]Sheet1!R1C1"

The problem is, I want [3.xls] to carry the sale value as varCellvalue with .xls appended, and not be kept static at 3. I tried inserting & varCellvalue & ".xls" into the brackets, but with no luck. My only difficulty is getting [3.xls] to vary along with varCellvalue.

View 6 Replies View Related

VBA To Pull Data From Specific File?

Jun 12, 2014

I pull a report every month which has the same name - at the beginning - (i.e. Monthly File Report_06012014.xls but the date is different every month; where we keep a copy of them every month.

So I need a vba to pull the data from that specific file - since I'll have more files open, but wouldn't keep the date since it will be different every month - is there an & that goes at the end or somehting: Monthly File Report &....xlsm?!

View 4 Replies View Related

Pull Specific Data From Word

Nov 5, 2009

i need a macro that pull specific data from word to excel.

View 9 Replies View Related

Pull Out Specific Data From Table

Jun 28, 2007

I don't know where to begin or if this is even possible. I have report that I need to format.

See example file. Note: real file has 2000 rows.

The book date and book amount in the orginal is the POS date and POS amount in the format sheet. I don't need the "over/short" data from the original. Col. A contains store #'s and they are 2,3,or 4 numbers long preceeded by an "S-". In the formatted sheet I need the "S-" removed. The data is grouped by column C. 3 of the same equals 1. Groups vary.

View 8 Replies View Related

Pull Specific Data From Spreadsheet

Jun 28, 2007

I have a complex data set that has been imported into Excel from a binary data file and I am looking for a way to simply pull out specific fields. I tried to record a Macro that would simply delete the rows between the data rows I want to keep, but it always references itself to those same ranges and I'm not sure how to identify that I want it to sequentially move down the page deleting the same number of rows, and adding the row of information I want to keep as it goes.

View 9 Replies View Related

Need To Pull Out Specific Info From Mixed List

Apr 3, 2014

I pulled a report into excel that lists staff details and workgroups that they have access to. There are nearly 8000 staff who can have anywhere from 0 to 120 workgroups.

The workgroups are listed with the staff details in the following format:

SurnameForenamePost Title183860314040|188778743040|261226948048|584865373040|088365861041
SurnameForenamePost Title695416612049|751836367043|430463930049|461208099048|488798547045|723225723045|183860314040|472108996043
SurnameForenamePost Title177448555041|315136549047|400323457046

With the whole list appearing in one cell and not in numerical order.

I need to pull out a list for each workgroup, with all the staff who have access.

Even if there was a way I could separate out the workgroups and get them to line up.

View 2 Replies View Related

Macro To Pull From Specific Sheets - Ignore Others

Mar 12, 2014

My workbook has several worksheets so do you need the name of those to be excluded? Or how many sheets i require which are located to the right of the summary tab?

Detail - 5 sheets to the left of summary tab are to be ingored, 7 to the right of the summary are to be pulled (let's say they're named Red, Green, Blue). I only want the colour named tab data pulled and pasted into the summary

Code is below :

Sub karryan()
Dim i As Long
For i = 1 To 3
Sheets(i).UsedRange.offset(1).Copy Sheets("Summary").Range("A" & Rows.count).End(3)(2)
Next i
End Sub

View 1 Replies View Related

Pull Specific Image If Cell Equals To 0

May 23, 2014

I have six images in "Sheet3" I want a macro to copy and paste image one if cell A3 = 0. How do I know the "name" of each picture so I can change the image based on the value?

View 2 Replies View Related

Pull The Entire Row If It Finds The Specific Word

Sep 23, 2009

The following sub will look in the file ("FY09 SOF"), in column "A", search for the strings that begin with "2109", "3009", or ends in "-1", and copy the entire row. It will then paste these in the file ("FY09 PR Log Blank").

I also need it to look in column "C" in the ("FY09 SOF") file and pull the entire row if it finds the word "Payroll".

For some reason it will pull everything needed except the "Payroll" rows. What am I doing wrong?

Sub get_data()
Dim wb As Workbook, wbDest As Workbook
Dim ws As Worksheet, wsDest As Worksheet
Dim lngCalc As Long
Dim FoundCells As Range
Dim FoundCell As Range

Set wb = Workbooks("FY09 SOF")
Set wbDest = Workbooks("FY09 PR Log Blank")
Set wsDest = wbDest.Worksheets("Paste all here, then sort")

With Application
.ScreenUpdating = False..........................

View 9 Replies View Related

Pull A Specific Time Frame Of Data From Worksheet?

Jan 17, 2014

I am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.

View 1 Replies View Related

Pull Data From A Specific Cell From A Closed Worksheet In VBA

Feb 3, 2006

How can I pull data from a specific cell from a closed worksheet in VBA?

Not sure but I think that Workbook("wb1.xls").Worksheet("Sheet1").Range("A2") only works if the worksheet is open.

View 9 Replies View Related

Excel 2010 :: Macro To Pull AP Within Specific Zip Code?

Jun 15, 2012

I have an excel 2010 spreadsheet that lists all of our vendors and the amount we spent with them over the last year.

I want to know how many of them were local vendors.

I have a list of all the zip codes within a 100 mile radius (there are about 500 zip codes). I would like to write a macro or sort function that searches the entire vendor list and only reports back those vendors that are included in the zip codes I specify.

The columns are as follows:

Vendor City StateZip CodeTotal AP
Vendor 1TROY AL36082527.37
Vendor 2PHOENIXAZ85054100
Vendor 3TUCSONAZ8571416255

I want to keep the all the columns, I just want to eliminate all of the vendors that do not fall within the zip code criteria I set.

View 5 Replies View Related

Pull Specific Word From String Of Text In Cell?

May 16, 2004

I need to pull a specific word from a string of text in a cell and have that word shown in an adjacant cell. For example A1 will contain the text "Smith Sun Alliance Pension Fund" I need B2 to show "Pension". I cannot use any filtering or text to columns as the word Pension can be anywhere within the text in A1 and I have thousands of entries. So I need a function.

View 9 Replies View Related

Workbook Is Too Large

Oct 7, 2006

I am having a problem with a workbook, I have copied all cells and pasted Special (Values only). But for some reason the workbook is 38 Meg's. The workbook only contains 6 worksheets with about 60 rows and 32 columns with figures and no formulas. I have tried to find formulas by Goto/Special/Formulas and Excel has found nothing. What shoud I do?

View 4 Replies View Related

Large, Slow Workbook

Aug 31, 2008

I have a large Excel workbook (about 70,000KB) and it takes ages to load and is slow to operate.

Is there anyway of compressing it or making it run faster and not slow down the computer?

View 9 Replies View Related

Large Workbook Keeps Corrupting

Jul 11, 2007

I have a large model which basically imports data into a sheet, pastses extra columns with formulas into the data sheet, refreshes some pivot tables based on all the data in the data sheet and then refreshes a report with the data from both the data sheet and the pivots.

My problem is that the model is incredibly unstable. It will usually only let me run the process about 1 or 2 times before causing Excel to have a severe error, and crash. That xls will then become corrupted, and then not work again. However, if I restore a backup version, it will always run fine again, regardless of the data, 1 or 2 times.

In the past, it seems like the error may be tied to using either:




though I think this is a syptom, rather than the problem. VBA will actually just not perform these commands when the sheet is corrupted.

View 9 Replies View Related

Summary Page For Large Workbook

Aug 1, 2013

I am looking to create a summary page to show open items within a log of initiatives. The workbook has 21 different tabs logging initiatives by the each individual person and also 9 other data tabs. On the tabs containing the initiative logs, it states whether the initiative is open or closed and I only want the summary tab to show the open items. Is it possible to do this?

View 1 Replies View Related

Copy-paste Large Workbook

Jan 5, 2009

I'm having a little bit of trouble with my code. I basically need to copy-paste special a large workbook (50 worksheets) into a new range-valued workbook. I've defined Sheet as S, but in some instances when I use the variable "S" I received a runtime error 1004.

Please see the highlighted red "S" in the code below.

Also, if one knew of an easier method for this particular operation that would be great!

Sub PasteSpecial()

Dim W As Workbook
Dim S1 As Worksheet
Dim S As Sheets

Set W = Workbooks("newfile2")

Set S1 = W.Worksheets("Sheet1")

Set S = Sheets

S(Array("Sheet1", "Sheet2", "Sheet3")).Select
S(Array("Sheet1", "Sheet2", "Sheet3")).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat
Selection.PasteSpecial Paste:=xlPasteValues, Paste:=XlPasteFormat

MsgBox "New Range-Valued Workbook has been created"

Application.CutCopyMode = False

End Sub

View 9 Replies View Related

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