Free Microsoft Excel 2013 Quick Reference

(Sorry, this is an accidental repost.)

Hello All!

I'm trying to add the data from two spreadsheets within a workbook. Normally this would be fairly easy, but the information (the column title to be exact) is added based on a vlookup formula in both spreadsheets.

Here's what I'm referring to:

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

50.00 #2

#3

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

75.00 #2

#3

This is the formula used to create the title/header for column D in Tables 1 &2: =IF(COUNTIF(\$'Line Numbers'.A2:B322;D10);VLOOKUP(D10;\$'Line Numbers'.A2:B322;2;0);0)

This is where the data needs to land:
BA BB BI
C: PRE-PRODUCTION

NBR DAYS RATE ESTIMATED
ACTUAL 101 Auto Rentals

\$0
XXX
102 Air Fares:

\$0

103 Per Diems:

\$0

So basically, I need to add D3 (in this example) from Table 1 and Table 2 into BI2 while keeping in mind that the information is connected to a VLOOKUP formula and subject to change (i.e. D3 could be 102/Air Fares or something else - depending on the project).

Thank you!

DLM

## Related Results

Hello All!

I'm trying to add the data from two spreadsheets within a workbook. Normally this would be fairly easy, but the information (the column title to be exact) is added based on a vlookup formula in both spreadsheets.

Here's what I'm referring to:

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

50.00 #2

#3

A B C D
RCPT # DATE DESCRIPTION Auto Rentals

101

#1

75.00 #2

#3

This is the formula used to create the title/header for column D in Tables 1 &2: =IF(COUNTIF(\$'Line Numbers'.A2:B322;D10);VLOOKUP(D10;\$'Line Numbers'.A2:B322;2;0);0)

This is where the data needs to land:
BAC: PRE-PRODUCTION

NBR DAYS RATE ESTIMATED
ACTUAL 101 Auto Rentals

\$0
XXX
102 Air Fares:

\$0

103 Per Diems:

\$0

So basically, I need to add D3 (in this example) from Table 1 and Table 2 into BI2 while keeping in mind that the information is connected to a VLOOKUP formula and subject to change (i.e. D3 could be 102/Air Fares or something else - depending on the project).

Thank you!

DLM

I am having trouble pulling data from multiple spreadsheets into one master spreadsheet. Someone tried to help me and provided me with this code:

Sub DataCompiler()
Dim z  As Long, e As Long, h As Long
Dim f As String, g As String
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""compile"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir("C:UsersTomyDesktopardaman*.xls")
Do While Len(f) > 0
If InStr(f, "Test") > 0 Then
ActiveCell.Formula = f
End If
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) <> ActiveWorkbook.Name Then
For h = 1 To 2
g = Choose(h, "B13", "B15")
Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) &
"]LogResults'!" & g
Cells(e, h + 1) = Cells(1, 3)
Next h
End If
Next e
MsgBox "collating is complete."
End Sub
I have tried it and it is not working. The debugger says there is a problem with this line:

Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]LogResults'!" & g

What I am trying to do is pull the values from cells B13 and B15 from the worksheet named (LogResults) from multiple files that include "test" in the file name and list them in the master spreadsheet. Can anyone help? Thanks in advance.

### Adding data from multiple columns into one, data seperated by commas

Using =A7&","&B7 to add data from two columns into one, separated by a comma.

What about multiple columns, still separated by a comma? Please See Example in attachment

Excel 2007

I have a macro that calculates statistics and plots chart of the data from different spreadsheets within the workbook (Spreadsheets to be included in the statistics are user selectable). I use this workbook as a template and replace the data in the spreadsheet everytime I have to generate a graph for some new data. In other words, everytime I get some new data and want to view statistics of that data, I have to copy the contents of that spreadsheet into the template that has macro. Generally, I have to copy about 5 spreadsheets to the macro to view statistics in the same chart. My question is: Is there any way by which I can eliminate the copying and pasting of data into the template? Any suggestions are appreciated. Thank you.

### How do import data from two sheets into one

I'm trying to figure out the easiest way to do this.
I want two copy a set of data from two different workbooks and merge it
together into one workbook.

Book1 Data1
Book2 Data2

Merge book1 and book2 into book3.

I've created a button in book3 to pull the data from book1 and book2. I've tried several different ways but it's a bit messy. I was wondering if there is a simple code for it.

thanks!

### Compare And Sum Data From Two Separate Worksheets

Hi,

I have the following data from two worksheets (the same workbook) that i am trying to compare and put the result into a third worksheet:

Worksheets 1:
ID Value
1001 10
1002 20
1003 30

Worksheet 2:
ID Value
1002 20
1003 30
1004 40

Worksheet 1 and 2 have about 6000 rows (or more) each to be compared. From the above worksheets I wanted to first Lookup the ID in worksheet 2 that are identical with worksheet 1, in this case 1002 and 1003 and then sum up the Value and put the ID and the added Values into worksheet 3, so the results should be:

worksheet 3:

1002 40
1003 60

I have tried to use the For Loop with the following code and the problem is it take so long for the loop to run (more than 20 minutes)..... I am not sure what is the result as I have terminated the run after 20 minutes. I know For loop is not that efficient, could someone point me to the correct direction as to what method should I use?

VB:

count1 = WorksheetFunction.Countif(Worksheets(1).Column(1), ">0")
count2 = WorksheetFunction.Countif(Worksheets(2).Column(1), ">0")

For i = 1 To count1
For j = 1 To count2
If Worksheets(1).Cells(i,1).Value = Worksheets(2).Cells(j,1).Value Then
Worksheets(3).Cells(i,1).Value = Worksheets(1).Cells(i,1).Value
Worksheets(3).Cells(i,2).Value = Worksheets(1).Cells(i,1).Value + Worksheets(2).Cells(j,1).Value
End If
Next j
Next i

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

many thanks

Best Regards
Casey Wong

### Viewing Two Sheets within One Workbook

Is it possible to view two different sheets side by side within one workbook?
I know I can view two differnt workbooks side by side within Excel. Thanks!

### Extracting Unique data from two columns

I'm trying to find a way to extract unique data from two columns within an excel spreadsheet. There are nearly 70,000 records in each column. 2007 as you know has moved beyond the 2x16 rule. I'm trying to compare each record in both columns and if the data is in column a but not in column b I want to list it in column c. Any ideas on how to do this. I've tried a couple of MS solutions and they are able to identify the uniqe records but not place them into a separate column. I don't want to go record by record copying and pasting.

Thanks for you help and ideas!

Gary

### Combine Data From Two Worksheets To One

Hello all, I'm having another VBA coding fit that I was hoping to get help with.

I am trying to copy raw data from two worksheets (Sheet2 = "550 Report (raw data)" & Sheet3 = "305 Report (raw data)"), that I cannot edit, over to a new worksheet (Sheet1 = "Exception Management") that I can edit. I have been successful in coding Sheet2 to come over correct, but am having problems with Sheet3 because of a few issues:

1st: Column A on both sheets contain a unique alpha-numeric identifier that can be used to identify records that can be combined. Sheet3 will sometimes contain multiple records for a single Sheet2 record.

2nd: Matching rows from Sheet3 will always be done based on Sheet2 column A (So I need to search through Sheet3 column A based on all values in Sheet2 column A).

3rd: The dataset is MUCH larger than my example files. Sheet2 normally contains 700+ rows of unique identifiers with 56 columns of data. Sheet3 is pretty much as-is, except ususally 1/10th the number of rows as Sheet2.

Here is the code I am experimenting with:

VB:

CommandButton1_Click()

Sheets("Exception Management").Select

' Prepare Exception Management sheet for new data

Cells.Select
Selection.Delete

' Combine sorted worksheets onto Exception Management Sheet.

Dim j As Long
Dim LastRow As Long

'Turn off screen updating and xlcalculation to  speed up macro

On Error Resume Next
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets

If ws.Name = "550 Report (raw data)" Then
LastRow = Worksheets("Exception Management").Cells.Find(What:="*" _
, After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
ws.UsedRange.Copy Worksheets("Exception Management").Range("A" & LastRow + 1)
End If

If ws.Name = "305 Report (raw data)" Then

'Insert Sheet3 code here

End If

Next ws

'         cycle through  sheets and  copy to sheet "Exception Management"
'        For Each ws In ActiveWorkbook.Worksheets
'
'            If Not ws.Name = "Exception Management" Then
'                LastRow = Worksheets("Exception Management").Cells.Find(What:="*" _
'                , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
'                ws.UsedRange.Copy Worksheets("Exception Management").Range("A" & LastRow + 1)
'            End If
'
'        Next ws

'        LastRow = Worksheets("Exception Management").Cells.Find(What:="*" _
'        , After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'delete blank rows

For j = LastRow + 1 To 1 Step -1
If WorksheetFunction.CountA(Rows(j)) = 0 Then
Rows(j).EntireRow.Delete
End If
Next j

' End of Macro Processing reset to default conditions

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True

End With

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The commented-out section of code is the only "successful" code that partially works. However, it only adds the sheet3 data below the copied sheet2 data...which does me no good whatsoever because I lose my Sheet3 Column headings.

On my Example "Exception Management" worksheet page, I manually formatted an example result that I'm trying to achieve with the code. I can easily do this formatting manually for a few single records using AutoFilter, but it becomes very time consuming when updating 70+ records every day.

I hope this is enough detail to start, Thanks. ~Dan Auto Merged Post Until 24 Hrs Passes;

Sorry, apparently my file attachment was lost. Here is the example file.

### Consolidate data from multiple sheets into one sheet.

How do I copy the data from several spreadsheets onto one sheet without copying and pasting from each one seperately. I would like the data from the first sheet to be first, the second sheet directly underneath, etc., through the end of the sheets.

### Data from two sheets make up a list in a third sheet (real time)

Hello everyone

I have a following problem:

- Two processes, which are recorded on two different sheets
- Some of the designs need to be worked on once, twice or several times
before they are finished goods.

SHEET 1 (Proces1)
Design nr. Name Finish good Time finish

1 Name1 07:00
2 Name2 08:00
3 Name3 09:00
1 Name1 YES 10:00
4 Name4 11:00
2 Name2 YES 12:00
4 Name4 13:00
3 Name3 14:00
3 Name3 YES 15:00
4 Name4 YES 16:00

SHEET 2 (Proces2)
Design nr. Name Finish good Time finish

5 Name5 08:00
6 Name6 09:00
7 Name7 YES 10:00
6 Name6 11:00
8 Name8 12:00
6 Name6 YES 13:00
5 Name5 YES 14:00
8 Name8 15:00
8 Name8 YES 16:00

What I would like to construct is third sheet which is updated automatically
with the overview of times of finished goods. I made a very simple example
above, where in reality the sheets contain many more designs and information
which I would like to eliminate by making a new and simple list. Something
that looks like this:

1 Name1 YES 10:00
7 Name7 YES 10:00
2 Name2 YES 12:00
6 Name6 YES 13:00
5 Name5 YES 14:00
3 Name3 YES 15:00
4 Name4 YES 16:00
8 Name8 YES 16:00

The sequence of finished goods coming at the same time has no importance.
The finish times are important in order to plan the processes to follow.

If the function of combining data from two sheets into one list is two
difficult to do, I could live with two separate lists for Proces1 and
Proces2.

The way I see it is to â€śsearchâ€ť for YES and copy the line or specific cells
into a new arc, but I donâ€™t have an idea for how to do it.

Any ideas are highly appreciated.

Regards

### Data from two sheets make up real time list in the new sheet??

Dear all and JBM

I posted my problem two days ago, but I didn't have chance to check the
answer until now. In the mean while the topic is already on the 3-4 page.
Therefore I copied it once again at the end of this post. JBM answered me,
but this is not exactly what I need. First of all, I would need to copy data
in order to put it in the new sheet and I would like to have a â€śreal timeâ€ť
update over the situation from the two processes; in case that an order is
delayed or the sequence is altered.

Secondly, I would like to combine it with the data from second sheet
(â€śProcess 2â€ť, please see below) into a list.

I'm not very experienced Excel user, so I have no idea what this would
require. Please ask more question if my explanation below is not clear and I
will try to be more often online.

Cheers

"JMB" wrote:
One way is to use Excel's Autofilter (Data/Filter/Autofilter). Filter the
Finish Good column for "YES". Then copy the filtered list to a new sheet.

> Hello everyone
>
> I have a following problem:
>
> - Two processes, which are recorded on two different sheets
> - Some of the designs need to be worked on once, twice or several times
> before they are finished goods.
>
> SHEET 1 (Proces1)
> Design nr. Name Finish good Time finish
>
> 1 Name1 07:00
> 2 Name2 08:00
> 3 Name3 09:00
> 1 Name1 YES 10:00
> 4 Name4 11:00
> 2 Name2 YES 12:00
> 4 Name4 13:00
> 3 Name3 14:00
> 3 Name3 YES 15:00
> 4 Name4 YES 16:00
>
>
> SHEET 2 (Proces2)
> Design nr. Name Finish good Time finish
>
> 5 Name5 08:00
> 6 Name6 09:00
> 7 Name7 YES 10:00
> 6 Name6 11:00
> 8 Name8 12:00
> 6 Name6 YES 13:00
> 5 Name5 YES 14:00
> 8 Name8 15:00
> 8 Name8 YES 16:00
>
>
> What I would like to construct is third sheet which is updated automatically
> with the overview of times of finished goods. I made a very simple example
> above, where in reality the sheets contain many more designs and information
> which I would like to eliminate by making a new and simple list. Something
> that looks like this:
>
> 1 Name1 YES 10:00
> 7 Name7 YES 10:00
> 2 Name2 YES 12:00
> 6 Name6 YES 13:00
> 5 Name5 YES 14:00
> 3 Name3 YES 15:00
> 4 Name4 YES 16:00
> 8 Name8 YES 16:00
>
> The sequence of finished goods coming at the same time has no importance.
> The finish times are important in order to plan the processes to follow.
>
> If the function of combining data from two sheets into one list is two
> difficult to do, I could live with two separate lists for Proces1 and
> Proces2.
>
> The way I see it is to â€śsearchâ€ť for YES and copy the line or specific cells
> into a new arc, but I donâ€™t have an idea for how to do it.
>
> Any ideas are highly appreciated.
>
> Regards

### Excel Fomulas to gather data from multiple spreadsheets

I wonder if there is a formula I can use to gather data from different spreadsheets into one. I am trying to collect expense data from 2009 to 2011. from different departments. For example, I will have a summary on top and below are the different department expenses in details. all these data comes from different spreadsheets. Below is what I ma trying to collect. I am usin excel 2010

Expenses FY09 FY10 FY11 *FY12
Corporate 178,169.73 329,717 276,910 -
Labor 145,566.55 225,317 228,681 -
Travel 7,683.95 5,996 8,349 -
Transportation 495.24 1,534 1,010 -
Utilities 50.35 287 115.01 -
Printing (253.58) 3,158 177.29 -
Contracts 23,272.32 90,347 36,155 -
Training - 342 336.75 -
Supplies 1,354.89 2,650 2,092 -
Equipment - 86 (6.88) -
Interest - 23 10.14 -
J1 6,569 6,673 7,967
Labor 4,822 5,358 7,207
Travel 149 201 182
Transportation 15 8 (13)
Utilities - - -
Printing 5 - -
Contracts 1,493 948 545
Training - -
Supplies 85 158 46
Equipment - -
Interest 0.05 -
DHRS 104,497 119,288 114,377
Labor 90,371 103,005 103,895
Travel 5,331 1,331 3,307
Transportation 435 949 795
Utilities - - -
Printing (302) 3,066 20
Contracts 7,776 10,095 5,658
Training - -
Supplies 884 841 702
Equipment - -
Interest 0.09 0

### How to merge data from separate spreadsheets

I have a question retaining to merging data from two spreadsheets. I have two sheets that I am working from: Expanded and VCs (see images below). On my Expanded sheet, I wish to merge the city and state for each respective company, which is found on the VCs sheet. Note that each company may be listed from 0 to a large number of times. The entire list in the Expanded sheet is over 1500 entries. I wish to avoid a long and strenuous copy and paste session and hope someone can help!

(see images in post below)

I have tried methods using Consolidate, but have not had any success. I am currently working out of Excel 07.

### Merging data from two columns

I am trying to merge data from two columns into one cell, with one below the other. For example, I have "1234 Main St" in column "a" and "Box 202" in column "b". I need the end result to be:

1234 Main St
Box 202

in one cell.

### Compare data from two sheets in one workbook

I have a workbook with three sheets. "Comparison", "Last Year" & "This Year".

Each sheet will only have one calendar month of data in it (ie 1st Aug through to 31st Aug, 1 row per day)I would post the sheet here to view however I am new here and cant seem to find a link to attach a sheet.

I would like to import "Last Year" & "This Year" into "Comparison". The problem I am having is that I would like to import the 1st day of month of 2008 to "Comparison". Say for arguments sake that this day is a Friday for instance, I would like to import the first friday of "Last Year" and import it into "Comparison" into the cell above the imported "This Year" row. In the row below these two rows, I would like a row calculating the difference between the two days of data.

I would like this repeated so that "Comparison" is comparing each day from there on in for the whole months' data.ie, "Comparison" rows should be something like...
DateDayPULPULPDieselTotal Fuel03-Aug-07Friday1,381 7,746 4,799 13,926 01-Aug-08Friday2,834 13,662 13,191 29,687 Difference1,453 5,916 8,392 15,761 04-Aug-07Saturday943 5,883 2,767 9,593 02-Aug-08Saturday1,288 8,094 8,588 17,970 Difference345 2,211 5,821 8,377 05-Aug-07Sunday1,497 10,880 11,969 24,346 03-Aug-08Sunday780 6,022 5,143 11,945 Difference- 717 - 4,858 - 6,826 - 12,401

I want this to automatically import so that I can change the month to September and send out to the sites and it automatically compares the right days for whatever month I put in.

Thanks heaps again!

Matt

### I want to send data from one sheet to another

Hi

Does anyone know how to send data from a sheet in one workbook to another
workbook containing the same information?

I have a project that I'm doing and I have access to the master copy -
others have been allocated 'copies' of the master to work on. Is there anyway
that I can arrange for the information on the master to be updated when I
reload it or refresh it? There are two spreadsheets within the workbook that
both need updating - one that contains detailed information, the other that
contains who has taken the file, when it was taken and what stage it's at. I
have conditional formatting on one column which deals with the status of the
file which the user updates themselves.

Any ideas?

Thanks

### Loop to copy data from multiple workbooks into one

Hi,

I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.

Each report is named as 'Report to PMT from Vauxhall', 'Report to PMT from Ford', 'Report to PMT from Fait' etc etc. and the format of each report is exactly the same.

From the attachments you will see that I'm trying to copy the following from the Report to PMT from Vauxhall to Summary Report:

Report to PMT from Vauxhall Cell D11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell E11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell F11 to Cell D19 of the Summary Report
Report to PMT from Ford Cell D11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell E11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell F11 to Cell D20 of the Summary Report
etc. etc.

Here is my current code:

Private Sub GetData()
Dim LookupDate As Date

LookupDate = Range("C6").Value
GetWorkStreamData ThisWorkbook, LookupDate, "Vauxhall"
GetWorkStreamData ThisWorkbook, LookupDate, "Ford"
GetWorkStreamData ThisWorkbook, LookupDate, "Fiat"
GetWorkStreamData ThisWorkbook, LookupDate, "VW"
GetWorkStreamData ThisWorkbook, LookupDate, "Honda"
GetWorkStreamData ThisWorkbook, LookupDate, "Toyota"

End Sub

Private Sub GetWorkStreamData(wb As Workbook, LookupDate As Date, WorkStream As String)

Const ROOT_FOLDER As String = http://sharepoint.net/meetings/reports/
Dim LastRow As Long
Dim NextRow As Long

With wb.Sheets("WorkstreamReport")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator &
"Report to PMT from " & WorkStream & ".xls"

With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

Rows(2).Resize(LastRow - 1).Copy wb.Sheets("WorkstreamReport").Cells(NextRow, "A")
ActiveWorkbook.Close savechanges:=False

End Sub
Currently, this is finding the appropriate workbook to copy from, but doesnt copy anything into the summary report. Therefore, I suspect theres a big problem with the cells it is looking at and then the location of the cell it is looking to place the copied data???

Any ideas would be much appreciated.

Cheers,
Alex

### Macro to get data from multiple sheets in one workbook into a second workbook

I have two workbooks. One is the master inventory list consisting of five sheets. The second is an inventory summary consisting of menu sheet and a sheet for each room. When I click the room number the correct room sheet comes up. I cannot figure out a Macro or formula that will bring the correct data from the master list into the correct room. I have to use two workbooks because the master list is used by the school district and I cannot modify the format. The summary workbook is so I can see what is in each room. I only need from the master workbook columns A - G from the sheets named "Computers", "Printers" and Projectors, Scanners" transferred to the summary woorkbook. Any help anyone can give me?

### VBA 2 pull data from 2 tabs within same Worksheet

Hi

I need to create a VBA to auto pull data from 2 different tabs (one has the header information like; site number, manager name and et cetera and the other has the raw date) from the same spreadsheet and input it into another spreadsheet. The 1st set of code I wrote worked (bolded below) but the 2nd code I wrote (italicized) to pull info from other tab makes the VBA/Macro fail. I'm hoping this is a simple fix

Everything beneath this line is code, so, I did not add code tags. _________________________________________________________________

VB:

HSSESafetyQuestions()
Dim fso, f, fldnm As String, WB As Workbook, WS As Worksheet, r As Long
Dim ws2 As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

fldnm = "bp1lpris001commonHSSEWoR10k2005WoR10kHSSEVBA" 'Folder to loop through
Set WS = Workbooks("HSSE_WoR_10k_summary.xls").Sheets("HSSE Questions")

r = WS.Cells.Find(What:="*", LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1
Application.ScreenUpdating = False

[I]  For Each f In fso.GetFolder(fldnm).Files
If UCase(Right(f.Name, 3)) = "XLS" Then
Set WB = Workbooks.Open(f.Path)
Set ws2 = WB.Sheets("WOR Summary")
With WS.Rows(r)
.Columns("j") = ws2.Range("c3").Value
.Columns("k") = ws2.Range("c2").Value
.Columns("l") = ws2.Range("c5").Value
.Columns("m") = ws2.Range("c8").Value
.Columns("n") = ws2.Range("c9").Value
.Columns("o") = ws2.Range("c7").Value
.Columns("p") = ws2.Range("f3").Value
.Columns("q") = ws2.Range("f4").Value
.Columns("r") = ws2.Range("f5").Value
.Columns("s") = ws2.Range("f6").Value
.Columns("t") = ws2.Range("f7").Value
.Columns("u") = ws2.Range("f8").Value
.Columns("v") = ws2.Range("f9").Value
.Columns("w") = ws2.Range("f10").Value

End With
r = r + 1
End If
Next
Application.ScreenUpdating = True[/I]

For Each f In fso.GetFolder(fldnm).Files
If UCase(Right(f.Name, 3)) = "XLS" Then
Set WB = Workbooks.Open(f.Path)
Set ws2 = WB.Sheets("WOR Questionnaire")
With WS.Rows(r)
.Columns("x") = ws2.Range("D12").Value
.Columns("y") = ws2.Range("D21").Value
.Columns("z") = ws2.Range("D29").Value
.Columns("aa") = ws2.Range("D55").Value
.Columns("ab") = ws2.Range("D62").Value
.Columns("ac") = ws2.Range("D64").Value
.Columns("ae") = ws2.Range("D93").Value
.Columns("af") = ws2.Range("D95").Value
.Columns("ag") = ws2.Range("D98").Value
.Columns("ah") = ws2.Range("D99").Value
.Columns("ai") = ws2.Range("D100").Value
.Columns("aj") = ws2.Range("D101").Value
.Columns("ak") = ws2.Range("D103").Value
.Columns("al") = ws2.Range("D104").Value
.Columns("am") = ws2.Range("D105").Value
.Columns("an") = ws2.Range("D106").Value
.Columns("ao") = ws2.Range("D107").Value
.Columns("ap") = ws2.Range("D109").Value
.Columns("aq") = ws2.Range("D108").Value
.Columns("ar") = ws2.Range("D110").Value
.Columns("as") = ws2.Range("D111").Value
.Columns("at") = ws2.Range("D112").Value
.Columns("au") = ws2.Range("D114").Value
.Columns("av") = ws2.Range("D118").Value
.Columns("aw") = ws2.Range("D130").Value
.Columns("ax") = ws2.Range("D119").Value
.Columns("ay") = ws2.Range("D129").Value
.Columns("ba") = ws2.Range("D121").Value
.Columns("bb") = ws2.Range("D122").Value
.Columns("bc") = ws2.Range("D123").Value
.Columns("be") = ws2.Range("D125").Value
.Columns("bf") = ws2.Range("D126").Value
.Columns("bg") = ws2.Range("D127").Value
.Columns("bh") = ws2.Range("D128").Value
.Columns("bi") = ws2.Range("D134").Value
.Columns("bj") = ws2.Range("D147").Value
End With
r = r + 1
WB.SaveAs fldnm & "archive" & f.Name
WB.Close
f.Delete
End If
Next
Application.ScreenUpdating = True
End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

### Combining data from multiple spreadsheets to one

I want to combine data from multiple workbooks (one spreadsheet per workbook) to one spreadsheet. Data is only present in 4 columns and every spreadsheet's last row is a 'white space', this too has to be copied.

I found this code on the forum and modified it a bit.
When the macro is run, I get Clipboard popup asking for saving the copied information or deleting it. Is there any way to stop this popup or get around it. I did change the clipboard setting, but that did not help.

Also, I want the last row of every spreadsheet (White space) to be copied as well.

VB:

FindAndAppendFiles()

Dim fs As FileSearch
Dim NewWB As Workbook
Set fs = Application.FileSearch
With fs
.LookIn = "D:Temp"
.FileType = msoFileTypeExcelWorkbooks
End With
With fs
If .Execute() > 0 Then 'Workbook exists
For i = 1 To .FoundFiles.Count
If Not .FoundFiles(i) Like "*maintest_Append.xls" Then
Set NewWB = Workbooks.Open(.FoundFiles(i))
NewWB.Sheets(1).Range(NewWB.Sheets(1).Range("A1"), NewWB.Sheets(1).Range("D65536").End(xlUp)).Copy
ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0).PasteSpecial xlValues
NewWB.Close False
End If

Next
End If
End With

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Suggestions are appreciated. Thank you.

### How to match data from two spreadsheets

I have two spreadsheets with data. Sheet one has about 2,000 employee IDs on it and sheet two has about 300 employee IDs on it. I need to write a formula in sheet one to find which employee IDs from sheet one are also listed in sheet two.

Is there a filter I should use to do this or is there a formula? I tried using the formula =ISNUMBER(FIND(A3,(Sheet2!\$A2:\$A\$200))) but that didn't seem to work.

### Plotting data from two pivot tables on one chart

Hi,

I've been struggling to represent data from a spreadsheet of mine for a while now and have not found the answer through searching. Sadly the data I have is sensitive so I can't post the spreadsheet but hopefully I can explain it.

I record survey data from thousands of people. This data records their issues with a service along with demographic data and location they use the service. This is searched through several pivot tables.

What I need to display is the results for a specific location against the grand total so that I can see what one location suffers from a particular issue more than the grand total. (It is all expressed in percentage terms)

The best I have managed so far is to place two pivot tables side by side. One shows every response broken down by issue. The other shows the responses for each issue only at a certain location. Ideally I'd like to be able to plot these results on the same graph but I can't seem to do so.

Hopefully this is clear. If not please ask and I can provide further info.

Many thanks,

Alan

### VBA: Copying data from closed workbooks

Hi,

I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.

Each report is named as 'Report to PMT from Vauxhall we 1031', 'Report to PMT from Ford we 1031', 'Report to PMT from Fait we 1031' etc etc. and the format of each report is exactly the same.

I've pretty much found a way of copying the data that I want from each report into the summary report, however at the moment it goes about it by actually opening up each report before it copies the data, using workbooks.open.

Issues encountered here are that I am presented with a message box asking me to "update or Don't update" links to data sources within the workbook, and also another message box asking me a yes/no question regarding copying the data.

Is there a way of getting rid of workbooks.open and hence activesheet so that it doesnt open up every report before it copies the data?

Basically, can my VBA code (below) be altered so that I'm copying the data from closed workbooks rather than opening them up???

Private Sub GetData()
Dim LookupDate As Date

LookupDate = Range("C6").Value

GetWorkStreamData ThisWorkbook, LookupDate, "Vauxhall"
GetWorkStreamData ThisWorkbook, LookupDate, "Ford"
GetWorkStreamData ThisWorkbook, LookupDate, "Fiat"
GetWorkStreamData ThisWorkbook, LookupDate, "VW"
GetWorkStreamData ThisWorkbook, LookupDate, "Honda"
GetWorkStreamData ThisWorkbook, LookupDate, "Toyota"
End Sub

Function convertDate(dDate As Date) As String
convertDate = Format(dDate, "mmdd")
End Function

Private Sub GetWorkStreamData(wb As Workbook, LookupDate As Date, WorkStream As String)

Const ROOT_FOLDER As String = "http://sharepointsite.net/meetings/reports/"
Dim ReportDate As String

ReportDate = convertDate(LookupDate)

Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator &
"Report to PMT from " & WorkStream & " we " & ReportDate & ".xls"

Select Case WorkStream
Case "Vauxhall": ActiveSheet.Range("D11:Q11").copy
wb.Sheets("WorkstreamReport").Range("D19:Q19")
Case "Ford": ActiveSheet.Range("D11:Q11").copy
wb.Sheets("WorkstreamReport").Range("D20:Q20")
' etc etc
End Select

ActiveWorkbook.Close savechanges:=False

End Sub