Computer Support Forum

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

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

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

Relevance 100%
Preferred Solution: Excel - macro to merge 2 excel files using a common column

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.)

Answer: 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?

3 more replies
Relevance 95.7%

Hi all - I need to take 2 different worksheets that have different data, but have one column in common (for ex - serial #) and merge the two in a new worksheet. Any ideas how to do this easily?
 

Answer:Excel - merge 2 sheets using a common column

8 more replies
Relevance 76.85%

Hi, I am hoping someone can help me with a macro for Excel 2007. I wish to "reverse" the following macro, so that it sends the contents of an entire column (in this case the second column) back to the previous column as Cell Comments, without interfering with the contents of the cells.

Sub CopyCommentText()
Dim CmtText As String
Dim CmtCount As Integer, i As Integer
Dim CommCell As String

CmtCount = ActiveSheet.Comments.Count
For i = 1 To CmtCount
CommCell = ActiveSheet.Comments(i).Parent.Address
Range(CommCell).Offset(0, 1) = ActiveSheet.Comments(i).Text
Next i
End Sub ​
 

More replies
Relevance 76.85%

Hi Everyone,

Seriously need your help ><"
I am using MS Office 2010 and Windows 7

I have a data like this (The number of Products and Shops are much more)

I want the result to be like this

This is the attached file for you to download the data or you can simply download from the attachment in this post
http://www.sendspace.com/file/iqvlce

I would really appreciate the help of the community
Kudos to everyone!!!!
 

Answer:Excel Macro - Merging multiple column into single column

in a new cell, create the following formula.
=A1&" "&B1&" "&C1

Or isn't this what you want?
(the cell-numbers are those which you want to combine).
You can copy the formula for all cells (haven't seen your file, find the link a bit tricky, maybe attach the file in a post here)
 

3 more replies
Relevance 103.32%

Hello! Apologies from a computer gumby. I am a PhD researcher conducting a recruitment mailout, with an excel participant database of 300 names. Participants' addresses have been entered (in entirety from house number to ZIP/postcode) in a single column.I need to conduct a mail merge, but cannot find a way (short of changing each cell individually) to reformat the addresses.Current address format: 32 Brook St, Lee Park 3111 Required format: 32 Brook St, Lee Park 3111Had I been forward-thinking, I would have entered data in separate columns :-(Sincere thanks for any advice.

Answer:Excel; addresses in single column; merge?

If all you want to do is separate the addresses into 2 columns, and not move the city underneath the house address, try:Data...Text To Columns...Delimited...Next...Comma...FinishClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 102.09%

Hi,I have a working macro that targets all of the ComboBoxes in my worksheet. I need it to only target whatever Column I choose. The Column will be the location of the comboBoxe's.  Here's what I have that works but does every comboBox on my sheet. Code: [Select]    Sub AllocateLinkedCellsToComobBoxes()
        Dim cbo As OLEObject
        For Each cbo In ActiveSheet.OLEObjects
        If TypeOf cbo.Object Is ComboBox Then
            cbo.Select
            Selection.LinkedCell = Selection.TopLeftCell.Address
            cbo.LinkedCell = cbo.TopLeftCell.Address
        End If
      Next
    End Sub

More replies
Relevance 102.09%

Hi,I have a working macro that targets all of the ComboBoxes in my worksheet. I need it to only target whatever Column I choose. The Column will be the location of the comboBoxe's.  Here's what I have that works but does every comboBox on my sheet. Code: [Select]    Sub AllocateLinkedCellsToComobBoxes()
        Dim cbo As OLEObject
        For Each cbo In ActiveSheet.OLEObjects
        If TypeOf cbo.Object Is ComboBox Then
            cbo.Select
            Selection.LinkedCell = Selection.TopLeftCell.Address
            cbo.LinkedCell = cbo.TopLeftCell.Address
        End If
      Next
    End Sub

More replies
Relevance 102.09%

I am using macro for filtering items in E column, now i need to add some column before E somewhere. Filtering columns has been moved to F now macro is not working. How can i resolve this problem without making again macro. Suggesstion pls

Answer:By using Macro in the Column E - Excel

Hi,The simple answer is to edit the macro to point to the new column. You don't have to write a whole new macro.Alternatively if the filter column has a unique header on row 1 then this macro will find it and apply the filter:Sub FiltTest()
Dim rngFilter As Range
Dim strFilterName As String

'set unique filter column header text
'change as appropriate
strFilterName = "Filter Data"

With Worksheets("Sheet1")
'find column to filter (in row 1)
Set rngFilter = .Range("1:1").Find(strFilterName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rngFilter Is Nothing Then
'filter column header found - use its column for the filter
With rngFilter.EntireColumn
'change the filter criteria to match
'or use a reference to a cell containing the filter criteria
.AutoFilter Field:=1, Criteria1:="B"
End With
Else
'filter heading name not found - display warning message
MsgBox "Your filter column header: " & vbCrLf & _
strFilterName & vbCrLf & _
"was not found"
End If
End With
End SubYou should be able to use the find part of this code with your existing macro.You can now add or remove columns and the filter will still work.Change the unique column header name on this line:'set unique filter column header text
'change as appropriate
strFilterName = "Filter Data"Regards

3 more replies
Relevance 101.27%

I barely use Excel but I have a list to modify / reformat.

What I have is about 25,000 names and addresses in one column:

address 1
_________
name1
_________
address 2
_________
name2
_________
address 3
_________
name 3

what I want to achieve is 2 columns where one is address , other is names

__________________
address 1 | name1
__________________
address 2 | name 2
__________________
address 3 | name 3
__________________

etc .

Can you help me out with macro to move every 2nd cell / row in first column and move them to next column , like it's shown in example above?

thank you for any help
 

Answer:Help! Excel Macro: How to move every 2nd row to next column?

6 more replies
Relevance 101.27%

I have to download spreadsheet on a weekly basis and I have to delete some column however the column that I need to delete always the same name but not located always in the same position (i.e. column tagline sometimes in column D sometime in Column F) Can someone help me create a macro that with delete the column using the name instead of the column Letter.

Thank you

Q
 

Answer:Excel Macro to delete column

7 more replies
Relevance 100.04%

I need an excel macro to refer to a different column each time I run the same macro. Does anyone know what the syntax is for this?
 

Answer:How do you get an excel macro to refer to a different column each time its run?

14 more replies
Relevance 100.04%

I want a macro to solve the below problems please:Sample:ABCT*DEFG*AB*Results:A D AB E BC F T G So copy row 1 and when it reach to *, then change another column. The height is uneven so the only splitter is *Many thanks for your help.

Answer:Excel Macro Change Column when Cell = *

re: So copy row 1 and when it reach to *...I assume you mean "So copy column 1 and when it reach to *..."With your data starting in A1, this code will split it, starting in B1:
Sub SplitData()
'Determine last Row with data in Column A
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Initialize Column number
colNum = 2
'Loop through data
For myList = 1 To lastRow
'When we hit an "*"...
If Range("A" & myList) = "*" Then
'Increment loop counter so we don't use the "*"
myList = myList + 1
'Increment the Column number
colNum = colNum + 1
'Reset the Row number
rowNum = 0
End If
'Increment the Row number
rowNum = rowNum + 1
'Place the data in the proper cell
Cells(rowNum, colNum) = Cells(myList, 1)
Next
End Sub

2 more replies
Relevance 100.04%

Hi there, I have some data for 10000 cases in 10000 raws in Excel ranged from Column A to S. I want all the raws transposed and been copied in the same column i.e., column T. I have tried Macros but it does not go beyond the first two raws. Any advice is appreciated.

Answer:Excel Macro for Transpose raws in one column

You have a very large number of rows and I do not know whether that will prevent the procedure for  this from working.  But, try the procedure as explained at http://office.microsoft.com/en-us/excel-help/switch-transpose-columns-and-rows-HP010224502.aspx

2 more replies
Relevance 100.04%

I have 1 identifier (column a) and 10 associated yes/no questions (column b to column k). I need a macro that inserts a separate row in a new sheet for each "No" recorded in those 10 columns. Rather than inserting the entire row, I only want the identifier (column a) and column heading (b1:k1) containing that value.

More replies
Relevance 100.04%

Hi there,I'm not used to VBA language so I couldn't use the comments of the replies on the same topic (May 2010) to try to write my own code.So here is my request : in excel 2010, I would like to hide the rows (as from row 7) for which the column L has the value "Complete" (I've put a data validation list in column L that resume the status of the different actions written in the first column).Is it also possible to get an "active" button to click on to hide & unhide the "completed" actions?Thanks a lot in advance to anybody who could help me out.Marie

Answer:macro to hide rows if 1 column has a specified value (excel)

HiCould you provide a link to the May 2010 topic? But it sounds like "Filtering" will get you what you want. If you place a filter on col L and in the Filter drop down select everything but "Complete" it will hide all rows with "Complete" in Col L. To reverse it simply click on "Complete" in the Filter drop down and all the "Complete" rows will reappear.

7 more replies
Relevance 100.04%

I am setting up a series of Excel sheets for my boss, in which one tab gets data from a previous tab. What I am trying to do is find a way for this new column (in the second tab) to be automatically sorted (it is a list of email, so alphabetically). This would than feed into another Excel sheet. I don't want my boss to have to go into that intermediary sheet and click sort himself or have to run a macro. Basically, is there any way to sort an entire column by utilizing formulas. I don't care if I have to create one or many extra columns. Thank you very much. Dan
 

Answer:Excel Help - Can you sort a column by using formula (not macro)

I may not be on the right track here, but for me, and my technically challenged bosses, the easiest thing to do is to setup autofilter. if you are using MSOffice 2003 or later it allows you to sort ascending or descending order by clicking the drop-down arrow and scrolling up on the options and selecting the sort order, this also allows for custom sorts, ie you are looking for only the contact details of all the members of a certain company. when i first started with this it seemed awkward but over time even my bosses have come to enjoy this particular feature of MSOffice 2003 and later. the older versions don't allow you to do the sort function on the auto-filter button. otherwise write a macro that runs when you open the spreadsheet.
 

3 more replies
Relevance 100.04%

I am looking to write a Macro that will search the first row of a table of data (the row containing the collumn headings) for 5 specificly named headings (call them Heading1, heading2 etc...), and only IF all of those headings are found,continue to run the rest of the macro. Otherwise give an error or exit the macro. The Macro itself runs a series of calculations and looks up information from the table to compile it into more managable data, but if all of the required collumns are not their then it will return an error and this causes me problems. I want to pre-empt the error and prevent the macro from executing. Thank you in advance. Finley

Answer:Macro to check for column before executing - EXCEL

You can certainly check for the Column Heading within the macro or you can use a Error Handling routine with On Error. With On Error you can force the Macro to stop execution or present a message to the user about the mssing column or basically do whatever you want it to do when an error occurs. You can even test the error value and do different things based on what error occurred. Look up the On Error statement in the VBA Help files or via Google. Using error handling routines is very powerful.If all you want to do is check for the Column heading and exit the macro if the heading isn't found, this is one of the many ways it can be done:Sub FindColumnHeading()
With Range("B5:N5")
Set c = .Find("MyColumnHeading")
If c Is Nothing Then Exit Sub
End With
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 99.63%

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!
 

Answer: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!
 

1 more replies
Relevance 99.63%

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
 

Answer: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?
 

1 more replies
Relevance 98.81%

Hi,

I've used Tech support guy for quite a while and almost always find what I need.
I guess it's about time I join and post a question

So this is my problem.
I send out an excelreport every day.
In it I use the content in column X to populate the hyperlink address in column Y.
I've done this manually but now the report is getting bigger and a macro seems in place.
Problem is I just don't know enough about the syntax used in excel.

What I want to do is loop though each row - if there is data in column Y then check in column X.
If X is populated use the data as hyperlink address in column Y (info in Y should still be what is shown).
This should be done for each row where Y is populated.
Then the X column shall be deleted (but I guess I could do that manually )

It feels pretty straight forward and I could do it in RPG or SQL but the syntax in excel befuddles me
Any tips?
 

Answer:Excel macro - use info from one column as hyperlink address in another.

Assume Y2 and X2 cells
=if( Y2 = "", "", IF(X2="", "" , do Y2)

but you need to have the formula in Y and change Y - i think that may need a macro, as you are testing Y and also rewriting Y

a spreadsheet with dummy data would be useful to see
 

1 more replies
Relevance 98.81%

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.
 

Answer:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

2 more replies
Relevance 98.81%

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you
 

Answer:Excel Macro to Move data in Rows to column

8 more replies
Relevance 98.81%

Hello I'm having a problem in create a excel macro, that multiply the values from a table(for example the range is (B5:L20)) by the values that are in the column (A5:A20). The result should be place in another sheet.

There is someone who can help me, please.

Regard
 

Answer:Solved: Excel Macro multiply range for a column

6 more replies
Relevance 98.81%

I'm using some software to export payment information into a CSV file. This file needs to be in the bank's specified SIF format which requires all the data to be in one column.

The limitation with my software is that the detail of each payment gets exported into it's own separate column. For instance, cells A1:A10 contain payment details to one individual, B1:B10 to another, and so on. I require the whole lot combining into Column A, running from A1:A65536 (it will never go this far down the spreadsheet, but you get the idea).

I've tinkered with some basic copy and delete macros but I'm unable to make the headway that I need to. I don't have the touch when it comes to programming .

Thanks in advance
 

Answer:Solved: Excel: Macro to combine all Columns into Column A

12 more replies
Relevance 98.81%

In a database exported to Excel, a number of records come in for multiple sales people. Each row is a record. However the sales person's name in Column A is only shown once and for each of his records below that, the cells in column A are blank until the first record of the next salesperson, where his name is shown only once, then all his records are shown in subsequent rows, but cells below HIS name are blank (they do not repeat his name) until the first record of the next salesperson, and so on.

In order to sort these records I need to autofill the blank cells below EACH salesperson's name down to where the next salesperson's name first appears, and then autofill the next salesperson's name down to the next one, etc.

This step is part of a larger macro, but I have to end the macro there and do this manually because I can't figure out how to write the code to do it automatically. ??? Hope someone can help!

Thanks, Jim
 

Answer:Solved: Need Excel Macro Code to Autofill Column

12 more replies
Relevance 98.81%

I am trying to write a macro in Excel... it's fairly simple, but I can't figure out one part. I used the record feature to get started (I'm quite rusty in writting macros)...

What I want to do:
I have lots of data in one worksheet. I want to pick out some of it and put in into another worksheet. I want the row to be variable, the columns set.

For example, I want the info from row 26. I want take the info from worksheet "pricing" cell F26 and put it in worksheet "report" in cell B5.

Excel gives me:

Range("B5").Select
ActiveCell.FormulaR1C1 = "=pricing!R[21]C[4]"

For whatever reason, using the record feature, it's selecting the row and column in reference to the cell that the data is going in to! (B5 + 21 rows + 4 columns = F26)

What I would like is it to pull info from a variable row and fixed column... can you help me with the scripting? I know how to set the variable and such.
 

More replies
Relevance 98.81%

I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item in each category occupies its own row, as follows:Eg.From this:col1 col2 category1 category2 category3 etc...aaa bbb $55 ccc ddd $44 $66To this:col1 col2 category amountaaa bbb 2 $55ccc ddd 1 $44ccc ddd 3 $66Can anyone assist?Many thanks,jeannie

Answer:Excel macro - column data to multiple rows

You could write a macro to do this, but if it's a one time excercise, it might be easier to do it manually. Turn on filters, one by one for each of the category columns; choose non-empty then hide non-relevant columns, copy and paste the results to a new sheet. Let me know if it requires further explanation (or a macro).

13 more replies
Relevance 97.99%

HI,I have an excel sheet with numbers in column A1 , column B1 , column c1 up to column p1like this500 1000 2000 3000 etc..and i have a table with numbers in rows starting at AA1 ,AA2 , AA3 like thisAA1....: AB1......AC1...........AD125..........30.........40.............60 ......... ...500... 60...... 2000AA2.... AB2...... AC2.............AD250.........60...........70...........1000 .........500.....60........ 70....... 3000AA3.....AB3.........AC3...........AD370..........80...........90..........3000........2000.... 90......... 80i need a macro that would search and match the numbers in column A1 B1 C1 with each ROW in the table starting at AA1 UP to AA15 and paste the remaining numbers from the row that as the matching number under the column A1, B1 C1 for example the result would beA1........................... B1 ..................C1500....................... 1000 ...............2000 -----------------------------------------------------------25.............................50..................25 30................... .........60 .................30 40...... ......................70 ................40 60............................500.................60 60........................... 60..................500 2000... ................... 70...................6050 ........................3000 .................70 60..................................................8070...................................................901000........... Read more

More replies
Relevance 97.99%

Hi, I need a macro to use in Excel 2003 that will select all data in a column except the header row. At the moment i am using the following command which works well in most cases

Range("A1").Select
ActiveCell.Offset(1, 0).Select
Range(Selection, Selection.End(xlDown)).Select

The catch to this is that if the column contains only one row of or no data then the command selects the entire column! Eek, can anybody please help me to fix this?
 

Answer:Solved: Excel macro to select data in a column with a header row

Dim lastRow as long
LastRow = worksheetfuntion.Max(2, Range("A" & rows.count).end(Exlup).Row

The variable will contain the last filled row in column A, the Max function will make sure that it will at least be row 2 just in case the column is empty

Then all you need is Range("A2:A" & lastRow).Select
Or if you need more columns then Range("A2:H" & lastRow).select
 

3 more replies
Relevance 97.99%

Hi All,

I have a list of over 20 or so web sites and their corresponding advertisers and how much money they spend with each web site.

The list of web sites looks like this:
Weather Channel............Dollars..............About.com.......Dollars......etc...for 18 columns
IBM...............................$200,500..............Cingular...........$350,000
TMobile..........................$450,000..............Revlon............$275,000

My goal is to consolidate all the advertisers into a SINGLE COLUMN then to the right of it, have all my web sites across as COLULMNS reporting.

should look like this:

............................Travelocity.............. Expedia ....................Orbitz
ADVERTISERS
AT&T Corp...............$78,100................$94,700....................$271,200
Starwood Hotels.......$696,500...............$30,600....................$124,100
Hilton Hotels............$306,700................$900........................$64,200
Ramada...................$13,000.................$450,000...................$600,000
etc....(for at least 150 rows)

I'd like to be able to have the flexibility to add as many columns (Web sites with advertiser dollar amounts) as possible but have the macro paste the result to a NEW worksheet.

Does anyone know how to do this?

-bob
 

Answer:Consolidating list of web sites and their vendors into one column - EXCEL Macro

11 more replies
Relevance 97.99%

In Excel 2007, I have a spreadsheet with 14 columns. Twelve of the columns contain categories of data that correspond to the entries in the other two columns. I need to change the list so that each item/amount in each category occupies its own row, as follows:

Eg.

From this:

col1-----col2-----category1-----category2-----category3-----etc... up to 12 categories
aaa------bbb---------------------$55
ccc------ddd------$44--------------------------$66
To this:

col1------col2------category------amount
aaa-------bbb------2-----------------$55
ccc-------ddd------1-----------------$44
ccc-------ddd------3-----------------$66
I know how to do this manually, but it needs to be done frequently on many different sheets. So, I need a way to do it automatically.

Many thanks for your help
jeannie
 

Answer:Excel macro - change column data to multiple rows

I have your data mapping from A:N to P:S, with no headers. In case that's wrong, make sure your work's saved before trying this.

Sub test()
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LastRow

For Each Cell In Range("C" & i & ":N" & i)
If Cell <> "" Then
DestRow = WorksheetFunction.CountA(Columns(16)) + 1
Cells(DestRow, 16).Resize(, 2).Value = Cells(i, 1).Resize(, 2).Value
Cells(DestRow, 18) = Cell.Column - 2
Cells(DestRow, 19) = Cell.Value
End If
Next Cell

Next i
End Sub

HTH (welcome to the board)
 

2 more replies
Relevance 96.76%

Hi Guys,

I need help creating a dynamic macro that could save me alot of time in the future.

In a nutshell:

- "Name" value in Column A
- "Yes" or "No" value in Column B
- "Name" value in Column C

I need:

- Every cell in Column C to be crossed referenced with Column A
- If there is a match, I need the value in Column B to be changed to Yes

Before:

------Column A ------------------------------ Column B ------------------------------ Column C -------
-------------------------------------------------------------------------------------------------------------------------
-- Jen NO Jen --
-- Jane NO Jane --
-- Sarah NO Sarah --
-- Mike NO James --
-- Mitch NO Fred --
-- Joy NO Lee --
-------------------------------------------------------------------------------------------------------------------------

After:

------Column A ------------------------------ Column B ------------------------------ Column C -------
-------------------------------------------------------------------------------------------------------------------------
-- Jen Yes Jen --
-- Jane Yes Jane --
-- Sarah Yes Sarah --
-- Mike NO James --
-- Mitch NO Fred --
-- Joy NO Lee --
-------------------------------------------------------------------------------------------------------------------------

Thanks for the help,
Ben
 

Answer:Need Help - Excel 2010 - Macro - Find, if Found Replace Text In Another Column

As just a Formula, put this in column B ' =IF(A1=C1,"Yes","No")'

As a Macro -
Sub MatchReplace()
Dim LastRowColA As String
LastRowColA = Range("A65536").End(xlUp).Row
For i = 1 To LastRowColA
Range("B" & i).Select
ActiveCell = "No"
If ActiveCell.Offset(0, -1) = ActiveCell.Offset(0, 1) Then ActiveCell = "Yes"
Next
End Sub
 

2 more replies
Relevance 96.76%

I have the following macro below which works great for when any value is entered in Column C, it time/date stamps the adjacent cell in Column D. However, I need it to do the same thing a few columns over (ie, when any value is entered in Column H, it will time/date stamp the adjacent cell in Column J)

For the life of me, and cannot get it to repeat . Any ideas? I'm a bit new at this, but thanks in advance for the help. Feel free to spell it out - I won't be offended.

Thanks! Here's the current macro:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("C3:C9999"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

 

Answer:Solved: Excel Macro - Repeat Time/Date Stamp for Different Column?

6 more replies
Relevance 96.35%

Ok, just a bit of advice needed on this one for the time being...
I need a macro that will:

- Save all (Excel) attachments from the currently open email message (or a message selected in the Inbox) to a directory on the PC.

- Merge all the saved Excel files into a single file - they're all formated exactly the same - data in about 20 columns, no column headers or anything, just standardised data.

- Save the merged file with a standard name (i.e. every time this is ran, it needs to have the exact same filename).

- Delete all the files in the directory apart from the merged file.

I know there's probably a fair bit of code online for doing the individual parts of this. Can I do both from within Outlook? It doesn't particularly matter if I need to do this in 2 steps - an Outlook macro to save the attachements, and an Excel macro to do the file merge etc.

Cheers.
 

Answer:Solved: MS Outlook / Excel macro - extract attachments and merge

16 more replies
Relevance 94.71%

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

Answer:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

3 more replies
Relevance 89.79%

I have column (i pretend column is line which goes from up to down) . It contains values. like 450 , 320 , 14000 . I want to delete entire row ( left to right) , whose value is less than 900. how cna i do this?

thanks

http://imageshack.us/photo/my-images/818/was34.png/
 

Answer:Need to edit excel files column

9 more replies
Relevance 89.38%

I have 2 excel files. I want to merge them together. It wil be like 100 excel files. Something like

bus
bus1

car
car1

laptop
laptop1

How can i merge 100 files together using macros.

thanks
 

Answer:how to merge 2 excel files

16 more replies
Relevance 88.15%

I was wondering if it is possible to merge multiple excel files into one?

I have the following:

name1.xls
name2.xls
name3.xls

etc... it goes on for a while and I want it so that when I open

name1.xls it has name2.xls, name3xls etc... where sheet1,sheet2 & sheet3 is etc...
copying all the data manually would take ages so is this possible?
 

Answer:merge multiple excel files into 1?

13 more replies
Relevance 87.33%

I'm trying to Merge an Excel spreadsheet to Outlook and provide alerts for critical dates in said spreadsheet in the Outlook calender !
 

Answer:Excel - Merge Excel spreadsheet to Outlook with Alerts

Welcome to TSG faithtronic.

I've never done it but your thread has been setting here a while. A method to do this here http://www.ehow.com/how_5685419_create-calendar-excel-data.html

I would start with a couple of dates so not to screw up the whole calender! There are some sharper excel folk on this site that might provide a better answer. But this is a starting point?
 

1 more replies
Relevance 86.51%

Hi Guys,I am all new to writing macros and VBA, so I need a bit of support now since I seem to have lost overview.What I am trying to do is a mail merge directly from Excel (a word file with layout and mergefields are already done, I just wish to call it from Excel) with output as PDF files, one for each letter (each having their information in a row in Excel as usually for mail merge).Moreover, I would like to name the PDF file with part of the mail merge - in specific the name of the receiver of the letter (stated in column A).Finally, I would like to add a piece of code making sure that I would not have to manually click "ok" to the "Opening this document will run the following SQL command: SELECT * FROM 'Sheet1$' ".Can you brilliant guys please help me?The code I have for now is:Option ExplicitSub PrintCPIP()' Sub that hopefully will make mail merges of several data as pdf (should end up with several pdf's) _and moreover name the pdf-files after the values of the first 3 columns in the merge. Dim wdOutputName, wdInputName, PDFFileName As String Dim x As Long PDFFileName = ThisWorkbook.Path & "\Letter " & x & ".pdf" 'I would like the name of the file to include _ the first and middle name of the person wdInputName = ThisWorkbook.Path & "\Example.docx" Const wdFormLetters = 0, wdOpenFormatAuto = 0 Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = 3 ' open the mail merge layout file Dim wdDoc As Object Set wdDoc = GetO... Read more

Answer:Mail merge from Excel to PDF's with specific naming of files

An example of both Excel and Word documents should be available as .zip via the link below for the next 7 days:http://www.fileconvoy.com/dfl.php?i...

7 more replies
Relevance 85.69%

Hi,

I have a problem in merging two worksheets in excel as the two sheets have different no of rows in it. and i need to merge the two sheets in a new worksheet, and in that work sheet i need to remove the duplicate values present in the both work sheets.

anybody can help me to do that.
 

Answer:Need to Merge two excel sheets in one excel sheet.

Hi,

What version of Excel are you using. If it's 2007 then I think you can copy the data to another worksheet and select the range then use the "Remove Duplicate" option that you find it the "Data" tab.
 

1 more replies
Relevance 85.28%

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
 

Answer: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?
 

1 more replies
Relevance 85.28%

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
____________________________
 

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

7 more replies
Relevance 85.28%

Im 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

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

7 more replies
Relevance 84.87%

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

Answer: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

1 more replies
Relevance 84.87%

Hi everyone,First I have search on mine own before asking this qeustion here :). I am using XP with Excel 2003.My qeustion is nextI try to make a macro that works for multiple excels files called scans_0001.xls and it counts till the end.I try to use ActieveSheet, or Myfile to get the file name of open excel file. What I need is a macro that opens every scans_????.xls file and copy some single raws and paste in the total.xls.Like:TotaL:raw 1: scans_0001.xlsraw 2:scans_0002.xls and so on.The scans has 7 sheets called, Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6 and overview. I used =Sheet1!B1 in the macro the get info of active sheet and it works but not with macro for every single file. So can some one help me please.Thank you in advanced.Greets,Kutu

Answer:Need Excel Macro to open files

Hi,I wonder if your problem is that you are using =Sheet1!B1, but you have not activated the appropriate workbook, so =Sheet1!B1 always looks at Sheet1 in the same workbook, also =Sheet1!B1 is the form used in worksheet cells not in VB code.I assume that you have some form of For...Next loop to create the name of each workbook and open itIf it's like this:For n = 1 to 10
Workbooks.Open ("C:\MyPath\scans_" & Format(n, "0000") & ".xls")
Next nChange it to create the workbook file name first and then use the filename to both open the workbook and reference it specifically:For n = 1 to 10
strWBname ="scans_" & Format(n, "0000") & ".xls"
Workbooks.Open ("C:\MyPath\" & strWBname)
x = Workbooks(strWBname).Worksheets("Sheet1").Range("B1").Value
Next nBy fully qualifying the cell with its worksheet and workbook names you will be sure of getting the data from the correct place.If you still have problems, please post the code you are using.Enter the code between <pre> and </pre> tags - you can insert them from the icon above the reply text box.Regards

12 more replies
Relevance 84.87%

Hello Everyone:I have a VBA macro designed to take a whole bunch of Modbus addresses entered into a list and then export specifically-named csv files from Excel. My code is below. It works beautifully, with one small problem. When I enter addresses into the list sometimes not all subsections of the list are filled, in which case there should not be a csv file created for that particular subsection. Unfortunately I'm getting some csv files that are 0 kB exported as a result. This wouldn't be a big deal except I grab all of the exported csv's at once and then import them into my other piece of software. Because of the way they are named the importing software does all the work!To use this sheet I put the title name in column A and copy it all the way down. Column B gets the subset name (could be a variety of things) Column C has a listing of attributes under each subsection name, and finally column D is where I 10-key in the Modbus addresses, where applicable. Anyone have any ideas? I'm not sure how to paste my code into here and made it actually look like code either. If anyone could advise for future posts I would certainly appreciate it. Thanks so much!Sub Export()Dim num_rows As IntegerDim filename As StringDim Filelocation As String'Save the CSV Files to this locationFilelocation = "C:\Documents and Settings\CSV_Exports"'Cell A5 is using the counta function, taking a look at the total entries in column A for which to apply these forumlasnum_rows = Range("A5").Va... Read more

Answer:Excel VBA Macro - exporting csv files (0kB)

1 - To post VBA code correctly (and thanks for asking!) click on the How To in my signature line.2 - I haven't really looked at your code (yet) to see what it's doing, but maybe you could explain this statement a little more:When I enter addresses into the list sometimes not all subsections of the list are filled, in which case there should not be a csv file created for that particular subsection. What is a "subsection"? Is it a range? Is there something we can key off of so the code will say "Hey, that range/cell is empty, skip the csv creation"?Posting Tip: Before posting Data or VBA Code, read this How-To.

6 more replies
Relevance 84.46%

I have a list of 39525 emails in column A and a list of 19909 emails in column B. If an email exists in column B that also is in column A I want it removed from column B. PLEASE HELP! THANK YOU!!!!!!
 

Answer:Solved: Excel 2010 - Remove duplicates from column B that exist in column A

9 more replies
Relevance 84.05%

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!
 

Answer: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
 

1 more replies
Relevance 84.05%

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.FileSearchCan 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 VariantDim SourceBk As Worksheet, DestBk As Worksheet, StartRow As Integer, IndRange As RangeDim ServRange As Range, SrcOpen As Boolean, SourceName As StringDim FilSrch As Object, MyFilArray() As String'MsgBox "This function not available in this version.", vbInformation, "Function Not Available"'Exit Sub'Sheets("LookUp").[a12] = NowWith Application .ScreenUpdating = False .StatusBar = "Counting source files ..."End WithSet FilSrch = Application.FileSearchWith FilSrch .NewSearch .LookIn = ActiveWorkbook.Path .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then ReDim MyFilArray(.FoundFiles.Count) For I = 1 To .FoundFiles.Count If .FoundFiles(I) <> ActiveWorkbook.Path & "\" & ActiveWorkbook.Name Then ... Read more

Answer:Excel 2003 Macro doesn't run under Excel 2007

As far as I know, Application.FileSearch is not available in 2007. Searching for filesearch replacement turns up lots of hits with various workarounds, such as this:http://groups.google.com/group/micr...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 84.05%

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 = .Column... Read more

Answer: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
 

1 more replies
Relevance 84.05%

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
 

Answer:Excel Macro runs fine..then excel crashes

11 more replies
Relevance 84.05%

With due respect and credit to DerbyDad03 whose earlier post gave me the code below; is there any way to have the new worksheets created from a variable length field e.g. a persons name in a list or a field of fixed length other than a date as the script below uses?Sub CreateMonthlySheets()Dim lastRow, mMonth, tstDate1, tstDate2, shtName, nxtRowOn Error Resume Next'Turn off ScreenUpdatingApplication.ScreenUpdating = False'Make a copy of the data sheet and sort by date Sheets("Sheet1").Copy After:=Sheets(1) Sheets(2).Name = "SortTemp" With Sheets("SortTemp") lastRow = .Cells(Rows.Count, 1).End(xlUp).Row Rows("2:" & lastRow).Sort Key1:=Range("A2"), Order1:=xlAscending 'Using SortTemp Sheet, create monthly sheets by'testing Month and Year values in Column A'Loop through dates For Each mMonth In .Range("A2:A" & lastRow) tstDate1 = Month(mMonth) & Year(mMonth) tstDate2 = Month(mMonth.Offset(-1, 0)) & Year(mMonth.Offset(-1, 0)) 'If Month and Year are different than cell above, create new sheet If tstDate1 <> tstDate2 Then ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count) 'Name the sheet based on the Month and Year ActiveSheet.Name = MonthName(Month(mMonth)) & " " & Year(mMonth)'Copy Column Widths and Header Row .Rows(1).Copy ActiveSheet.Rows(1).PasteSpecial Paste:=8 'ColumnWidth ActiveSheet.Rows(1).PasteSpecial 'Data... Read more

Answer:Excel Macro to create new worksheet in Excel 2010

I created a quick macro to help you out. This macro creates a new sheet and names it using the contents of cell "A1".Sub CreateNewSheet()
Dim NewSheet
Dim SheetName As String

SheetName = Worksheets("Sheet1").Cells(1, "A").Value
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = SheetName

End Sub
Law if Logical Argument: Anything is possible if you don't know what you're talking about.

4 more replies
Relevance 84.05%

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

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

13 more replies
Relevance 84.05%

I need some help. I am trying to come up with a formula that counts the times that a "string" occurs based on a value in another column.....
Example:
Everytime column a = "Jones", check if column e = "tom".....if so count it.
 

Answer:Counting strings in one column based on Condition in another column in Excel

The EASY way to do this is to concatenate columns A and E using (for instance) this formula:

=A1&E1

or

=A1&" "&E1 (puts a space between two words)

Then count the individual field. Make sense? Will it work for you?

------------------
~dreamboat~
Brainbench MVP for Microsoft Word
Brainbench
 

3 more replies
Relevance 84.05%

Hi Rollin_Again and everyone in this forum,

Hoping someone could help me in my problem.
I have a workbook in Microsoft Excel 2010 (Windows) of about 60.000 rows of words in different languages.

Some words have 5 different languages and some are only 4 or 3, all are written in Column A of the workbook.

Column A
English -------(in color Orange)
Spanish -------(in color Red)
Italian ------- (in color Blue)
German ------ (in color Black)
Papiamento ---(in color Green)
etc.

What I want is a Macros Program that can put all Spanish words (red color) in Column B, all Italian words (blue color) in Column C, all German words (black color) in Column D, and all Papiamento words (green color) in Column E.

Like for example:

Column A --- Column B ---Column C --- Column D ---Column E
English -----Spanish ----Italian ----- German --- Papiamento
etc.

Thank you very much in advance and I'll appreciate all the helps you could give me.
sukisuki
 

Answer:Transfer texts from Column A to Another Column, Excel 2010 (Windows)

9 more replies
Relevance 84.05%

I have a large spreadsheet where I need to search Column B for any cell containing the word "County" and if a cell is found, move that cell over to Column A. The spreadsheet has more than 10,000 rows, so I don't relish the thought of doing this manually.

The cells I am pulling out have more than just the one word. Example:
Column B contents: County: MOUNTRAIL State: ND
The recurring word is "County". Any cell in Column B needs to be moved to Column A without comprimising the rest of the document.

Any suggestions?

Thanks!
 

Answer:Excel: Need to search text in column B and if found, move to column A

You could use the following to either copy the contents of Column B into Column A or show a blank if the word "County" is not in Column B:
=IF(ISERROR(FIND("County",B2)),"",B2)
You would place this formula in A2 (assuming you have column headers) and copy it down the column to get either a blank (if County is not in the cell next to it) or the contents of the cell in column B. Then, you could select all of column A and do Copy and Paste Special > Values to paste the results of the formula and get rid of it.

Let me know if this is what you're looking for!
 

8 more replies
Relevance 84.05%

Is there a way to have Excel open a folder and place the file name with extensions in Column A. Then open another folder and place the contents of that folder into Column B?

I also would like the list to skip adding file to column if it already exist on worksheet, but if a file was deleted from folder it would be removed from the worksheet.
 

Answer:Excel 2007 Macro/Folders & Files

Hi

Here is a macro that I use quite often ...
Code:


Sub Get_File_Names()

[B]'1st directory[/B]

lRowA = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:A" & lRowA).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 1) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 1) = MyFile
Wend


[B]'2nd directory[/B]

lRowB = Cells(Rows.Count, 2).End(xlUp).Row
Range("B1:B" & lRowB).ClearContents

MyPath = "C:\Users\XXXXX\Documents\XXXXXX\" [COLOR=red]'adapt to your requirement - always end with "\"[/COLOR]
Count = 1
MyFile = Dir(MyPath)
Cells(Count, 2) = MyFile
While MyFile <> ""
Count = Count + 1
MyFile = Dir
Cells(Count, 2) = MyFile
Wend
End Sub

Unless identifying which files are new and what's been deleted is important I would just delete the previous list and get all current files - as I've done in this code.
 

2 more replies
Relevance 84.05%

Hi all

Excellent site! I know I should check for duplicate queries, but couldn't find the search feature as I'm new here and this is my first ever post...

I have two files 'Expenses Analysis' and 'Expenses Form' whereby the user fills out his / her expenses in the analysis file and then transcribes the summary figures into the form on other file, which is then sent off to the finance department.

I've created a simple macro that transcribes the figures between the files when they are both open, but I want it to be clever (=more fool proof) than that. I would like to embed the Expenses Form file into the analysis file, and get the macro to launch the file and enter the figures in one go. (I need to do it this way, rather than combine the functionality, as the Expenses Form is owned by another department and I can't unprotect it etc... ).

Sounds very simple, but I've trouble finding resources on it, as it seems people misuse this functionality for viruses and the like . If another could help me, I'd be much obliged!

I would like to find a way of launching Expenses Form from the Expenses Analysis file, bearing in mind that both files contain macros and a security warning pops up in between.

Kind regards

Neil
 

Answer:Opening embedded files in Excel using macro

What application are you using? Can you post some sample files?

Rollin
 

3 more replies
Relevance 84.05%

Good day,

I have an inquiry, I wanna ask if there is a way to get the contents of .txt #1 file and place it in a sheet in excel starting from cell A2, then the macro will look for the end of the copied .txt file in excel...offset it by 3 cells down then paste the contents of .txt #2.

Then the macro should also be able to delete the pasted values from .txt #2 and replace it with .txt #3 at any time should the user choose to do so still making sure that it's below .txt #1.

The data is actually being used by a pivot table, it would just refresh everytime we have a new .txt file coming in without replacing the first part. Oh...i don't have Access on my system, I only have VBA in Excel and the text files to use.
 

Answer:Solved: Text files and Excel Macro

6 more replies
Relevance 84.05%

Hi Again

Keballah gave me this macro that when I put the cursor in a cell that has for example WW1.xls and then execute this macro it opens up the Excel workbook named WW1. However, when I try to open a PDF file like WW1.pdf I get garbage and not the PDF file. Can someone out there take a look at the macro below as an example and create a new one so that it also brings up PDF files??

Thanks

Mario


Sub Demo()
Dim fso As Object 'FileSystemObject
Dim fldStart As Object 'Folder
Dim fld As Object 'Folder
Dim fl As Object 'File
Dim Mask As String
Dim thisFolder As String
Dim fullFilename As String

Set fso = CreateObject("scripting.FileSystemObject") ' late binding
'Set fso = New FileSystemObject 'or use early binding (also replace Object types)

Set fldStart = fso.GetFolder("M:") ' <-- use your FileDialog code here
Mask = ActiveCell.Value
thisFolder = fldStart & IIf(Right(fldStart, 1) = "\", "", "\")
Debug.Print thisFolder, Mask
If Not IsError(Dir(thisFolder, vbReadOnly)) Then
If Dir(thisFolder & Mask, vbReadOnly) <> "" Then
Workbooks.Open Filename:=thisFolder & Mask
Else
fullFilename = ListFolders(fldStart, Mask)
If Len(Trim(fullFilename)) > 0 Then
Workbooks.Open Filename:=fullFilename
End If
End If
End If
End Sub

Function ListFolders(fldStart As Object, Mask As String) As String
Dim fld As Object 'Folder
Dim thisFolder As String
For Each fld In ... Read more

Answer:Hyperlink Macro in Excel VBA that finds PDF files

You're getting garbage as you're asking excel to open a pdf as a workbook:

Code:
Workbooks.Open
You'd need to open the PDF using a PDF viewer and code accordingly.
This might be helpful:
http://www.myengineeringworld.net/2012/07/vba-macro-to-open-pdf-file.html
 

2 more replies
Relevance 84.05%

Hey all,

Im currently working on a macro of rather large size and im a little stuck at the moment.
The endgoal of the macro is to have a clean file out of all the mess and that can be mailed to other pple of my staff. Now here it comes:
In my example there are 3 tabs: Raw file, Database and Endfile. The point of the whole macro is to look up some things between Raw file and Database and then to write it to the Endfile. The Raw file and the Database are allready macromade and all three sheets have a layout that cant be changed.
--> First thing is that i need to check if the Rapprt N in column L of the Raw file is somewhere in the Database file at column M (CINCUS).
--> IF the number is there I need to check the type wich can be found at column S (CINSTA)
--> IF THEN that type is 7 then it has to be written away to Endfile, what has to be written away is colored blue in my example.
--> IF THEN that type is 8 it has to be written away to the sheet "Type 8" for further investigation, the data is the same (blue colored)
--> IF the number is not found it has to be written away to the sheet "Not Found" with also the same data.

I think thats it, its much i know. But im stuck in finding all the formulas and stuff. So some help is greatly appreciated. Thx in advance.
 

Answer:Macro excel matching and writing files.

9 more replies
Relevance 83.23%

I have a spreadsheet that I would like to give to both English and Spanish speaking workers. Is there a way to convert the English text in column A to Spanish in column B? I will be changing the text in column A on a weekly basis and will need to translate then distribute to all workers.

Thanks in advance.
 

Answer:Excel - Translate English in Column A to Spanish in Column B

8 more replies
Relevance 83.23%

COLUMN A
5
5
6
9
COLUMN B
A
A
B
B

A=10
B=15
Hi there, I need help creating a formula that adds Column A, only if Column B = 'A'.
I then need to multiply that counted value by another cell.

Can someone please help me? I think I'm over-thinking this.
 

Answer:Excel: Count Values In One Column On Basis Of Other Column Value

=SUMIF(B1:B4,"A",A1:A4)

Where B1:B4 is where your A and B values are, and A1:A4 is where your numbers to sum are located.

HTH
 

1 more replies
Relevance 83.23%

I have a data logger file which is comma deliminated. The file contains 100's of values. I'd like to format the file everytime it is generated so that values fall into labeled columns distinguishing them from one another.
I'd like to know if a macro can be created to make the column titles and move the appropriate data in its column. I can't imagine doing this with record macro because the task is so large the likelihood of a mistake is too great.
Appreciate any feedback.

------------------
Jim R.
 

Answer:Excel Macro to format comma deliminated files

6 more replies
Relevance 83.23%

I'm running about 400 sequential data queries from the web. They eventually bog down my PC, causing it to slow down and then later lock up. I think the problem is related to having too many temporary internet files, because if I delete them before I start the macro, the PC executes more queries before locking up
than otherwise.

So I'd like to have the macro purge the temporary internet files after it gets the data from them and before it executes another web query. I tried using a code like this.

Kill "C:\Documents and Settings\Dick\Local Settings\Temporary Internet Files\" &
"hp?s=^DJI&a=01&b=01&c=2004&d=12&e=31&f=2005&g=w.html"

But this statement won't delete files in "C:\Documents and Settings\Dick\Local Settings\Temporary Internet Files". I can't even delete a file like x.gif from that directory

Example - Kill "C:\Documents and Settings\Dick\Local Settings\Temporary Internet Files\x.gif"

But the same statement will delete x.gif from My Documents\Word directory using the command

Kill "C:\Documents and Settings\Dick\My Documents\WORD\x.gif"

Do you have any ideas as to how I can make the macro delete files from "C:\Documents and Settings\Dick\Local Settings\Temporary Internet Files" ?

If you do, another complication I don't know how to deal with is that Windows Explorer shows the file name as "hp?s=^DJI&a=01&... Read more

More replies
Relevance 83.23%

Hi excel-macro experts, I am writing a data-compiling macro which does, 1)select folder, 2)open xls files in the folder, 3)select all data for each file (only sheet 1 has data), 4)create a new file in the folder (let's say summary file), 5)paste data selected in the process (3) to the summary file created. With a lot of help from many websites, the following macro has been created. However, there is a PROBLEM that is when the data are pasted, all data were pasted into one column (sorce data of each original file has many columns). I need to avoid this. All I want is pasting the source data to Sheet1 of the summary file with the same number of columns (all source data files has the same number of columns) as the sorcce data has. Your help would be greately appreciated!!!

Function RDB_Last(choice As Integer, rng As Range)

' A choice of 1 = last row.
' A choice of 2 = last column.
' A choice of 3 = last cell.
Dim lrw As Long
Dim lcol As Integer
Select Case choice
Case 1:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
Case 2:
On Error Resume Next
RDB_Last = rng.Find(What:="*", _
After:=rng.Cells(1), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
Case 3:
On Error Resume Next
lrw = rng.... Read more

Answer:Help!: Excel Macro; copy multiple files into one file

Can you put your code in the CODE blocks (there is a button in "Go Advanced" mode)? It'll make it much easier to read - that is, if you've indented your code.
 

1 more replies
Relevance 83.23%

I am looking for help to create Excel Macro for download files from sharepoint to local.

Or i need to count the number of files are in each folder on sharepoint.

Thanks,
 

Answer:Excel Macro for download files from sharepoint to local

7 more replies
Relevance 83.23%

Hello guys

I need some help please in Excel macro and VBA.

I am a coop student, and my my boss wants me to do the following :

A software on my pc collects various csv files from several other pc's on the plant. There are 3 different files.

What I would like to do is to run a macro every day which opens that software, import all the csv files to an excel spreadsheet, add the specific header to each file ( I have 3 different headers in one excel file, one for each type of file) and then save the files in xls format.
Could someone provide me with the VBA code for this please?

Thanks
 

Answer:Running excel macro to import csv files from a software on my pc

8 more replies
Relevance 82.41%

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.
 

More replies
Relevance 82.41%

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
 

Answer: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
 

3 more replies
Relevance 82.41%

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?
 

Answer: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.
 

1 more replies
Relevance 82.41%

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).
 

More replies
Relevance 82%

Hi Gurus,

I have many excel files containing similar data.

1. I want to copy specific columns (columns A to U) from all files and paste the consolidated data into another excel file.

2. I also want to copy specific columns (columns C,D,E,G,H,K,L,T,V,W,X,Y) and paste the consolidated data in a different excel.

Please help.
 

Answer:Excel macro - copy specific columns from different files to one file

Are all of these source Excel files stored in the same directory? Do they need to be opened and consolidated in any particular order? More details please. If possible please try to provide a sample file with any sensitive data removed.

Regards,
Rollin
 

1 more replies
Relevance 82%

I need a macro that will go through all the excel files in a directory and add a template file. Replace the template formula names with the filename on the template sheet (I can do this with a find/replace type options on the template sheeet inserted)

With 100's of files this will save a lot of time.

Next is to create a Master summary file that will copy/paste values from all the template sheets into one file.
 

More replies
Relevance 82%

I'm trying to create a macro that will sort on numerous fields. The macro will be stored in my "personal" file as I need to use it in a new report every month. I think I know what the probelm is. It looks like it hard coded the name of the worksheet where I originally created it. I need the highlighted piece to be flexible depending on whatever file is open.

Sample portion of macro starting at the beginning...

ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("O2:O15173"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("D215173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("PWQ QA Report v.05").Sort.SortFields.Add Key:= _
Range("E2:E15173"), SortOn:=xlSortOnValues, Order:=xlDescending, _
DataOption:=xlSortNormal
 

Answer:Solved: Sort Macro for multiple files- Excel 2010

6 more replies
Relevance 82%

I have searched for a similar issue but was unsuccessful. I need to create a macro to do what will surely be a miserably repetitive job of transposing data for a whle bunch of files. I need to get into each excel file and copy certain data fields from column B (B5-B8 turquoise, B13-B15 bright greeen, B23-B27 bklue, B29-B30 pink, and B35 tan) into sequential cells in another file into a single row under a heading A2 - O2. I will need to do this several hundred times, open a file, copy the fields and all the files selected data from a column will wind up in a single excel data sheet in columns.

I thought I would just do a careful record macro, but cannot get 2007 to record beyond my first cell copy. I have named both files xlsm and changed settings to no avail.

The source file is attached and called Source and Detsination is called destination. Any help or a macro would be greatly appreciated!!attached the source file (Source ) I collor codes the field to be copied in the source as indicated aboue with the header fields in the destination if that helps. Thank you VERY much!!
 

More replies
Relevance 82%

Hi guys,

I have a question that has stumped me so far. Here is the scenario: I am dealing with a spreadsheet that has several tabs on it. In each tab, there are several word, powerpoint, and pdf documents that are embedded as objects. My task is to write a macro, that will for each document (keep in mind its an embedded object that needs to be opened), open it with the appropriate program, and save it using a pre-assigned name (the name of the column heading and the row it is in) to a predetermined folder path. This process needs to be duplicated about 200 times. I'm not sure if this is even possible, but I know enough about vba to believe that it is. I've been working on it since yesterday, and I can't seem to tie the pieces together. Can anyone help on this?

Thanks!
 

More replies
Relevance 81.59%

I hid all but three columns and if I right-click on column A to insert a column, it becomes column B and no column is to the left.

Answer:In Excel 2007, how do I add a column to the left of a column when there is only 3?

I have Excel 2007
I put the cursor in column A, right click, and then insert.
Insert entire column. It appears to the left. Is this what you are doing

4 more replies
Relevance 81.59%

Hi,

I have a query but I'm not sure if it's even possible to do.

If I enter a name into cell J1 I want to check for this name in column H then for each row that is found on I would like to sum column B and output the result to cell J2. E.g. If I enter Paul into J1 I would like to search for this name in column H. For every row this is found on I would like the B cell for that row to be included in a sum of which the result is output to cell J2.

Is this possible and if so does anybody know how?

Cheers
Paul
 

Answer:Solved: Excel - Search for value in one column, sum a different column

yes, SUMIF() should do that

i'll edit - and add an example

enter into J2

=SUMIF(H2:H9,J2,B2:B9)

see attached example file
 

1 more replies
Relevance 81.59%

I have a very large spreadsheet with thousands of rows and about 20 columns
Col A has either of 4 values North, South, East, West
The other Cols have numbers
I want to get a total for North, South, East, West for Col B, C etc
Is there a formula I can use something like (if Col A = North , total ColB is )
Don't want to keep resorting by Cola and putting in Subtotals
Hope I have explained it well enough

Thanks

Brian
Dublin, Ireland
 

Answer:Excel Total in a column based upon value in another Column

yes you can use
SUMIF()

SUMIF(A:A, "north", B:B)

OR if you put the values North, South etc into a new column - Say Z2,Z3,Z4,Z5

SUMIF(A:A, Z2, B:B)
and copy down

if not - please upload a sample
Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
 

3 more replies
Relevance 81.59%

Dear all,

How do I sort a column with "#.#.#" according to descending values in excel, while keeping the other columns on the same row?

Number range is 0<= # >=100,000.

Example for 2 columns:
B 1.668.901
A 8.515.492
V 0.0.35
K 1.579.319
P 0.7.82
C 4.263.57
G 1.925.60
H 0.0.0
D 29589.292.235
L 0.29.834
J 1.870.904
M 0.635.5
S 0.0.355
X 0.0.5

Required Output sort only second column:
D 29589.292.235
A 8.515.492
C 4.263.57
G 1.925.60
J 1.870.904
B 1.668.901
K 1.579.319
M 0.635.5
L 0.29.834
P 0.7.82
S 0.0.355
V 0.0.35
X 0.0.5
H 0.0.0

Code can sort by ascending but does not pull the column.

Code:

Sub sortColumn()
Dim arrData As Variant
Dim i As Long, j As Long
Dim temp As Variant
'Range name is "ID"
arrData = Range("ID").CurrentRegion.Value

For i = 1 To UBound(arrData, 1)
For j = i + 1 To UBound(arrData, 1)
If getDesc(arrData(j, 1), arrData(i, 1)) Then
temp = arrData(i, 1)
arrData(i, 1) = arrData(j, 1)
arrData(j, 1) = temp
End If
Next j
Next i

Range("G1").Resize(UBound(arrData, 1), 2).Value = arrData
End Sub

Function getDesc(a As Variant, b As Variant)
Dim aWords As Variant, bWords As Variant
Dim i As Long
aWords = Split(a & "..", ".")
b... Read more

More replies
Relevance 80.36%

Hi,

I have several completed templates that I need to summarize into a spreadsheet.

Is there a way for me to list down the files and the location of the excel spreadsheets and run a macro for copying the data I need from the files listed and pasting them into a spreadsheet format?

Here's what I am doing now, manually.

1. Open file1
2. Copy B2
3. Paste to SummaryFile on B1
4. Copy C2
5. Paste to SummaryFile on B2
6. Copy D2
7. Paste to SummaryFile on B3
8. Close file1

9. Open file2
10. Copy B2
11. Paste to SummaryFile on C1
12. Copy C2
13. Paste to SummaryFile on C2
14. Copy D2
15. Paste to SummaryFile on C3
16. Close file 2

I hope somebody can help.

Thanks,
Monica
 

Answer:Solved: Excel Macro needed for creating a spreadsheet from data on several but similar files.

7 more replies
Relevance 79.95%

Using Excel 2003, I have names and addresses and some other information across 5 different workbooks. I'd like to be able merge these tables into a single table, preserving unique records and avoiding duplicate records. The format varies across the workbooks, but we can make it the same if needed. Tools/"compare and merge workbooks" is currently grayed out. Can you tell us how to do this?
 

Answer:Solved: help with excel merge (not mail merge)

9 more replies
Relevance 77.08%

I am using the MSN Stock Quote addin with MS Excel 2003. Prior knowledge of this should not be necessary, but I just thought I'd provide full context to my issue. I wrote a macro that essentially records daily stock prices. Because I don't want to store old quotes into perpetuity, i need to ensure that the update quotes button has been hit. I use sendkey() to accomplish this, and then the macro copies the quotes and pastes them elsewhere. My main goal is to have the task scheduler open this file daily so i dont ever have to think about this again, and i'm so close. Essentially my problem is that it takes a few seconds after the update button is hit for the add-in to fetch the new prices from the internet, and this process appears to be delayed until the macro is finished. This is a problem because the macro ends up recording un-updated quotes. I have tried the Application.Wait procedure, and I've tried time consuming loops, do while loops, etc. All of these approaches result in the same thing: Can someone please help me with this? I'm too lazy to hand update this spreadsheet every trading day for the rest of my life (or until i quit). Thank you in advance!
 

Answer:Excel Macro - Allow cells to update query before macro completes

Can't you just put the update macro code in a module of it's own and call it from the main macro?

Regards,
Rollin
 

1 more replies
Relevance 75.44%

I am running Microsoft Office Professional Plus 2010 on Windows 7.

When I open Excel my personal.xlsb opens fine and my macros are available and I can create new macros. No problems.

BUT when I open any existing Excel file the pesronal.xlsb is not opened and therefore no macros and I cannot create new macros as I get the error "Personal Macro Workbook in the startup folder must stay open for recording." I have been trying things all night with no luck. Any suggestions would be greatly appreciated. I will list the things I have tried or checked with no success below:
In Options>Advanced Tab>General Section....the box for "Ignore other applications that use DDE" is NOT checked.
In Options>Trust Center>Trust Center Settings>Trusted Locations... I have C:\Users\Nick\AppData\Roaming\Microsoft\Excel\XLSTART listed which is where my personal.xlsb is located
In Options>Trust Center>Trust Center Settings>Macro Settings ...Disable all macros with notification...selected and not selected neither way solves the issue.
In Options>Add-Ins>Manage: Disabled Items ...There are no disabled items.
I have deleted HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel in the registry editor
I have deleted C:\Users\Nick\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB and then run excel to create a new personal file.
I have renamed C:\Users\Nick\AppData\Roaming\Microsoft\Excel\XLSTART\PERSONAL.XLSB to PERSONAL.XLSA

Thank you in advance fo... Read more

Answer:PERSONAL.XLSB does not open when existing excel files are opened in Excel 2010

6 more replies
Relevance 75.44%

Hi,
I transferred all my old Excel (2003 and before) files to my new notebook which has Vista HP and Excel 2007. I open an old Excel file and it looks OK but when printing or print previewing, every line appears double-spaced and the document runs over to a second page.
If I change the row height it prints the squeezed up text but still double-spaces. If I copy the whole content of the spreadsheet into a new blank spreadsheet it prints normally.
Is there any setting in Excel 2007 that I might have missed ?
Thanks.
RB
 

Answer:Printing Excel 2003 files with Excel 2007 give double spacing

7 more replies
Relevance 75.44%

Hi! i need you help!.. I am unable to open or save files in the shared drive using Excel 2007 but i can open/save excel 2007 files saved in my local drive

The error message I get when i try to open is "Could not open...." followed by the path and excel name.

This is the error message i get when i try to save in the shared drive:

Microsoft office excel cannot acces the file". There are several possible reasons:

the file name or path does not exist
the file is being used by another program
the workbook you are trying to save has the same name as a currently open workbook

Please help
 

Answer:Unable to Open and Save Excel files in the shared drive using Excel 2007

15 more replies
Relevance 75.44%

Ok this is a weird problem I ran into in the past with Word, and I'm having trouble finding information on it, because of the details involved.

What's happening is if i load up Excel and then goto file/open and load an excel document it comes up.

If I goto Start/Documents and try to load it from there Excel seems to hang, also if i Browse my computer and goto any excel file same thing happens, and of course it happens over a network share as well.

I didn't give it more then a minute or 2 to come up (We are talking small Excel files here nothing huge) On the last computer this happened on with Word, the documents would eventually come up, but it would take 5 minutes to load this way.

Anything I load withen Excel (Meaning I load excel and then goto File/Open, comes up instantly)

Windows knows how to handle the excel files and what program to load them with, I tried deleteing the (Is it called a mime?) entry in windows that tells excel how to handle the .XLS file, then held shift and Right clicked a .XLS file to re-enter it into Windows, just in case Windows wasn't opening it right but this had the same effect.
Any ideas?
Thanks
 

Answer:Can't open excel files by double clicking, HAS to load withen excel

9 more replies
Relevance 75.03%

I'm trying to make a chart to track the workouts of a group of people over the course of the summer. I want the first column and the last column to be fixed, but i want all the columns in the middle to scroll. How do I fix the last column (their total points) when I don't know how many columns there will be between now and the end of the summer? i only want 4 or 5 columns showing between their names and the total column at the end.

Answer:fix first and last column in excel

Directly from Microsoft Online Help..."You cannot freeze rows and columns in the middle of the worksheet."Why not freeze columns A & B for their Names and Points and scroll the rest of the worksheet for the other columns?Maybe if you gave us some more details as to what you are trying to do with the columns in the middle, we could come up with some other ideas.

4 more replies
Relevance 75.03%

In an Excel spreadsheet I have a column which contains an identifying symbol such as M or O. What is an easy way to change all the M's to, say, O's without doing it one by one or disturbing anything else?
 

Answer:Column changes in Excel

Select the column.
Edit - Find and Replace - Find M - Replace with O - Replace all - Close
 

2 more replies