Over 1 million tech questions and answers.

Solved: How to use this Macro in MS Excel (compare excel worksheets)

Q: Solved: How to use this Macro in MS Excel (compare excel worksheets)

Hi guys,

I have been on the lookout for a macro that will compare 2 worksheets within MS Excel and output the differences on a third sheet.

I am being directed to the URL below but cannot for the life of me work out how to implement this:

http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

There are two snippets of code which I will include here for convenience but not sure whether these should be pasted into Workbook Code (i.e. right-clicking Excel icon left of "File" - top-left), Worksheet Code (i.e. right-clicking worksheet tab) or inserting a new Module - or some other method. Is there anyone here that could guide me to how to implement this code?

Quote from webpage - "With the macro below it is possible to compare the content of two worksheets.
The result is displayed in a new workbook listing all cell differences. "

Code:
Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
Application.DisplayAlerts = False
While Worksheets.Count > 1
Worksheets(2).Delete
Wend
Application.DisplayAlerts = True
With ws1.UsedRange
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With ws2.UsedRange
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & Format(c / maxC, "0 %") & "..."
For r = 1 To maxR
cf1 = ""
cf2 = ""
On Error Resume Next
cf1 = ws1.Cells(r, c).FormulaLocal
cf2 = ws2.Cells(r, c).FormulaLocal
On Error GoTo 0
If cf1 <> cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(maxR, maxC))
.Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error GoTo 0
End With
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", vbInformation, _
"Compare " & ws1.Name & " with " & ws2.Name
End Sub

Quote from Webpage - "This example macro shows how to use the macro above: "

Code:
Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
End Sub

Thanks in advance,

DarkKman...

RELEVANCY SCORE 200
Preferred Solution: Solved: How to use this Macro in MS Excel (compare excel worksheets)

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: How to use this Macro in MS Excel (compare excel worksheets)

Apologies... I have sorted this... For those that might be looking at this for help here's what I missed:

These are both Sub's that should be created as a Module - both in the same module. I am just comparing worksheets within the Active Worksheet so just removed:

' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
Click to expand...

I was then able to run this from Macro's. All sorted.

Apologies again

Read other 1 answers
RELEVANCY SCORE 124

Is there a way to compare Excel 2002 worksheets and cross-reference them?
 

A:Solved: Excel - Compare Worksheets

Read other 9 answers
RELEVANCY SCORE 114.8

I have two Excel worksheets containing data. I want to know if there is a way of comparing the two to find which rows from worksheet 1 exist in worksheet 2.

Cheers in advance for any help.

Paul
 

A:Excel - Compare worksheets

This may be useful to you - it compares each cell between two work sheets- worked brilliantly when I used it for a weekly European Sales meeting
http://forums.techguy.org/business-applications/336809-solved-flagging-if-cell-s.html
 

Read other 1 answers
RELEVANCY SCORE 114

I have the following code:
Code:

Private Sub Workbook_Open()

Dim myCount
Dim i
Dim ws As Worksheet

Set ws = ActiveSheet

Application.ScreenUpdating = False

myCount = Application.Sheets.Count

Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount

ws.Unprotect "test"

ws.Range("A55:A58").FormulaHidden = True
ws.Range("B35").FormulaHidden = True
ws.Range("B43").FormulaHidden = True
ws.Range("B47").FormulaHidden = True
ws.Range("B50:B52").FormulaHidden = True
ws.Range("C35").FormulaHidden = True
ws.Range("C43").FormulaHidden = True
ws.Range("D49:E49").FormulaHidden = True
ws.Range("D55").FormulaHidden = True
ws.Range("E35").FormulaHidden = True
ws.Range("E38").FormulaHidden = True
ws.Range("E44").FormulaHidden = True
ws.Range("F52").FormulaHidden = True
ws.Range("F58").FormulaHidden = True
ws.Range("G4:G26").FormulaHidden = True
ws.Range("G49").FormulaHidden = True
ws.Range("J4:J26").FormulaHidden = True
ws.Range("J30:J31").FormulaHidden = True
ws.Range("J38:J39").FormulaHidden = True

ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1

ws.Range("... Read more

A:Solved: Excel Macro That Applies To All Worksheets

I was able to fix the issue by modifying the code.
Code:

Private Sub Workbook_Open()

Dim myCount
Dim i
Dim ws As Worksheet


Application.ScreenUpdating = False

myCount = Application.Sheets.Count

Sheets(1).Select 'This line of code selects the 1st sheet

For i = 1 To myCount

[B]Set ws = ActiveSheet[/B]

ws.Unprotect "test"

ws.Range("A55:A58").FormulaHidden = True
ws.Range("B35").FormulaHidden = True
ws.Range("B43").FormulaHidden = True
ws.Range("B47").FormulaHidden = True
ws.Range("B50:B52").FormulaHidden = True
ws.Range("C35").FormulaHidden = True
ws.Range("C43").FormulaHidden = True
ws.Range("D49:E49").FormulaHidden = True
[B]ws.Range("D55:E55").FormulaHidden = True[/B]
ws.Range("E35").FormulaHidden = True
ws.Range("E38").FormulaHidden = True
ws.Range("E44").FormulaHidden = True
ws.Range("F52").FormulaHidden = True
ws.Range("F58").FormulaHidden = True
ws.Range("G4:G26").FormulaHidden = True
ws.Range("G49").FormulaHidden = True
ws.Range("J4:J26").FormulaHidden = True
ws.Range("J30:J31").FormulaHidden = True
ws.Range("J38:J39").FormulaHidden = True

ActiveWindow.ScrollColumn = 1
A... Read more

Read other 1 answers
RELEVANCY SCORE 113.6

OK. I know this may have been covered in another post but here is what I've got. I generated two reports from my system that have results for year 2010 and YTD 2011. The columns are all the same. I want to combine the two worksheets to show comparison between the two years. The two criteria to break down the results are Name and Product. There will be many of these that are common between the two worksheets so I just want to combine those and show the 2010 to 2011 comparison. And I want any unique Name/Products listed also where there might only be that Name/Product in either only 2010 or 2011. I have attached a sample. Thanks in advance for any help!
 

A:Compare Excel worksheets 2010

This is pretty easy. All you need to do is copy the data from 1 sheet to another and make a pivot table on the data. You can probably use the wizard to figure out how to set up the table up to display what you are after... but if you need help, do a simple google search to understand pivot tables better.... If you are still stuck, please reach out to me
 

Read other 2 answers
RELEVANCY SCORE 112.8

I'm in a bit of a pickle.

I have an excel document with a summary page ("Tracker"). This worksheet has a table in it with a whole heap of customer information, and at the end there are columns for revenue summaries.

Every month I run a report that I add in a new worksheet which is called "[Relevant Month] Revenue Report", and then I do a vlookup to transfer the revenue and product information in columns "C" and "D" of the report into the relevant month columns in the "Tracker". This has been working well for me, but i'll be handing this role over soon so I was wondering if there was a macro that I could use that would do this vlookup automatically for the relevant month?

The information in the reports is not in any particular order so i can't do a simple copy/paste into my "Tracker" summary.

Any help would be greatly appreciated!!
 

A:Solved: Excel Macro to transfer data from several worksheets into one

Read other 8 answers
RELEVANCY SCORE 112.4

I have 2 worksheets. the main worksheet that I want to have the conditional formatting on has a connection to an Access database. I want to take this worksheet and have it compare to another worksheet in the same workbook. I am comparing cell to cell. Example - D5 on each page. But I have many cells to compare. I would like to have the highlights happen on the connected worksheet, but I am not sure if that is possible. There are 13 columns and 880 rows to conditional format

I have created a 3rd worksheet that uses =ISNA(MATCH(Summary!E5,'Last Summary'!E5,0)) to get some true false results. but I still cannot seem to get it incorporate into the main connected worksheet.
 

Read other answers
RELEVANCY SCORE 111.6

Hello,

I'm trying to do some statistical work and i need to compare some values that i will explain in the next rows:

I have let's say this 6 numbers, and a table with 200 rows x 6 columns, each cell containing a byte value.
1 2 3 4 5 6

I need to know if any minimum 3 of my six numbers match one row, so basically there are 200 compare operations to do.

So, for this example we have in left nr for compare, midle - the results and on right the nr to compare with
---------------- Result
1 2 3 4 5 6------1 1 1 1-----1 2 3 4 5 7
1 2 3 9 10 15---------1-----2 3 5 6 7 8
----------------------------1 2 3 4 5 6
----------------------------4 5 6 7 8 9. The first result is 1 for 6 nr, 1 for 5 nr, 1 for 4 nr and 1 for 3 nr, and the second result is 1 for 3 nr.

I hope you will understand my request

Edit:

Here is a Borland Pascal code for what i need. Hope it will be useful.

repeat
for i:=1 to 6 do
read (f,a); readln (f);
repeat
Assign (g,'var.txt'); reset (g);
for i:=1 to 6 do
read (g,v); readln (g);
c:=0;
for i:=1 to 6 do
for j:=1 to 6 do
if a = v[j] then c:=c+1;

if c >= 3 then
if c = 3 then c4:=c4+1
else if c = 4 then c3:=c3+1
else if c = 5 then c2:=c2+1
else if c = 6 then c1:=c1+1;
until eof (g);
close (g);

writeln (out, "6 nr ',c1,'; 5 nr ',c2,'; 4 nr ',c3,'; 3 nr - ',c4);
c1:=0; c2:=0; c3:=0; c4:=0;

until eof (f);
 

A:Solved: How to compare 2 tables in excel with macro

 

Read other 2 answers
RELEVANCY SCORE 111.6

I have a 2 spread sheets which it has some employee information(Sample),
I need to compare those 2 sheets,
Sheet 1: data is taken from database and it has 12 columns where column A has unique values no repeatation (Employee ID)
and number of rows are 10 (Example there may be more than 10000)
Sheet 2: Data is taken from Application and it has 12 columns where again it has Employee ID as Unique key, but Number of rows are 12
If I compare excel Row cell to cell then I am getting wrong results as in sheet 2 it has 2 extra values and they are coming somewhere in between,

I want a solution to compare these 2 files,
Below is what i am thinking to do the macro,
Steps to do:
1: Open Sheet 2
2: Temp = Value(Sheet2.Cell(A2))
3: Open Sheet 1:
4; Search Temp in Sheet1.Column A
If Result is True Then temp1=Sheet1.Row Number
Compare Sheet2. Row 2 Value = Sheet1.Row(Temp1)
If all data matches highlight green color
If any data missmatches highlight in red color
If result found = False
Then copy Temp to Sheet3

Any one can help me,
 

A:Solved: Excel Macro Need help to search and compare

Read other 14 answers
RELEVANCY SCORE 103.6

Hi all,

the title of the post only gives part of the story.

What I'm looking for is a VBA script to attach to a button. When the user clicks the button I need each of the worksheets within the workbook to be checked for an entry (say in cell A8), and to only print the ones that have a value (the value will be different on each sheet, so another way to look at it is a check that does not print worksheets with no value in that cell).

For example: The workbook has 5 worksheets to check (A, B, C, D and E). A8 is completed on sheet A, D and E. When the user clicks the button, sheets A, D and E are the only ones that should print.

Hope I have been clear enough with what I need!

Thanks in advance for any help you can provide.
 

A:Excel - Need macro to print multiple worksheets

Hi welcome to the forum.

I have a piece of code which I found and have been using since.
It's in the sample sheet and you will have to workout some things for yourself like the filetr for the sheets you wish to print.

There is a simple explanation in Sheet3 and 2 buttons which can help you on the way.

The code is as is.

Happy coding

PS excuse my mispelling greased
 

Read other 3 answers
RELEVANCY SCORE 102.4

Hello, would you please assist me in solving this problem: I have an Excel object (Excel.Sheet.1) in MS Word document. I'm trying to write a Visual Basic macro for MS Word to change the data in cell A1 of this table. How can I access the cell? I can only activate the table for example like this: ActiveDocument.Fields(1).OLEFormat.Activate - this leads to the same result as double clicking on the table object...

I know how to access MS Word tables, but the problem here is that I have to work with an Excel table.. I suppose only an Excel macro can access it and modify it... And if I write an excel macro to do so, how can I call it from my MS word macro script? Does solving of this problem involve OLE, DDE...? Maybe I should run the Excel application to modify the table and then close it.. so far I have no success in doing that.. this has to be implemented in a single MS word macro.

Thank you!
 

A:Accessing excel worksheets in a word document (VB macro).

Read other 9 answers
RELEVANCY SCORE 92.8

Hello again,

I have a word doc that has a form in it. The form may have any number of fields (boxes) to type text answers into, or be a drop down list to select an answer from. I need an excel macro that extracts all data from the form, but with every five fields starting a new row in excel. So all data from the form will appear within 5 columns in an excel spreadsheet.

field 1 field 2 field 3 field 4 field 5
field 6 field 7 field 8 field 9 field 10
etc

Is that possible? I have had no luck to do it myself.

Sample form attached, thank you
____________________________
 

A:Solved: Excel macro to extract Word form data into Excel

Read other 7 answers
RELEVANCY SCORE 92.8

Iím using Windows 7 and I used a macro which worked totally fine under Excel 2003. Now, under Excel 2007 it does not run anymore but displays the following error message:

"Run-time error '445':
Object doesn't support this action.

The function of the macro is to open up all other Excel spreadsheets located in the same folder and copy data out of them into the spreadsheet in which the macro is stored.

When I click on "Debug" the Visual Basic Editor highlights the row which says: Set FilSrch = Application.FileSearch

Can you help me with this? I've searched for a solution for a long time but could not find anything which worked.

The following is the start of the macro code:

Sub UpdateTable()
Dim X As Range, I As Integer, J As Integer, RecNo As Integer, CopyVal As Variant
Dim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As Range
Dim ServRange As Range, SrcOpen As Boolean, SourceName As String
Dim FilSrch As Object, MyFilArray() As String

'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"
'Exit Sub
'Sheets("LookUp").[a12] = Now
With Application
.ScreenUpdating = False
.StatusBar = "Counting source files ..."
End With
Set FilSrch = Application.FileSearch
With FilSrch
.NewSearch
.LookIn = ActiveWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute > 0 Then
ReDim MyFilArray(.FoundFiles.Count)
For I = 1 ... Read more

A:Solved: Excel 2003 Macro Doesn't Run in Excel 2007 - Help Requested

Read other 7 answers
RELEVANCY SCORE 92.8

I am trying to create a code that will autofilter data on an Excel worksheet. The code works fine to filter the data, but it does not hide the AutoFilter Arrows.

Below is the code I am using. What (if anything) is wrong with the code below?
Code:

ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>", Visibledropdown:=False
 

A:Solved: Excel 2007 Macro -> Hide Excel AutoFilter Arrows

I noticed that the code above (in Post #1) that the AutoFilter Arrows were beinf truned off on Field 2, but none of the others.

I have played around, and done some research to come up with the code below:
Code:

Dim c As Range

For Each c In ActiveSheet.Range("A1:D1")
ActiveSheet.Range("A1:D500").AutoFilter Field:=2, Criteria1:="<>"
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next

Does anyone have a better solution?
 

Read other 1 answers
RELEVANCY SCORE 89.6

I have an Excel workbook that contains two worksheets. There are formulas on worksheet two that brings data from worksheet1.

I have code that runs when a button is pressed to strike through data on worksheet one. Does anyone know a way to strike through the respective data on worksheet 2 as well?
 

A:Solved: Excel Worksheets

Read other 8 answers
RELEVANCY SCORE 89.6

I need help..

I have two worksheets (ABLE and BETA) and would like to search for duplicate data in colume A in both worksheets. If there is a match, copy data in worksheet ABLE row E to worksheet BETA row E. Also if it finds more than one of the same duplicates create a new row and copy the data from worksheet ABLE row A and E to BETA row A and E.
Thanks for your help.
 

A:Solved: Excel Two Worksheets

Read other 12 answers
RELEVANCY SCORE 87.6

I recently took responsibility of a large amount of data in several Excel 2010 workbooks. The previous person was saving a copy of each workbook that alphabetized the worksheets. There are no macros in place. I know she was doing it, I just can't figure out how. Any solutions?
 

A:Solved: Alphabetizing Worksheets in Excel 2010

Read other 6 answers
RELEVANCY SCORE 87.6

How can I change the format in one excel 2007 file so that all the sheets are in landscape format? I would like this to be for only one particular file. Thanks.
 

A:Solved: Format Worksheets in Excel 2007

Read other 16 answers
RELEVANCY SCORE 87.6

I need to move data from a second worksheet to the first. See attached example. The second worksheet will be changing weekly (the real data will be about 18000 records), so I'd like the formulas to be in place with minimum intervention. I need to pull the AgentSet off of the second worksheet (ServerDump) and put it in Customer Form (Type of Agent). I would like to say 'BaselineAgent' if it's true. I've tried different formulas and think I should be using vlookup but can't get it to work. Any suggestions or examples of coding I could use? Thanks!
 

A:Solved: Excel - Match and copy between two worksheets

Read other 12 answers
RELEVANCY SCORE 87.6

My company uses Excel for our timesheets. In the same file, I have 12 worksheets, one for each month. I want to add a 13th worksheet so I can automatically tally my vacation days.

Whenever I use a vacation day, it gets added as 8 hours on that day. Each month tallies up the vacation days used for that month in the same cell coordinate, Row 55/Column AJ.

I can't figure out how to create an equation that spans multiple worksheets. Can someone tell me the equation that I need that does the following computation?

I need it to add up all the numbers for each of the 12 monthly worksheets in cell 55AJ and then divide that total by 8 to give me the total vacation days currently used.

or

at least just sum up all the numbers for cell 55AJ from the 12 worksheets. I can easily divide that number to get the day breakdowns.

Thanks in advance.
 

A:Solved: Help with an Excel Equation over mult. worksheets

Read other 13 answers
RELEVANCY SCORE 86.8

Hi,

Hopefully one of you delightful people can help me with a small problem I'm having pulling together data from multiple reports... I have manually combined 4 separate reports into 1 workbook with 4 worksheets (1 for each report). Each row represents a separate job.

The 1st worksheet is an export from our own Access database, the 3 others come from external reports.

Whilst each report/worksheet contains different sets of data, 1 column in each worksheet contains &#8216;CSR&#8217; or &#8216;Site ID&#8217; numbers. These numbers can be used to match the jobs - however a) not every worksheet has every number b) some numbers may appear more than once on the same worksheet (these are essentially returns to the same job).

The 1st worksheet also has a column with OPID numbers (Column A), these are unique numbers that we have paired to a CSR or Site ID number.

I want to compare the numbers in the CSR column of the 1st worksheet (Column B of Sheet 1) with the CSR columns in the other worksheets and where there is a match, copy/add the corresponding OPID number to a blank column in each worksheet.

What would be the best way to do this?

Thanks in advance for any help and advice offered!
 

A:Solved: Comparing/Matching data across in Excel worksheets & more

Read other 9 answers
RELEVANCY SCORE 86.8

I need to make two versions of the same pivot table. One groups something by month, one groups it by every three months. These need to be in the same worksheet.

My Month column sits in the Row grouping. When I group by 3 months in Pivot Worksheet A, it groups by three months in Worksheet B. If I then go back and ungroup Worksheet B, it goes and ungroups worksheet A. I want A to be grouped and B to be ungrouped, but they are being linked by Excel 2003.

Is there any way to get around this?
 

A:Solved: Decouple AutoGrouping between Worksheets in Excel Pivot

Read other 6 answers
RELEVANCY SCORE 86.8

Hi All,

What I'm trying to do is type lists in Excel (using the 2007 version) so that an amount can be summed in separate worksheets and then the list to be consolidated into another worksheet so all the items can be seen at a glance.

I would also like to make it so that when the data changes in one worksheet, the consolidated view updates accordingly.

An example would be for say a shopping list where the items are broken down into their respective storage areas. So there would be a worksheet for Fridge, Freezer, Top Cupboard, Bottom Cupboard etc.

Each of these would contain a number in Column A and then the name of the item in Column B, with each item having its own row.
E.g. 5 Apples, followed by 6 Oranges on the next row.
There would also be a =SUM function on each worksheet to total the items on that sheet.

Once all the data was entered on the separate sheets the consolidated data would appear in the format below (commas separate column data):

Name of Place (Fridge)
Data E.g. 3, 6pts of Milk
10, Eggs
//1 Blank Row
Name of next place (Freezer)
Data E.g. 3, Packs of Bread
2, Ready Meals
//1 Blank Row
Name of next place etc.

Hopefully this consolidated view would also update so that if for instance "1, Butter" was added under "10, Eggs" then the "1, Butter" would not go into the blank row but would add itself above the blank row on its own new row.

I'm sure that this has to be possible just I lack the know-how
Hopefully I can g... Read more

A:Solved: Consolidating data from multiple worksheets in Excel?

Read other 10 answers
RELEVANCY SCORE 86.8

I have a workbook to keep track of total and monthly sales. Worksheet 1 is Total sales, worksheets 2 - 13 are monthly sales.
If I get a new customer and want to add the record I currently have to add it to every worksheet manually.
I want to be able to add a new row to the total sales and it be automatically added to the other 12 worksheets.
I have tried selecting all worksheets and right clicking but the add new row button is faded out and won't let me click it.
Help?
 

A:Solved: Adding rows to multiple Excel worksheets

Worked it out. I had the data in tables. Converted back to range and it is working fine.
Cheers
 

Read other 1 answers
RELEVANCY SCORE 86.8

I have an Excel program that starts at worksheet A, then depending on the users' responses could navigate to B, then C, then D or could go from A to D to B, etc.

I would like code that would navigate to previous sheets in the order in which they were activated by users. For example, if the user navigates A -> C -> D -> B, I would like code that could take the user back from B -> D -> C -> A.

The number of movements from sheet to sheet forward and backward could be quite large so I'm thinking that I need code that stores the sheet names in the order in which they were activated, then calls on them in reverse order. I've found code in this forum which navigates back but it goes only back one step (http://forums.techguy.org/business-applications/546347-excel-macro-previous-worksheet-easy.html).

Any help would be much appreciated. Otherwise I am forced to write code with a lot of if/then statements which quickly becomes unmanageable.

Thanking you in advance.
 

A:Solved: Navigating to previously activated Excel Worksheets

Read other 16 answers
RELEVANCY SCORE 86

I need to compare 2 ranges to ensure they are referring to the same item.

Code:

dim cell1 as range
dim cell2 as range
set cell1 = sheet1.range("A1")
set cell2 = sheet1.range("A1")
msgbox cell1 is cell2

The above msgbox is supposed to return true, but it didn't.
Any other alternative?
 

A:Solved: Excel Range compare

Read other 9 answers
RELEVANCY SCORE 86

Hi,

There is probably a really simple solution to this....but let me first define the problem..

I work in a maintanence tracking department. Every week I am given an excel spreadsheet with hundreds of jobs, each with a unique identification number arranged in columns. during that week as work is done I can generate another excel spreadsheet with all the job numbers that have been completed and I have to sort through the first list and tick them off as 'done'. I usually use the 'find' tool in Excel - but I am only able to find one number at a time.

Ideally I would like to be able to copy and paste a whole list of numbers into 'find' and get Excel to highlight all those jobs that have been completed.

Is this possible - do I need a macro? Help - cos it is taking up valuable hours of my life!!

Cheers,

Chris.
 

A:Solved: Excel data compare help!

Read other 16 answers
RELEVANCY SCORE 85.6

I have a large worksheet that I have a filter on so I can filter by certain columns. I make my changes and then need to print out only that filtered information.
I have most of my work done, and printed, but now I am on a worksheet where I have done the same thing from the beginning and the newly filtered data will not print like the other filtered data. It scrunches the data to the left of the page and it is so small that it cannot be read.
The theme font and size is set just like the rest are set but I cannot get it to print out correctly. (so are the margins and page set up). Everything on this filtered sheet is set up exactly like the prior sheets that printed perfectly.
Thanks for your help.
 

A:Solved: Excel 2007 printing filtered worksheets problem

found the answer- needed to change the pages from 1-1 to 1-3
 

Read other 1 answers
RELEVANCY SCORE 85.2

Hi,

I'm looking to use a workbook that I type an employee number in to and it'll automatically pull the name from another workbook and add it to the following cell.

In other words I have a database of names and employee numbers. The other is a form, but I want it to automatically add the name when I type the employee number. How can I do that?

Thanks
 

A:Solved: excel compare/retreive data

Read other 7 answers
RELEVANCY SCORE 85.2

Help,

I've seen several examples of Compare & Calculate, however I have a twist that has stumped me. I have two separate files based on an "as of data date" that I want to compare and then calculate the delta between two numbers and leave the result in the current working file.

The twist is that the current years working file may not have all the rows that are in the previous years file and may also have new ones. The rows represent accounts. An account that is paid to zero during the current year will not be in the current years report. A new account will appear in the the current years file, but not the previous years file.

I need a result that shows the change in the account balance even if the current year doesn't have a row representing an account paid to zero. So the result shows all of the accounts that appear in both reports as though both reports had been consolidated and then shows the change in value of the account balance, either positive or negative, as compared to the previous year.

I've attached a file that uses worksheets (named 2010, 2011 and Delta) as an example. Note accounts #2, #4 and #5 paid to zero and do not appear in "2011", yet appear in "Delta" with a negative number representing the reduction in balance between "2010" and "2011".

Thanks for any help you may be able to provide . . .
 

A:Solved: Excel 2007 - Compare / Calculate

Read other 16 answers
RELEVANCY SCORE 83.6

Hi,

I was wondering if anyone could help me. I need to extract data from multiple excel files into a new excel file and sum the values. The data comes from excel files that are made from a template so the data is always in the same place or same cells. For example, i have data saved daily in excel files with the following naming convention 080204, 080304, 080404,..... and so on. What i would like to do is have a macro that will create a week ending sheet by importing and summing the data from that week.

Thanks for reading!
 

A:excel macro for copying and pasting data from multiple excel files

thanks for all those who read!!!! let me know if anyone needs the code!
 

Read other 1 answers
RELEVANCY SCORE 83.6

Hello,

I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

Notes:
the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.
Mzz
 

A:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?
 

Read other 1 answers
RELEVANCY SCORE 83.2

Hi,
I want to merge two excel files into a 3rd one.

The data are in the files Book1, Book2 and the result in Book3.

I know that i can use VLookup to do the above. But what i have pasted is just an example. The real excel files contains about 9 columns with at many repeating row!!!

Any help will be much appreciated.
Thx
 

A:Excel - macro to merge 2 excel files using a common column

Hi welcome to the board. How are you going to treat the duble items, If you want to merge 2 files adn duplication occurs here must be one cell whihc is common to let's say Book1 and Book2.

Do you have a common value? Index? Whatever you call it?
 

Read other 3 answers
RELEVANCY SCORE 83.2

I'm working on a Bill of Material creation automation project that requires some expertise in VBA, and I have none.
The objective is to run a macro from an excel spreadsheet called "PGE BOM", to do the following:
1) Go to the folder "C:\Documents and Settings\Desktop\Auto Project"
2) Find all the .mdb databases in this folder
3) Find "HistoricalMaterialItemsAll" table in EACH of those databases in step 2, and import the data from the columns listed below into PGE BOM.xls's columns C through G:
DrawingNumber
ItemNumber
Quantity
PgeCode
Description
The following is a VBA code that my friend had written in Excel 2007. Unfortunately I have an older version (2000) and the code does not seem to be compatible with Excel 2000.
Sub ImportAccessData()
Stop
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
sRow = 2
bFile = False
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath)
strFlNm = Dir
If strFlNm = "" Then bFile = False
Loop
End Sub
Sub GetData(fl)
Stop
strSQL = "Select HistoricalMaterialItemsAll.* From HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, xlCmdTable
Set WB = Application.ActiveWorkbook
Set WS = Applicatio... Read more

A:Excel Macro - VBA code to import access data to excel

Hello, and welcome to the board!

When posting code, please use CODE tags, which extremely helps with readability.

Perhaps you could try using the code constant???...
Code:

Option Explicit

Sub ImportAccessData()
Dim dPath As String, sFile As String, strSrch As String
Dim TargetWB As Workbook, TargetWS As Worksheet
Dim sRow As Long, bFile As Boolean, strFlNm As String, strPath As String
dPath = "C:\Documents and Settings\Desktop\Auto Project\"
sFile = "*.MDB"
strSrch = dPath & sFile
sRow = 2
bFile = False
Set TargetWB = Application.ActiveWorkbook
Set TargetWS = TargetWB.ActiveSheet
If Dir(strSrch) <> "" Then
strFlNm = Dir(strSrch)
bFile = True
End If
Do Until bFile = False
strPath = dPath & strFlNm
Call GetData(strPath, TargetWB, TargetWS)
strFlNm = Dir()
If strFlNm = "" Then bFile = False
Loop
End Sub

Sub GetData(fl As String, WB As Workbook, ws As Worksheet)
Dim strSQL As String, iRow As Long, sRow As Long
strSQL = "SELECT HistoricalMaterialItemsAll.* FROM HistoricalMaterialItemsAll"
Workbooks.OpenDatabase fl, strSQL, 3 'xlCmdTable
iRow = 2
Do Until ws.Cells(iRow, 1) = ""
ws.Cells(sRow, 7) = ws.Cells(iRow, 5) 'Get the Description
iRow = iRow + 1
sRow = sRow + 1
Loop
Application.DisplayAlerts = False
WB.... Read more

Read other 1 answers
RELEVANCY SCORE 82.4

I run a weekly excel 2007 macro that converts a spreadsheet to a Word document, but it stops after row 29 of data. Any spreadsheet with 29 rows or less works fine. If I have 34 rows of data, the macro hangs. See below for script. Any help would be appreciated.

Public Sub AddControls(WrdApp As Word.Application, CurRow As Integer)
Dim OptChecked As Boolean
Dim GrpName As String
GrpName = "Grp" & CurRow
'Calculate the colors based on the cell information.
CurRange = "F" & CurRow & ".." & "F" & CurRow
Range(CurRange).Select

If InStr(1, UCase(ActiveCell.Text), "X SRM") > 0 Then 'SRM
OptChecked = True
Else
OptChecked = False
End If

SetCellBG WrdApp, True
WrdApp.Selection.TypeText Text:="Governance:" & vbCrLf

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "SRM"
.Font.Name = "Arial"
.Font.Size = 8
.Value = OptChecked
End With

If InStr(1, UCase(ActiveCell.Text), "X PPO") > 0 Then ' PPO
OptChecked = True
Else
OptChecked = False
End If

WrdApp.Selection.Collapse Direction:=wdCollapseEnd
WrdApp.Selection.TypeParagraph

Set RadioObj = WrdApp.Selection.InlineShapes.AddOLEControl("Forms.OptionButton.1")
With RadioObj.OLEFormat.Object
.GroupName = GrpName
.Caption = "PPO"
.Font.Name = "Arial"
.Font.Size = 8
.Value = O... Read more

A:Excel Macro - converting excel to word doc - stops after row 29

Read other 13 answers
RELEVANCY SCORE 82.4

Hi, I have created an excel macro that runs sensitivities in my model. The excel button runs 5 different cases.

The macro runs fine and does as I want it to.

Once the macro has run, and I go to edit a cell and hit "F2" excel crashes. This only seems to happen in certain cells and not all of the time.

Does anyone have experience with similar questions?

Thanks
 

A:Excel Macro runs fine..then excel crashes

Read other 11 answers
RELEVANCY SCORE 82.4

Does anyone know how to run Excel 2003 print macro on Excel 2007 or higher? When I tried running my 2003 macro, the VBA editor pops up w dialogue box, " Run-time error '1004': Method 'ActivePrinter' of object'_Application' failed ".

This is my code: Application.ActivePrinter = "Epson LQ-300 ESC/P 2 on USB003"

Can anyone help?

Thanks!
 

A:How to run Excel 2003 print macro on Excel 2007?

Welcome to TSG hope we can help you and you can help others.

I'm not an Excel macro guy but you code looks reasonable. What happens if you run this. I suspect printer name is a little off.

MsgBox "The name of the active printer is " & Application.ActivePrinter
 

Read other 1 answers
RELEVANCY SCORE 80.8

I have an equation that I need help with putting into an excel Macro. The equation is as follows:
=IF(AND(ISBLANK(E2),ISNA(F2)),"XXXXXX",IF(AND(ISBLANK(E2),ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNUMBER(F2)),"ASSIGN LAN ID",IF(AND(E2="XXXXXX",ISNA(F2)),E2,IF(AND(OR(ISBLANK(E2)="FALSE",E2<>"000000",E2<>"111111",E2<>"999999",E2<>"IOIOIO",E2<>"XXXXXX"),OR(ISNUMBER(F2),ISNA(F2))),E2,"No Criteria Met")))))

I need this put into a macro as either an if then else statement or a case statement. If anyone could help me out i would appreciate it
 

A:Excel Equation converted to an excel macro

Not sure if this will work for you or not, untested...
Code:
dim e as range, f as range, c as range
set e = range("E2"): set f = range("F2"): set c = activecell
if e = "" and worksheetfunction.isna(f) then
c.value = "XXXXXX"
elseif e = "" and isnumeric(f.value) then
c.value = "AS SIGN LAN ID"
elseif e = "XXXXXX" and isnumeric(f.value) then
c.value = "ASSIGN LAN ID"
elseif e = "XXXXXX" and worksheetfunction.isna(f) then
c.value = e.value
elseif (e <> "" or e <> "0000 00" or e <> "111111" or e <> "999999" or e <> "IOIOIO" or e <> "XXXXXX") and (isnumeric(f) or worksheetfunction.isna(f)) then
c.value = e.value
else
c.value = "No Criteria Met"
end if
HTH
 

Read other 3 answers
RELEVANCY SCORE 80.8

Does anyone know the best way to setup a Macro in Excel 2007 that will work in Excel 2003 as well?

I have an Excel 2007 workbook that I am using a macro to change the "Fill Color" and "Font Color" of cells. However, I will get everything working in Excel 2007, but when I open the workook in Excel 2003 I get many debug errors.

If I change the code in Excel 2003, and then re-open in Excel 2007 I get debug errors.

There must be a way to setup teh workbook to work on both Excel 2007 & 2003.
 

Read other answers
RELEVANCY SCORE 80.8

Excel '07 won't open an "excel 4.0" macro (i.e. *.xlm file). I am one of a few users at work using excel 2007, I believe this file was made with excel 2000 or similar. When I had excel 2000 it opened fine, it's a common file on the network that everyone should be able to access, and everyone with excel 2000 can easily. So I know it's not the file. When I try and open it with excel 2007 I get that stupid error about the file being different to the extension.

I've tried registry edits and workarounds after scowering the net but nothing seems to fix it. All it does is display the error, then opens excel in the background without even a new worksheet. Excel isn't hanging, it just does nothing. Any ideas?
 

A:excel 2007 won't open excel 4.0 macro

XLM was long ago replaced by VBA (with Excel 5 in 1993) and MS no longer really supports it (although I think they claim that they do, kinda, sorta.) So the only thing you can do, I think, is to make a copy of the xlm file and convert the xlm code in the copied file to VBA. Fortunately, I found a link that tells you how to do that:
http://blogs.msdn.com/b/excel/archive/2010/02/16/migrating-excel-4-macros-to-vba.aspx
(Look at the link at the bottom, too. But you will need to download that to an older PC, possibly.)
Now, assuming all goes well, this will give you two versions of the same file, but if everyone else has Excel 2000 (or later) then they will all be able to use the new VBA-based file, so you won't end up with two files that would lead to confusion.
Still, it looks to be a lot of work, so good luck!

On a final note, if you need this file done in VBA and you can't figure out how to redo it, make a post describing your needs - there are some amazing VBA coders on here.
 

Read other 1 answers
RELEVANCY SCORE 80.8

A macro I wrote in VBA for Excel 2004 won't run in Excel 2011 (alas...). Upon opening the spreadsheet an "on open" routine runs, and bombs out on the following command:
Code:
Set cmdbar = Application.CommandBars.Add _
(Name:="Sort", _
Position:=msoBarTop, _
MenuBar:=False, _
temporary:=True) 'msobartop docks the menu at the top

Does anyone know if the VBA structure ("CommandBars.Add") has been removed or altered?

Is there another way to accomplish the same thing in 2011? I want to create a custom command bar (I populate it later in the module).
 

Read other answers
RELEVANCY SCORE 80

How do you paste a cell or create a link from a particular cell in one worksheet to a particular cell in another?
Thanks,

Harold
 

A:Excel Worksheets

Read other 8 answers
RELEVANCY SCORE 80

I am new at creating macros and am running into a little problem. I'm not really sure it can be done. I know how to add sheets for daily spreadsheet for June's 30 days, but I don't want to change the date on every sheet. Is it possible to place 6/1/2009 on the first sheet and have it automatically have it add a day on the second sheet then another on the third sheet on down to 6/30/2009 on the last sheet without having to type it on each sheet?
 

A:Excel Worksheets

Like this? (I did this on a button click, but can be run as a macro):

Private Sub CommandButton1_Click()
Worksheets.Add After:=Sheets(Sheets.Count)
Worksheets(Sheets.Count).Name = "Sheet" & Sheets.Count
Worksheets("Sheet" & Sheets.Count).Cells(1, 1).Value = Format(Worksheets((Sheets.Count) - 1).Cells(1, 1).Value + 1, "Short Date")
End Sub
 

Read other 3 answers
RELEVANCY SCORE 78.8

Hi, everyone!
I have monthly worksheets, with a client and billable hours matrix. (no more than 20 clients, about 40 billing functions).
It was my bright idea to have a cumulative client history page, which totals up the hours per client.
But it's only April and my formula is HUGE! It covers up half of the cumulative history worksheet.
Am I doing this the right way?
E.g.,
I start with the cell that has Smith/Record Review, and I go:
= sum (then I click the total box for January's sheet, then February's sheet, then March's sheet, then April's sheet), and it ends up with a huge formula like 'C:My Documents\ImportantBillings\JanuaryBillings.xls then some exclamation marks, etc. And, I think I also have to change to absolute references.
It's a lot of work. I dread getting to May. Is there any easier way?
Many thanks, in advance!
allison
 

A:Excel - Multiple Worksheets

Hmmm...

Suppose you have sheets:

January
February
March
April
Summary

All worksheets are exactly the same, except the values. So, for instance, cell b5 in January may have the amount that Acme Explosives paid for fur replacement. They also paid for some of this in February, and this is in b5 of February's sheet. Now, Acme Boulders is listed in b6 of each sheet. On the summary sheet, you want to show (for now) January through April totals these guys, as well as many other expense columns and companies...

For our purpose, though, you'll type in cell b5 of the summary sheet:

=SUM(January:April!b5)
Okay. So, you fill April's up, make a new sheet for May. You go to your Summary sheet, and you hit Find/Replace.

You'll make sure it's looking in *formulas*, and then put January:April in the find what box and January:May in the replace with box. Replace all.
 

Read other 3 answers
RELEVANCY SCORE 78.8

Hi Guys,

I have an Excel 2007 Workbook with two Sheets.

Sheet1 is Info pulled from a SQL DB Table. It shows Residential and Postal codes for suburbs. Both residential and postal codes appear in one column.

Sheet2 is info pulled from the Post Office Website. It shows Residential and postal codes. However, it displays them in two seperate columns.

Therefore, Sheet1
A B C
Suburb City code
---------- ------ -------

Sheet2 :
A B C D
Suburb City Residentialcode postal
---------- ----- ----------------------- ---------
I need to compare these data as the data from the SQL DB(Sheet1) may be outdated. Sheet2 is from the post office as

Is there anyway to compare them?
 

A:Comparing Worksheets in Excel

Read other 10 answers
RELEVANCY SCORE 78.8

Is there a way to create and name Worksheets with dates?

In particular, July 1, 2009; July 8, 2009; July 15, 2009; etc.? for the whole year?

thanks!
--
Jeff

A:Name multiple worksheets in Excel

I don't know if you can do them all at once. You can certainly do so one at a time.In Office 2003, Right Click on the worksheet tab and choose "Rename" then type in the Date for the name.To create sheets, right click on a tab and choose Insert then Worksheet. Rename the sheets as above.Orange Blossom

Read other 1 answers