Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Extract Data From another sheet using VBA

I have a data sheet in a workbook which I need to extract all the rows where Col G is equal to "Y" and insert the adjacent Cells (Cols A:F) into another sheet.

Is there a piece of VBA code that can do this please?

Many thanks


Post your answer or comment

comments powered by Disqus
Hello,

I really need help

Is it possible to load a data from a sheet using a combo box? The options in combo box should be the names of the sheet (BMW, AUDI...). I need to make a summary sheet that needs to be extremely user friendly.

Each sheet has the same number of columns but different number of rows.

I have posted an example... The summary sheet will give you an idea of what I wish to achieve

Thanx a lot...

Hi Guys, i am from brazil, i am creating excel plan, but i would like to put some complex formulas on it to create report using data from another sheets.

Sorry for my bad english. My native language is portuguese.

I translated my plan from portuguese for all forum users understand.

Please download my plan here: http://www.witson.com.br/uploads/englishversion.rar

I will use this plan to control payment Receiving (check and slip).

My objective is Sheet (Resumo) Get automatically data from anothers sheets (Loja 1, Loja 2 and Loja 3).
For example Open Shop 1 (Loja 1 in Portuguese), see dates, month 04 go from Number 1 to 18, now go to Resumo Sheet, you will see month 04 checks copied.

I want that Resumo Sheet get data in this sequence:

Get all lines for month 04 checks from Loja 1 Sheet > Get Names Fields and all lines for month 04 slip from Loja 2 and Loja 3 Sheets > Get Names Fields and all lines for month 05 checks from Loja 1 Sheet > Get Names Fields and all lines for month 05 slip from Loja 2 and Loja 3 Sheets > Repeat until Finish

I use 2 macros on my plan, go to Loja 1 Sheet and type in line 67 any number in credit (it automatically will create new line waiting new data entry..) and i also am trying auto update Resumo Sheets using Macro, go to Resumo Sheet and Press Data Update Button (you will see that automatically it will get all 04 month lines from Loja 1 Sheet, but i am not able to develop something better for now, i just started use VBA 4 days ago and i dont know Programming language.. I dont know if better way to create this list is using macro or another way, please help me, i only will be able to use this plan after help from this forum users..

Only to understand, i already tried post this doubt from more than 6 excel forum from brazil and anybody was able to help me, becuase of that i researched better excel forum and translated all my plan to try get help..

If not understand anything, please reply that i will try clarify..

Thanks for All

I need to sum data from a colum using a specific date range from another
excel sheet. Sheet 1 contains my summary page extracting the sum from sheet
2 by listing date ranges on sheet 1 to find corresponding numerical data to
be summed that fall in the date ranges. Any ideas where to start?

Can someone help me with a VBA code that grabs data from another sheet (in the current workbook) into the current sheet?

for example I want to grab data in cells A1:B15 and copy it into my current sheet into range C1:D15.

the sheet I want to copy differs from time to time so ideally, I'd want a popup message that asks me "what sheet do you want to copy from?" and then copies from there. The range doesn't change.

Thanks.

Hi i have a code that points to a column and extracts data from there ... but what if i insert a new column and it will crash the macro. so i was wondering if there is a way to extract data from the column using the title given to the column the title is in row 8 ( always will be there)

	VB:
	
 
    txtStatus.Caption = "Status: Reading budget file (1)" 
    DoEvents 
    On Error Resume Next 
    Set NewBook = Workbooks.Open(txtBudFile.Text) 
    If Err.Number  0 Then 
        MsgBox "System cannot open " & txtBudFile.Text, vbCritical, "Open budget files" 
        Application.Cursor = xlDefault 
        Exit Sub 
    End If 
    Set mySheet = NewBook.Sheets(1) 
    iRow = 10 
    Do While Len(mySheet.Range("C" & iRow)) > 0 
        If Not FillBudget(MyTaskData1, mySheet.Range("C" & iRow), mySheet.Range("F" & iRow)) Then 
            If Not FillBudget(MyTaskData2, mySheet.Range("C" & iRow), mySheet.Range("F" & iRow)) Then 
                If Not FillBudget(MyTaskData3, mySheet.Range("C" & iRow), mySheet.Range("F" & iRow)) Then 
                    If Not FillBudget(MyTaskData4, mySheet.Range("C" & iRow), mySheet.Range("F" & iRow)) Then 
                        If Not FillBudget(MyTaskData5, mySheet.Range("C" & iRow), mySheet.Range("F" & iRow)) Then 
                            FillBudget MyTaskData1, mySheet.Range("C" & iRow), mySheet.Range("F" & iRow) 
                        End If 
                    End If 
                End If 
            End If 
        End If 
        iRow = iRow + 1 
    Loop 
    NewBook.Close False 
    Set NewBook = Nothing 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Please help me modify it

Hi,

Please give me the code for extracting data from one sheet and displaying it in the other sheet.......

thanks and regards..

vinod

Hi all,

I am having a "run time error 424 object required" when I try to read rows from another sheet. My code works fine until the rows are selected. Then, I try to process every selected row, but I have this strange error... My code is this:

	VB:
	
 Range 
 
Worksheets("Sheet1").Activate 
 
Set sales = Worksheets("Sheet1").Range("table1") 
sales.AutoFilter Field:=25, Criteria1:="2011" 
sales.AutoFilter Field:=4, Criteria1:="sale" 
sales.SpecialCells(xlCellTypeVisible).Select 
 
If selection.Areas.Count

.

(¯`•._.•[ Extract Data from Multiple Sheets ]•._.•´¯)

http://www.sbtankserve.com/SAMPLE.zip

This is a SAMPLE excel file that I use to enter weekly data at work

Data for 5 days is entered for five days on five sheets

DAY 1
DAY 2
DAY 3
DAY 4
DAY 5

The first 7 rows are used for day specific calculations and column headers; Data is entered from the 7th row and below

The first column is used for day specific serial numbers

Maximum data range on any of the five Day pages is A1, L38

I was wondering if any body could be so kind as help me achieve the following objectives with the help of a formula or macro

For steps 2,3 & 4 I have found array formula's that remove blanks from a given column but I need assistance in spanning this effect across multiple columns with one as a reference , the across multiple sheets

--------------------------------------------------------------------------------------------------
1) Copy all the data on the five sheets to a new sheet to a new sheet called "Consolidated"

But the data must be only
- Values,
- Fonts and
- Background cell colors only!
--------------------------------------------------------------------------------------------------

2) Create a new sheet called "Dispatches"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1, Day 2, Day 3, Day 4, and Day 5) that have entries in the DPS column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date Value" from each respective page

Basically creating a sheet with all the dispatches for the week

--------------------------------------------------------------------------------------------------

3) Create a new sheet called "Call Backs Made"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1, Day 2, Day 3, Day 4, and Day 5) that have entries in the "Phone Number" column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week

--------------------------------------------------------------------------------------------------

4) Create a new sheet called "One Call's"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1, Day 2, Day 3, Day 4, and Day 5) that have entries in the "1 Call" column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week

--------------------------------------------------------------------------------------------------

Any assistance with the following problem will be greatly appreciated

- Hyperdreamz

.

Hi there,
Is it possible to do the following?

I would like to create some command buttons on my sheet.
When a user clicks on the command button then, depending on what cell the cursor is on, the command button action will extract data from another program to fill that particular cell. Is this possible?

Thanks in advance.

.

(¯`._.[ Extract Data from Multiple Sheets ]._.´¯)

http://www.sbtankserve.com/SAMPLE.zip

This is a SAMPLE excel file that I use to enter weekly data at work

Data for 5 days is entered for five days on five sheets

DAY 1
DAY 2
DAY 3
DAY 4
DAY 5

The first 7 rows are used for day specific calculations and column headers; Data is entered from the 7th row and below

The first column is used for day specific serial numbers

Maximum data range on any of the five Day pages is A1, L38

I was wondering if any body could be so kind as help me achieve the following objectives with the help of a formula or macro

--------------------------------------------------------------------------------------------------
1) Copy all the data on the five sheets to a new sheet to a new sheet called "Consolidated"

But the data must be only
- Values,
- Fonts and
- Background cell colors only!
--------------------------------------------------------------------------------------------------

2) Create a new sheet called "Dispatches"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1, Day 2, Day 3, Day 4, and Day 5) that have entries in the DPS column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date Value" from each respective page

Basically creating a sheet with all the dispatches for the week

--------------------------------------------------------------------------------------------------

3) Create a new sheet called "Call Backs Made"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1, Day 2, Day 3, Day 4, and Day 5) that have entries in the "Phone Number" column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week

--------------------------------------------------------------------------------------------------

4) Create a new sheet called "One Call's"

This sheet must contain all the rows from ALL the FIVE sheets (Day 1, Day 2, Day 3, Day 4, and Day 5) that have entries in the "1 Call" column

But the
- "Serial number" column HEADER must be replaced "Shift Date" and
- Each respective "Serial Number" cell must represent the "Shift Date Value" from each respective page

Basically creating a sheet with all the "Call Backs Made" for the week

--------------------------------------------------------------------------------------------------

For steps 2,3 & 4 I have found array formula's that remove blanks from a given column but I need assistance in spanning this effect across multiple columns with one as a reference , and then across multiple sheets

Any assistance with the following problem will be greatly appreciated

- Hyperdreamz

.

Hi,

I am looking for a way to create a sheet (which I'll call A) to read data
from another sheet (which I'll call B).

The data in B is team scores. What I need to do is record on sheet A the
results from the teams in B. The scores on sheet B are not in team order (for
example, the results for team 1 are in column AA but on rows 1, 6, 8, 20 and
so on). Is there a formula that I can use to read the data for each team and
present an average score on sheet A with one column for team name and one
column for average score?

I'd be grateful for some help. Oh, and a Happy New Year to you all!

Dear all,

I am using MS Excel 2003 and would like to retrive multiple data from another sheet. For example i have client information sheet which is Sheet1 where I have multiple information related to same client like Client Code, Name, Address, Phone, Email etc. Now considering that the Client Code is the only unique information which has entire row for detail information, I want to retrieve data in my second sheet which is Sheet2 by just writing Client Code and all of it's information will be retrived as per my specified columns in Sheet2.

Kindly let me know how can i do this.

Thank you.

hi all ,

I want to extract data from another worksheet if it fulfills a condition.Then i will sum the values together .

Function getMonthlyProfit(month As Integer) As Double

    Dim sumOfProfit As Double
    
    sumOfProfit = 0
 
    For startRow = 7 To 46
        If Worksheets("Data(JobsList)").Cells(startRow, 3).Value = month Then
        sumOfProfit = sumOfProfit + Worksheets("Data(Jobs List)").Cells(startRow, 14).Value
        End If
    Next startRow

    getMonthlyProfit = sumOfProfit
    
End Function

I tried to use this function , but it always returns a #value error. Please advise why.

THanks a million!!!

Hi,

Please find the attached example, where I have tried to use validation list selection in formulas to read data from another sheet.

How can I get the real value from another sheet instead of the correct reference formula in text format.

- janne

Hi all,

I am having a "run time error 424 object required" when I try to read rows from another sheet. My code works fine until the rows are selected. Then, I try to process every selected row, but I have this strange error... My code is this:

Dim sales As Range

Worksheets("Sheet1").Activate

Set sales = Worksheets("Sheet1").Range("table1")
sales.AutoFilter Field:=25, Criteria1:="2011"
sales.AutoFilter Field:=4, Criteria1:="sale"
sales.SpecialCells(xlCellTypeVisible).Select

If selection.Areas.Count <= 1 Then 'this line is the one where the error appearsFor Each row In selection.Rows<<<
code to run >>>Next row
ElseFor Each a In selection.AreasFor Each row In selection.Rows<<< code to run >>>Next rowNext a
End If
This code works fine when I execute it on the same sheet, but when I try to use data from another sheet it doesn´t work (I need it to execute from 2 different places because the <<<code to run>>> is different depending on the place where is executed)

Any idea??

Thanks in advance...

Hi,

Is there a way to copy a table from another database using vba? I want to use a form OPEN event to launch the code, then have the code copy a table from an existing database into the current database.

Thanks

Eljays

Hi everyone, first post here.

I have uploaded my excel file. it is 26kb
http://members.optusnet.com.au/adrianloh01/Boots.xls

I have a test tomz at uni, and i need to be able to bring data from
another sheet (sheet 1) from a droplist box on another sheet (sheet 3).
These are just sales figures for a motorbike boots (my assignment). I
have made the drop downlist that has all the "sales agents" in it, but
i need to know how to make it so when you click one "agent" the data
comes from sheet 1 and brings up the relevant sales information.

I have started putting the headings and needed columns on sheet 3.

If anyone could help me here, i would really appreciate it.

thanks,
Ady

--
bnr32
------------------------------------------------------------------------
bnr32's Profile: http://www.excelforum.com/member.php...o&userid=26739
View this thread: http://www.excelforum.com/showthread...hreadid=399966

Hi All

I am looking for a formula like VLookup to select data from another sheet.
The VLookup one I have that works is

=VLOOKUP($A$3,INDIRECT("'"&A4&"'!$A$3:$K$38"),9)

The only problem is that the data that is being retrieved has to be in
alphabetical order.

I have tried the following formula

=OFFSET(A3,MATCH(INDIRECT("'"&A33&"'!A1:A100"),0), 6)

However can not get it to work, any help much appreciated.

Richard

All experts there please look into the attached file and post comments or suggestion, I think there are many improvement possibilities. I am new to Excel and just tried to create a Dashboard kind of display that extracts data from other sheets. Thanks

Hi everyone,

I am looking a solution for retrieving data from other sheet using the dropdown list.
I had attached sample worksheet.

Selector tab needs the solution.

For Cell A1 need to have dropdown menu - and the data must be the tabs name
for example: Offer1, Offer 2, Offer 3...

when selecting offer 1 need to display at Column C the data located at tab Offer 1. Same also when selecting the offer 2 and offer 3 (dropdown list.)

thanks for the help.

I want to import data from another sheet and I want to know who to do that !

Here is a link to my excel file. If you open it you will understand what I'm trying to do .

Hi everyone, first post here.

I have uploaded my excel file. it is 26kb
http://members.optusnet.com.au/adrianloh01/Boots.xls

I have a test tomz at uni, and i need to be able to bring data from another sheet (sheet 1) from a droplist box on another sheet (sheet 3). These are just sales figures for a motorbike boots (my assignment). I have made the drop downlist that has all the "sales agents" in it, but i need to know how to make it so when you click one "agent" the data comes from sheet 1 and brings up the relevant sales information.

I have started putting the headings and needed columns on sheet 3.

If anyone could help me here, i would really appreciate it.

thanks,
Ady

I know how to pull the data from another sheet (ex. =Sheet1!A1) but how would I pull that data then apply a formula to it? the formula is lenghty, but mainly it's to round the number.
example:
=ROUND(A2, 3-(INT(LOG(A2))+1))

I'm just not sure how to mix both =Sheet1!A1 and =ROUND(A2, 3-(INT(LOG(A2))+1)) in 1 formula.

is it even possible?

What I am trying to accomplish is a search sheet ('SEARCH') for easy access.
I want to know if there is a way that I can enter a number into a cell, then from the number entered, pull data from another sheet ('Master Acct') to populate a list.

Here is an example.

On the data sheet, there is a column that contains a date that a piece of equipment is due inspection. The difference between that date and todays date gives us how many days out until inspection.

On the "search" sheet, I want to be able to put a number and then have a list populate from the data sheet onto the search sheet showing all pieces of equipment that fall in that window (i.e. If I put 120, then all equipment that is due 120 days or less from today() will show up on the search page)

I have included my example (sloppy I know, but it's a work in progress):

What I'm trying to do:

Enter a value into A10 on 'search'
All values in column A of 'Master Acct' that are <= the entered value, I want displayed on 'Search' A11-?? (I only want Column B,C, and E info from 'Master Account brought over)


No luck finding an answer? You could always try Google.