Computer Support Forum

Need some help with a simple Excel Macro

Question: Need some help with a simple Excel Macro

Good morning all,I am learning how to write a simple Macro program in Excel:A B C=== === ===Jame Apple AppleTom Orange AppleDavid Apple Apple-------------------------------------------Here is the code:Sub testing()For N = 1 to 3If cells(N,2).value=cells(N,3).value ThenRange(corresponding-value).copyRange(corresponding-value).pastespecialEnd IFNextEnd Sub------------------------------------------------------------------------The program will copy specific rows when the cells in colum B equals those in column C,and then it will paste the specific rows of data somewhere in the same worksheet.The program works but it returns empty cells.Here is what the result looks like:=====JameEmpty cellDavid=====Is there any method to skip the empty cells?Please kindly help if you could!KittyP.S. I am using Excel 2002

More replies
Relevance 100%
Preferred Solution: Need some help with a simple Excel Macro

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

Relevance 76.26%
Question: Simple Excel Macro

How do you create a very simple macro to automate a repetitive step or two I'm doing in an Excel Spreadsheet?
 

Answer:Simple Excel Macro

Francine: Did I not include the instructions on how to record a macro in Word when I sent you that email? I'll resend it, if necessary. It's the same steps to do it in Excel. The only difference is that you create the toolbar button AFTER you record the macro. Basically:

Hit Tools-Macro-Record new macro, give it a name. Take the steps you "usually" take, then hit the Stop button on the Record Macro toolbar. To test it, hit Tools-Macro-Macros and run the macro. When you're ready to create a toolbar button, just hit View-Toolbars-Customize, click on the Commands tab. On the left column, scroll down and select macros. On the right, click on the smiley and drag it onto a toolbar. Edit/change the button as directed in the Word Macros document. Close the toolbars dialog. Click on your new toolbar button and it will ask you to assign a macro, double-click your macro name. Hit the button again to run the macro.

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

3 more replies
Relevance 76.26%

Perhaps it's too simple for me to understand...or I'm .....

I want to create a manual macro that will allow me to add 85 to whatever value may currently be in the cell. Nothing automatic...no if/then scenario.

One of those things that I can normally do with my eyes closed but can't seem to make work.

Any help would be appreciated.
 

Answer:Simple Excel Macro Help

8 more replies
Relevance 75.44%

Hello

I'm writing (rather than recording) macros for the first time.

I need to get excel to select a cell on a specific row, within the same column as the current active cell.

I have been trying...
ActiveCell.Rows(17).Select
... but this just shifts the active cell down by 16 rows, rather than selecting the cell in row 17!

I know this should be simple but I am finding Excel VBA help files atrociously difficult to follow.

Hoping somebody can come to my rescue...

Andy
 

Answer:Surely a simple Macro in Excel?

7 more replies
Relevance 75.44%

I need a simple excel macro for my work. I have tried looking it up online but I cannot get how to build one and I am in dire need of one I would love it as well if the person who gives me the macro could explain something about it.
Here is what I need. I need a macro that I can change as needed to write coins in Excel.
Example: you have A1, A2, A3, A4 as 0.25, 0.10 , 0.05, 0.01. These are of course quarters, nickels, dimes...
You have B1-B4 as the number of each type of coins that you currently have. I want a macro that when you put in B1-B4 the number of coins you have to tell you in C1-C4 how many whole rolls of coins(different coins have different rolls, quarters in 40's, nickels in 40's, pennies in 50's..etc) you have 0 to however many and in D1-D4 how many loose coins you have.
Again any help would be greatly appreciated and I would very much so contribute to y'alls site as well
 

Answer:needed simple excel macro

14 more replies
Relevance 75.44%

I'm sure this is a simple task for you boffins out there.

In Col A, I have a list consisting of a series of number ie
24,56,23,98,34,77,01
65,98,12,17,36,99,31
etc
Unfortunately, these lists of numbers are taken from several different sources and some contain spaces ie
24,56,23,98,34, 77,01
65,98, 12,17,36, 99,31
etc
I just need a macro, which I can then assign to a toolbar button, which eliminates these spaces.
All the list is contained to Col A and the number of rows varies from, say, 18 rows up to possibly 25.000 rows.

I'd greatly appreciate any help with this please.
 

Answer:Solved: Excel - Need simple macro

6 more replies
Relevance 74.62%

I need HELP !!!! I need a simple macro that will simply move the cursor around the spreadsheet ( right,left,up or down) without embedding the ending cell address in the macro itself !

EXAMPLE: Macro 1 - to move the cursor (up) 1 cell, to the (right) 2 cells and ( down) 1 cell. The key is, I need the macro to do this each and every time it is executed, in this pattern, no matter where it starts ( assuming it is not blocked by margin constraints )

PLEASE !!!!!!! I'm going crazy !

cheyenne
 

Answer:a simple but complex macro - 2002 excel HELP!

10 more replies
Relevance 74.62%

I'll try to be as descriptive as possible.

Current Status:
I have an excel file with two sheets: sheet1 and sheet2.
Column "A" will have lots of data.
Some rows in column "A" (or the first cell for that row) will have the word "Directory".

Intent:
I need to search sheet2, only in column "A", for the row that contains the word "Directory" and copy that whole row onto sheet1 in the same row. Continue doing that until it completes through all of the rows.

Example Data:

(Before)
sheet2
Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah

sheet1
Row Column A
Row Column A Column B Column C
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 blah blah blah blah blah blah
5 blah blah blah blah blah blah

*Action: Column "A" find "Directory" Copy Row 4 to sheet1

(After)
sheet1
Row Column A
1 blah blah blah blah blah blah
2 blah blah blah blah blah blah
3 blah blah blah blah blah blah
4 Directory cool text huh?????
5 blah blah blah blah blah blah
 

Answer:Simple Excel Macro to Copy Row from one Sheet to Another

"copy that whole row onto sheet1 in the same row"

Do you mean like this?

(make sure what you already have is "safe" before you try, obviously)

Sub test()
If ActiveSheet.Name <> "Sheet2" Then Exit Sub
LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each Cell In Range("A1:A" & LastRow)
If InStr("Directory", Cell.Value) <> 0 Then
x = Cell.Row
Rows(x).Copy
Sheets("Sheet1").Cells(x, 1).Insert Shift:=xlDown
End If
Next Cell
Application.CutCopyMode = False
End Sub
 

1 more replies
Relevance 74.62%

need a simple excel macro to create 31 sheets and name them for the dateexample:sheet 1 is named 05-1sheet 2 is named 05-2sheet 3 is named 05-3etc for the whole month to 05-31

Answer:need a simple excel macro to create 31 sheets and name them

Assuming that you are starting with a "standard" new workbook with 3 blank worksheets, this code will result in 31 sheets named as you requested...
Sub DateSheets()
'Rename Sheets 1 - 3
For sht = 1 To 3
Sheets(sht).Name = "05-" & sht
Next
'Add and Name sheets 4 - 31
For sht = 4 To 31
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "05-" & sht
Next
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 74.62%

G'day Magicians

I am an incompetent Excel macro creator and would appreciate some help.

I have a spreadsheet column with text in it
I want to move every second row two cells right and one row up
I want to delete every third row

Should be easy but I've just wasted two hours trying to do it can someone please help me tonight?

Forever grateful

Mike.
 

Answer:Solved: Simple Excel Macro Required

16 more replies
Relevance 74.62%

Hello,

Can someone help me, I am trying to create a macro in excel that allows me to create a copy of a worksheet, clear its contents then hyperlink it to a particular cell.
The problem is that I want it to link a different page to a different cell every time i.e. create sheet 2(3) and link to R3. create sheet 2(4) link to cell R4, but I can only make it so that it creates the copy, deletes the contents and the links cell R2 with Sheet 2(2), at the moment the coding looks like this:

Sheets("Sheet2").Select
Sheets("Sheet2").Copy Before:=Sheets(2)
Sheets("Sheet1").Select
Range("R2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
"'Sheet2 (2)'!A1"
What would I need to add or change to make it do what I want to?

Thanks Claire
 

Answer:Excel Macro Frustration, I think I'm missing something simple!

Can you post a sample workbook?

Regards,
Rollin
 

2 more replies
Relevance 73.8%

Hi I have a very basic macro I need coded. I have an excel file with Column A,B,C,D,E,F,G Column B = last name of user Column C = first name of user Column D - institution number There are about 5000 users listed who have trained on Equipment X. I need to find which Equipment X trained users are also trained on equipment Y. Column E, F, G are the same setup as B,C,D and list out the folks who are trained on equipment Y (about 3000 users). I need a Macro go through each user and in Columna A...check if user trained on Equipment X is also trained on Equipment Y (which basically entails seeing if a particular B,C,D set of info is found in the list of 3000 users in E,F,G. Some names are idential but their instituion will be different so the macro needs to make sure it checks the complete record (if #B,#C,#D = any #E,#F,#G). If it finds the name put a 1 in the respective column A next to the name of the equipemnt X trained peron. If not put a 0. Does this make sense...any help would me MUCH appreciated!

See attached file.
 

Answer:Simple Urgent Excel Macro Help! Boss is killing me!

Sounds like a good place to use a database instead of a spreadsheet.
 

3 more replies
Relevance 72.98%

Hi All,

I really hope someone can help me. I've been looking through all the forums and haven't quite found a macro code that has worked for me. I know this is very simple, but I have no idea what I am doing. Here it goes....

I have a very simple expense report on Sheet 1 of an Excel (2007) workbook. In this worksheet, the first couple of columns simply list the expense items. However, there is a table with only 14 rows (including the header) of data (6-19). Underneath row 19 is a total row that sums the 2 columns of information (don't need to worry about this row but thought I'd tell you anyway). This table only has 4 columns as follows:

Column G - Code
Column H - Category
Column I - $ (this column is using the SUMIF function to calculate the expense category totals)
Column J - % of Total (this column is simply dividing column I by the Total $)

I don't know if this matters or not but there is also a pie chart linked to this table of information.

I would like this table (H6:J19) to automatically sort by % of Total (in descending order) any time I add new expenses so that I don't always have to sort manually.
If there is a way to do this, I would so appreciate simple instructions. I've attached a sample of what the report looks like.
I hope you guys can help!!!
 

Answer:MACRO NOVICE - Simple Auto Sort using Excel 2007

Hi Janel, welcom to the board

I think this should do the trick, I don't have 2007 here but I used your sheet in 2003

Add the following code the the Sheet1 project

Code:


Private Sub Worksheet_Change(ByVal Target As Range)
Sheet1.Range("G7:J19").Sort Key1:=Range("J7"), Order1:=xlDescending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

To do this just right-click on the Sheet1 tab and select view Code, you will automatically be in the VBAProject.

In the right screen paste the code above.

every time you change values in the list the range G7:J19 without hearders will be sorted by Total % descending order, th epoie chart will do it's own work since it's related to this table.

Happy coding.
 

1 more replies
Relevance 72.98%

I'm trying to write an Excel macro that goes down a column. When it sees an empty cell, it should fill that cell in with the value of the cell that is just above it.

Thanks in advance.
 

Answer:Solved: Simple Excel Macro to Fill empty cells

8 more replies
Relevance 72.98%

Hello,
I'm finally getting near to the end of my mission to get my trading website online but I have one nutty task left to crack with my data, and I could do with a bit of help please.

I have about 6000 products (graphic novels - I run a comic shop) and I have painstakingly put together my related product info in a spreadsheet laid out as in the 'current layout' below. For most of the products there are 4 related products (0,1,2,3) but for some there are only 2 (0,1) or 3 (0,1,2). The 0,1,2,3 simply pertains to the order from left to right the related products are shown on the site.

What I need to do is get them into the 'required layout' shown below so I can just cut and paste them into a specific Access table that already exists in the online trading software.

However, I'm struggling with this as my Excel skills don't really go in macros and loops and whatnot. (I'm pretty chuffed with myself when I use something like concatenate!)

I get the impression this should be reasonably straightforward though, which is very frustrating. Can anyone recommend an available macro or bit of code that does this sort of thing? Or am I just missing some very obvious functionality in excel?

many thanks in advance,
Jonathan
CURRENT LAYOUT (this is meant to be 5 columns - Product Reference, 0, 1, 2, 3 but it doesn't want to tab nicely)

Product Reference 0 1 2 3
9780867196818 9781593073558 9781593075330 9781593077778 9781560979494
978156... Read more

Answer:Solved: Is there an available macro to do the simple rejig of my excel data I require

6 more replies
Relevance 59.04%

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 56.17%

Hey all, i heard this was a well respected forum with good feedback. I need a little guidance...
I have a master file 'Copy and Save.xlsm'. It contains 3 tabs: FR, GM, and CH.

I want to save a copy of each tab to a new Excel file and name the file as Tab name - Master file name, e.g. FR - Copy and Save. xls. The master file is a Macro enabled Excel 2003 format and The new file need to be plain Excel 2003-2007 format rather than a Macro enabled format..(It should be ok to change the master file to Macro enabled format but the new files have to be Excel 2003 format...)

My Macro is able to create a new workbook, save and name it but can't move on to the next tab...

Sub copy_save()
'
' Move and make a copy of each tab from the master file and save as a new workbook. Name it tab name - master file name
'
'
ActiveSheet.Select
pName = ActiveWorkbook.Path ' the path of the currently active file, the master file
wbName = ActiveWorkbook.Name ' the file name of the currently active master file
shtName = ActiveSheet.Name ' the name of the currently selected worksheet, the master file

For i = 1 To Worksheets.Count

ActiveSheet.Select
ActiveSheet.Copy
Tabname = pName & "\" & shtName & " - " & wbName 'Name the new workbook as: Tab name - master file name

Dim Newshtname As String
Newshtname = Tabname

ActiveWorkbook.SaveAs Filename:= _
Newshtname, FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=Fa... Read more

Answer:hi need simple macro help!

Hi, welcome to the forum:

You should checkout Ron de Bruin's site.

The code below is from his site and I edited it to suit my purpose.
I'm sue you can find your solution here too.
Code:

Option Explicit

' Use VBA SaveAs and CheckCompatibility in Excel 2007-2010
' Ron de Bruin (last update 2-Jan-2010)
' http://www.rondebruin.nl/saveas.htm
' Code by Ron de Bruin on his site
' Editted by Hans Hallebeek, February 2010
' Changed code from sub to function and added the filepath and filename as parameters

Function FileSaveAs(TempFilePath As String, TempFileName As String, Optional isNew As Boolean = True)
'Working in Excel 97-2010
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim Sourcewb As Workbook
Dim Destwb As Workbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
'HH: Extra code to save a copy, not a new sheet
If isNew Then ActiveSheet.Copy
Set Destwb = ActiveWorkbook

'Determine the Excel version and file extension/format
With Destwb
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007-2010
'We exit the sub when your answer is NO in the security dialog that you
'only see when you copy a sheet from a xlsm file with macro's disabled.
... Read more

1 more replies
Relevance 56.17%
Question: simple macro

i am looking to gather information about making macros. i know nothing about macros and i would like a simple explaination of what they do, how they are used, what they are used for, how they are programmed. any information can be useful. i have to do a speech on macros and i dont have the faintest idea what they are or topics i could talk about. plz help me asap thanks

i have tried to google it but im not a geek, which is the very reason i came here
 

Answer:simple macro

What kind of macros? MS Office?
 

8 more replies
Relevance 56.17%

I wondered if anyone can help me. I am looking for a macro in excel that will find other occurences of my highlighted in that column.

Bascially atm i enter a new value onto the spreadsheet, i copy and paste the value into "FinD" and then it takes me to the other values with are the same.

Thanks in advance.
 

Answer:Help with Simple Macro

Welcome to the forum. In Excel instead of clicking on "Find Next" click on "Find All." This allows you to display a list of all the text that you are trying to find.
 

1 more replies
Relevance 55.35%

Hi there,

I've been reading loads of forum posted regarding excel macro's, i've been trying for days now to manipulate one of them into my own project with no avail.

this one should be easy and possibly a repeat of another but please would someone be able to shine a light on this one for my i'll be forever endebted

Try a row of data, once complete i want to click a macro button at the side and lock the row that i've just typed.

Kind of like a bit of validation.

here's what i've gotten

Sub Macro2()
'
' Macro2 Macro
'

'
If Range("O4").Value = "Locked" Then
Range("A4:M4").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("O4").Value = "Unlocked"
Range("A5").Select
End If

If Range("O4").Value = "Locked" Then
Range("A4:M4").Select
Selection.Locked = False
Selection.FormulaHidden = True
Range("O4").Value = "Locked"
Range("A5").Select
End If

End Sub
Help?
 

Answer:What should be a simple Macro function?

By default all cells are locked.
Locking and unlocking will not work unless you protect your sheet.
You must also put your macro into sheetchange event, for it to be "auto".
It will be something like this

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long
Dim col As Integer
Dim curRow As Range

col = Columns("O").Column
If Target.Column = col Then
row = Target.row
Set curRow = Range("A" & row & ":M" & row)
If Target.Value = "Locked" Then
ActiveSheet.Unprotect
curRow.Locked = True
curRow.FormulaHidden = True
'Target.Locked = True ???
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
curRow.Locked = False
curRow.FormulaHidden = False
ActiveSheet.Protect
End If
End If
End Sub

Btw, you might want to unlock column O first before triggering the macro.
 

2 more replies
Relevance 55.35%

Excel 2007 on MS Vista machine:
I need help with a very simple macro. I am attempting to create a note entering feature that works as follows:

I enter notes into a cell, click a button and the macro cuts and pastes the notes into a different cell which already contains text. The main focus is to have the macro cut and paste the text to the END of the previously entered notes. The catch is that I have a list of 25 clients next to which I have checkboxes, and a textbox that notes appear in when the checkbox is checked. The macro will have to paste the notes into a correct cell, depending on which textbox is checked.

In addition I have very little experience with VBA and would appreciate to be as detailed as possible. Thank you very much!
 

Answer:Simple macro question...

Hi, and welcome!
Could you post a sample file? It would be easier to find the right macro for you!
 

1 more replies
Relevance 55.35%

I'm just starting to learn how to use macros in excel. And I have a very simple project that I can't figure out. First of all, when I input three numerical values into three connected cells, I would like the macro to automatically move three values three columns over, and add the current static date in time. Secondly, when I input new values into the original cells, I would like the macro to move the previous values down three rows, and move the new values into their former position, and again add date and time. I hope that makes sense, I'm not sure I described it well. Any advice would be appreciated.
 

Answer:Simple Macro Questions

7 more replies
Relevance 55.35%

Hi,

I am attempting to create a simple macro and I am just learning about the use of macros.
What I am trying to do is, after running a report and sorting the data by a particular column, 1, I want to place the information for each separate number appearing (they appear multiple times) onto a different sheet within the workbook. There area 15 different sheets.
Ex. If a 1 appears in Column 1 - that record should go to "name 1" page of the workbook

If a 2 appears in Column 1 - that record should go to t"name 2" page of the workbook
I have created a macro already to perform the opposite Task. Taking records from separate sheets and combining them onto 1 sheet, but doing the opposite seems much more complicated.

Any help or direction would be appreciated.


Thanks,

Felton.
 

Answer:Solved: Simple Macro help

16 more replies
Relevance 55.35%

I'm completely lost... I don't understand the logic of Access.

What I want to do is update a value in a form when another value is changed.

What I have now is:
"Value" - onChange - run macro "25Procent"

This macro does:
SetValue
Item: [Forms]![Tarife]![Value25]
Expression: [Forms]![Tarife]![Value]*1,25

But I always get an error. When I run it in the macro window it reads 'Out of string space' (what does this mean???) ... when I run it in the form window -- it errors as well.

What am I doing wrong? Both values have the same format (euro) --- both have Control Source's from the database (resp. Value and Value25) ... that shouldn't matter, right?

any help is appreciated!
Jazz
 

Answer:Access Macro - this should be simple, right?

Hi Jazz


I found this Article in the Microsoft Knowledge Database looks like there is a workaround to your problem here....

http://support.microsoft.com/default.aspx?scid=kb;en-us;130954


Let us know how you got on....
 

8 more replies
Relevance 55.35%

This is the macro I have from simply recording macro, basically i want to have solve for column c here C1 to be zero by changing cell of column E here E1.

Sub Macro1()
'
' Macro1 Macro
'

'
SolverOk SetCell:="$C$1", MaxMinVal:=3, ValueOf:="0", ByChange:="$E$1"
SolverSolve
End Sub

I would like the macro to do the same for cell C2 changing E2 until the cell C100 when changing E 100

How can I do this? It is my first time using this...
Thanks a lot.
 

Answer:simple question VBA macro

Try the following:
Code:

Sub Macro1()
For i = 1 to 100
SolverOk SetCell:="$C$" & i, MaxMinVal:=3, ValueOf:="0", ByChange:="$E$" & i
SolverSolve
Next i
End Sub

Regards,
Rollin
 

3 more replies
Relevance 55.35%

Hi, I'm new to this forum (actually forums in general) I was wondering if anyone could help me with a macro code. I work with 4 Excel sheets for my shippments, 2 sets for one plant and 2 for the other but all within the same file.

What I am trying to do is set up a macro that will automatically search for changes within sheet 1 (also for sheet 3, the other plant) When I change the rows color from blank to green/red, I need the macro to automatically cut that whole row and paste it into sheet 2 on the next blank row.

This doesn't seem like it would be that hard to do, but I know know the whole macro language.

If you can answer this please send me an email to soulblazor(@)hotmail.com

Thank you for your help.

More replies
Relevance 54.94%

ok im very very new to this, and i wanted to create a simple keyboard macro that did the following commands..

(browser1) backspace + ctrl + tab | (browser2) backspace + ctrl + tab

so it is refreshing (by using backspace) a window and changing to the next tab in a browser window (eg FF) -- this is what it does currently. but i want it so that it does this across 2 browser windows set up exactly the same. and with a hot key to activate it (eg space bar) - rather than having to double click on it from the desktop ;)

this is what i have so far..


Code:
Dim Wsh
Set Wsh = Wscript.CreateObject("Wscript.Shell")
Wsh.AppActivate "Super"
Wsh.SendKeys "{BACKSPACE}^{TAB}"
id appreciate this soo much

More replies
Relevance 54.94%

This is the macro I have from simply recording macro, basically i want to have solve for column c here C1 to be zero by changing cell of column E here E1.

Sub Macro1()
'
' Macro1 Macro
'

'
SolverOk SetCell:="$C$1", MaxMinVal:=3, ValueOf:="0", ByChange:="$E$1"
SolverSolve
End Sub

I would like the macro to do the same for cell C2 changing E2 until the cell C100 when changing E 100

How can I do this? It is my first time using this...
Thanks a lot.
 

Answer:simple question to repeat macro

I don't use Solver, but a simple For Loop should do the trick:
Sub Macro1()
Dim i as Integer
For i = 1 to 100
SolverOk SetCell:="$C" & i, MaxMinVal:=3, ValueOf:="0", ByChange:="$E" & i
SolverSolve​Next i
​End SubClick to expand...
 

1 more replies
Relevance 54.94%

Hello,I have created the macro below. I recorded it so that when I tap the button the macro is attached to it inserts a new row above my designeted cell (IMP_01). One of the cells in the new row also has a formula in it which makes it equal 2 of the other cells in the row multiplied by each other. The problem is the first time I run the macro everything works fine. The second time I run the macro the formula does not work/appear in the new row. In addition, I am using this macro and other similar ones to add new rows and 3 different sections of the same spreadsheet.Any help would be awesome, thanks!Range("IMP_01").Select Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove Range("E29").Select ActiveCell.FormulaR1C1 = "=RC[-2]*RC[-1]" Range("IMP_01").SelectEnd Sub

Answer:Simple formula is not working in macro

First, a posting tip...Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum. As for your question, your code selects E29 and puts the formula in that cell. That's all it's ever going to do since you've hardcoded that range reference. It's going to put the formula in E29 every time you run the macro.In addition, you do not have to Select a range via VBA to perform an action on it. That's very inefficient. You can almost always perform the operation directly on the range within VBA.Try this. It inserts a row above the Named range and then uses the Row property of the Named range to determine where to put the formula. Basically you are letting VBA figure out what row to put the formula in instead of telling it to put the formula in a specific cell.Sub InsertFormula()
'Insert row above named range
Range("IMP_01").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
'Put formula in new row
Range("E" & Range("IMP_01").Row - 1).FormulaR1C1 = "=RC[-2]*RC[-1]"
End Sub
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

3 more replies
Relevance 54.94%

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 54.94%

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 54.94%

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 54.94%

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

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

7 more replies
Relevance 54.94%

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 54.53%

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
 

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 54.53%

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 54.12%

Hello:

Most macro or hotkey software seems to be geared toward launching programs, or opening folders. I do not need or even want that.

Rather, I need to type a phrase a lot, and would like to assign a key to do that.

It would also be ideal if the phrase could be maintained encrypted, but I bet that is asking a lot.

#1) While I think this option is a low probability, it would be ideal:
I understand there is a microprocessor in the keyboard, and it would be great if there were a utility whereby I could reprogram the actual keyboard such that my computer never knows I am not simply typing keys.

#2) second best idea.
Setup Windows such that I do not need a TSR program running in the background to reprogram a key to enter a phrase. Maybe alter the system registry or something. I know this can be done to change a single key to another key, but I don't know about entering a phrase.

#3) have a VERY SMALL TSR that will do the job. Recommendations?

Thanks in advance for any help here.

Mark.
 

Answer:Keyboard Macro WinXP Simple, Free

option #1 - been there done that -kinda. We have to use pass phrases now and all that typing for multiple logins & apps so I made a typing keyboard. Get a teensy duino and program it to type for you. I so need to make a real how to for this, but haven't gotten around to it... there are similar projects on the web but here is the code for it..

void setup(){
pinMode(4,INPUT_PULLUP);
pinMode(5,INPUT_PULLUP);
pinMode(6,INPUT_PULLUP);
pinMode(7,INPUT_PULLUP);
Keyboard.begin();
}
void loop(){
if(digitalRead(4) == LOW){
Keyboard.println("password or phrase");
delay (3000);
if (digitalRead(4) == HIGH)
Keyboard.releaseAll();}
if(digitalRead(5) == LOW){
Keyboard.println("password or phrase");
delay (3000);
if (digitalRead(5) == HIGH)
Keyboard.releaseAll();}
if(digitalRead(6) == LOW){
Keyboard.println("password or phrase");
delay (3000);
if (digitalRead(6) == HIGH)
Keyboard.releaseAll();}
if(digitalRead(7) == LOW){
Keyboard.println("password or phrase");
delay (3000);
if (digitalRead(7) == HIGH)
Keyboard.releaseAll();
}
}

All you do (in this case) is get 4 switches and put one post to ground, and the other post to the pins 4,5,6,7. Change "password or phrase" to what you want it to type. The delay is 3 seconds to prevent "bouncing" on the switch, so you can remove that or shorten it. There is a better way I think with resistor... Read more

3 more replies
Relevance 54.12%

My old freeware program called Launchboard stopped working when moved to Win7.

AutoHotKey is the Swiss army knife of macro programs but requires a lot of coding.

Is there a simple macro program that survived to Win7?

I just want to assign/map a couple of function keys with simple alpha/num strings
for logins and such.

More replies
Relevance 54.12%

hey,
I am trying to learn how to write macros so we can do some engineering analysis. im an intern at my job and was just thrown an excell book and told to figure it out. any helpw ould be great.

Soo i have this idea where you just enter in data on the first worksheet and then then the macro is used it sends it to the second sheet where all of my formulas and analysis goodies are. any ideas for a simple macro for this. My boss wants to make it where if you enter two sets of data it makes two worksheets. is that possibe?
 

Answer:Solved: a simple copy and paste macro help

8 more replies
Relevance 54.12%

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows 7 Home Premium, Service Pack 1, 64 bit
Processor: AMD A6-4400M APU with Radeon(tm) HD Graphics, AMD64 Family 21 Model 16 Stepping 1
Processor Count: 2
RAM: 7650 Mb
Graphics Card: AMD Radeon HD 7520G + HD 7600M Dual Graphics, 512 Mb
Hard Drives: C: Total - 931724 MB, Free - 868986 MB; D: Total - 21840 MB, Free - 2343 MB;
Motherboard: Hewlett-Packard, 18A7
Antivirus: Norton Internet Security, Updated and Enabled
Excel 2007

I wish to sort several rows of about 10 numbers each using a simple macro in Excel 2007.
All rows start in the same column
It appears I have completely forgotten my VBA basics.
Can you please enlighten me and suggest some VBA code.?

Regards

Flynne
 

Answer:Sort a row of numbers (usualy 10) using a simple macro (VBA

Homework, perhaps?
 

2 more replies
Relevance 54.12%

Hi guys,

I would really appreciate your help on this one.

I would like to make simple interactive model by using spin button and make it change value in excel.

Please see attahced example...
Thanks in advance!
 

More replies
Relevance 54.12%

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 54.12%

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 54.12%

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 54.12%

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 54.12%

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 54.12%

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 53.71%

I'm a VBA newbie and would much appreciate any assistance in creating a macro to carry out the following task:

I am analysing several funds. I have spredsheet which calculates certain statistics for each fund when the fund is selected from a drop down menu.

I have an output table with the fund names in the first column, Statistic 1 in column 2 and Statistic 2 in column 3. In order to populate this table I have to manually select each fund and then copy and past the calculated statistics into the table. I would like to a macro which automates the task of selecting each fund in turn and copying and pasting the results into the table. To illustrate the issue more clearly I have attached a sample spreadsheet with some dummy data.

Any assistance would be much appreciated.
 

Answer:Solved: Help needed with simple copy and paste macro

7 more replies
Relevance 53.3%

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 53.3%

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 53.3%

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 53.3%

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 49.2%
Question: excel macro help

I am writing a macro that will clean up a lager exported from an accounting program.

I have the following sheet:
Now:
A B C
1 GL Account-8600
2
3 1 / 2011
4 1121
5 1416
6 1416
7 1416
8 1121
9 1121
10 1121
11
12 2 / 2011
13 1416
14 1121
15 1121
16 1416
I would like cell “A3” copied and pasted until they have an empty cell. In this example, it will be A3-A10 will be changed to the number “1”, and cell A13-A16 will have a number 2. (The amount of rows will vary) I will not have a number greater then 12 months. Also, the empty rows (A2, A3, A11 and A12) and the row below needs to be deleted. I will use this data to make a table in excel, ant the macro has been written for that.

I would like the following outcome:
A
1 GL Account-8600
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
11 2
12 2
13 2
14 2
15 2
16 2
 

Answer:excel macro help

8 more replies
Relevance 49.2%
Question: Excel Macro Help

I am trying automate the process of outputting about 20 rows of data in excel to 20 different text files*1 file per each row*, i have tired several methods but nothing has worked effectively because it doesn't appear to saving exactly what the row contains and its exact formatting. Also i need it to check if there is a file by the name its trying to save as i.e. fc1 to increment to fc2. my current macro:
Sub Macro3()
'
' Macro3 Macro
'

'
Range("A1").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
SaveFileAs "fc", "C:\Users\Illusion\Desktop\Free Content Stuff\"
ActiveWindow.Close savechanges:=True
Windows("CSV FileMaker Macro.xlsm").Activate
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Application.Run "'CSV FileMaker Macro.xlsm'!Macro3"
End Sub
Function SaveFileAs(sFilename As String, sPAth As String) As Boolean

Dim fn As String
Dim check As String
Dim ok As Boolean
Dim index As Long
fn = sPAth & sFilename & ".php"
check = Dir(fn)
ok = (check = "")

Do Until ok
index = index + 1
fn = sPAth & sFilename & index & ".php"
check = Dir(fn)
ok = (check = "")
Loop

ActiveWorkbook.SaveAs fn, FileFormat:=xlTextPrinter
End Function

works but like i said, it doesn't output the file exactly like its formatted which is causing a big problem...

does anyone have any ideas/suggestions that ... Read more

Answer:Excel Macro Help

I think you did it perfectly...
What is the problem then?
Only the formating? try to paste special by retaining formating
Record a macro with past special W/Formating and copy it's code
regards...
 

1 more replies
Relevance 49.2%
Question: excel macro

does anyone know a macro code line to move to the next row...for instance i am in a4 and i want it to move to a5 then a6

thanks
doug
 

Answer:excel macro

8 more replies
Relevance 49.2%
Question: HELP Excel macro

I have two spreadsheets, one with company name listed in column A and the other with company name (column A) and postcode (column B). I simply want to be able to add the postcode into column B in my first sheet. I cant simply copy them over as there are different number of records.

I guess this is quite a simple matching exercise and if anyone knows a macro to do it I would be mightily grateful.

Tx
 

Answer:HELP Excel macro

I don't think you need macro to do this. Function VLOOKUP should do fine, try it.

Jimmy
 

1 more replies
Relevance 49.2%
Question: Excel Macro's

Hello,

I have developed an approx 50 page (work tabs) spreadsheet in Excel 2003 (Windows XP).

The workbook contains three main macro?s and after several manual inputs are performed the salaries for the organization are projected.

When I send out the report to the applicable parties, I hide the other 49+ tabs and then protect the workbook with a password to ensure the other parties cannot see the other areas information.

Due to the size/complexity of the document I am unable to delete the tabs (takes a long time) and that is why I have hid the rest of the tabs.

Each tab runs on the three main macros and the figures that are generated are rolled up in summary pages for the applicable areas (these summary pages do not used the macros)

Question #1: in a complex workbook is there a way to delete the tabs quicker?

Part 2:

A majority of the parties still use Windows 2000 (Excel 2002) due to other applications that are based around this operating system.

They are able to update/save the report and any changes that are made are accurately reflected. However, when the Excel 2002 user saves the document and sends it back to me it takes an extremely long time (45 mins) for their report accurately show the figures. This message occurs in any cell that requires the macro:

#NAME?

Another piece of information that I obtained said:

Microsoft Office Excel re-calculates formulas when opening files last saved by an earlier version of excel.

This leads me to believe that since the... Read more

Answer:Excel Macro's

Your problem reads like there's a lot of unstated constraints in your situation, like resources and procedures for distribution, management, return, collation. Too much for me. I'm looking here just at a more efficient message setup operation.Sounds like much of your overhead irritation relates to bothway transmission of large quantities of irrelevant (even though hidden) data. Here are three lines of thought:1. Another macro to generate 50 individual spreadsheets from your workbook.2. Rework your base dataset in EXCEL to bare-bones tabular form, to enable mailmerge into a user-friendly WORD template. I've done this with 50 data fields into a 20-page 85-client report template, and it's a ho-hum WORD mailmerge task once you get the master doc looking pretty.3. Take this on to include the transmission stage with the article "Integrate mail merge in Microsoft Word with Excel" at TechRepublic, at http://techrepublic.com.com/ The direct approach http://search.techrepublic.com.com/index.p...l&go=Search might even work.I'm afraid any of those options is a week's work, but that's why we're paid so well.

1 more replies
Relevance 49.2%

Hey everyone,
i need some help with vb/macro in excel
i got two integers, lets say Value1 and Value2.
Value1 = 2
Value2 = 4.
i want the macro to goto a cell where the column is Value1 and the row is Value2.

is it possible?
 

Answer:Excel VB/Macro help

7 more replies
Relevance 49.2%

Hi,
I have been working on getting multiple files to open in excel and maybe even organise the data in the format i want. A number friends have helped and here is what I have:

Public Sub open_files()
Dim intIndex As Integer
Dim fsSearch As FileSearch
Dim fsoFile As FileSystemObject
Dim tsmContents As TextStream
Dim strPath As String
Dim wksSheet As Worksheet
Dim intCount As Integer
Dim fdDir As FileDialog

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set fdDir = Application.FileDialog(msoFileDialogFolderPicker)
fdDir.Show
fdDir.AllowMultiSelect = False
strPath = fdDir.SelectedItems(1)
Set fdDir = Nothing

Set fsoFile = New FileSystemObject
Set fsSearch = Application.FileSearch
fsSearch.LookIn = strPath
fsSearch.Filename = "*"
fsSearch.Execute
If fsSearch.FoundFiles.Count > 0 Then
For intIndex = 1 To fsSearch.FoundFiles.Count
intCount = 0
Set tsmContents = fsoFile.OpenTextFile(fsSearch.FoundFiles(intIndex))
Set wksSheet = ActiveWorkbook.Worksheets.Add
wksSheet.Name = fsoFile.GetFileName(fsSearch.FoundFiles(intIndex))
Do
Call Import_Contents(tsmContents.ReadLine, intCount)
intCount = intCount + 1
Loop Until tsmContents.AtEndOfStream
Next intIndex
End If

Set fsSearch = Nothing
Set wksSheet = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

The trouble is the code is not really working in my worksheet as I keep getting compile errors.

AS I described before, I want the... Read more

Answer:Help with Excel macro

What does the Compile error tell you?
 

3 more replies
Relevance 49.2%
Question: Excel Macro Help

Hello All,

I am using Access 97 to generate a database. After entering data into forms in Access, the user then clicks a button which opens a report in excel. I have created a macro in excel which should automatically maximize the worksheet in excel. This macro also changes the active worksheet to "sheet2". The part of the macro which changes the active worksheet is working fine when I load the report through access, but not the part of the macro associated with the maximization of the window. Both parts of the macro work if I open excel on its own (ie: not through access). Below is my code:

Sub auto_open()

Worksheets("Sheet2").Activate
ActiveWindow.WindowState = xlMaximized

End Sub


Any suggestions?

Thanks in advance for your help.

engineer_in_training
 

More replies
Relevance 49.2%
Question: Excel Macro Help

I don't know anything about visual basic, and have no programming experience, but am skilled with computers in general.

Would someone please help me with a Macro for Excel?

All I want is for it to send an email with an attachment (same email address, same attachment) every Monday-Saturday at 10:00 PM

Thanks!

(My email is [email protected])
 

Answer:Excel Macro Help

10 more replies
Relevance 49.2%

First off, I am trying to create a balance sheet for a check cashing store. I have 4 seperate Teller Balance Sheets, and a main sheet where I want the values to be entered.

The problem:

On the main sheet are a bunch of categories that ask the employee what they have done during the day (Money Orders, Checks Cashed, Etc.) On the right hand corner, I have 4 buttons which would direct the values over to their appropriate sheet. What I have tried to do was copy and paste the values over while recording a macro, but as soon as the original value was deleted, everything would go blank.

What I want to do:

I want a way where I can enter all the values, click on a button, and have those values go from the main sheet over to one of the teller sheets. From there, I could go back to the main sheet and proceed to do the next 3 employee's sheets.

If this is too confusing, let me know.

All help is appreciated.

-Jon
 

Answer:Excel Macro Help...Is this possible??

6 more replies
Relevance 49.2%
Question: Excel Macro

Hello all, long time since ive posted, need some help from you guys again please.

i have made an excel macro, on the excel page you have a drop down menu which you can use to select an option from. When you have selected something numbers appear in the cells below.

the problem is that i want to be able to protect those cells in which the value appear, i dont want users to be able to modify these cells, but when i protected the sheet the program cannot write to the cells and therfore i get an error.

is there a way around this please?

Thanks all.
 

Answer:Excel Macro

Here's what I would do.
1. Put those cells on a separate worksheet. Call this sheet the "Descriptor"
I guess those cells are the controlsources of dropdown lists, listboxes, etc. I would put the rowsources of the controls on the same "Descriptor" sheet.
2. Put the controls themselves on a userform.
3. Hide the "Descriptor" sheet.
4. Protect the workbook with a password.

This way the users can't unhide the "Descriptor" sheet, so they can't modify any data there. They also won't be able to see the data on the "Descriptor" sheet, but that's not a problem, since they have it in the listbox or dropdown list. But you can read/write it via VBA code. Does that suit you?
 

2 more replies
Relevance 49.2%
Question: Excel macro

Excel macro

A friend has a spread sheet he receives every week. He has to copy info from that sheet and past it into a new Excel sheet in a different order and then save the new sheet as a "prn" file.

He wants to create a macro (or two) that will do as much of the following as possible.

The following cells are always the same.

He wants to copy the following cells and then paste each cell area below the previously pasted cell area with one blank cell between each cell area.

The "pasted" info is then saved as a "prn" file.

Here's the cell info he's coping.

D7-F18
D21-F32
H7-J18
H21-J32
L7-N18
L21-N32

I can create a macro but this is out of my league.
Questions? Solutions?
 

Answer:Excel macro

7 more replies
Relevance 49.2%

Need help with some Excel VBA as I am not very good a programming!
I have a long list of records in a single excel spreadsheet and I would like to move a whole row based on the the data content in one of the cells to a new workbook with the cell data as the name of the work book.

bouitac
 

Answer:Excel VBA/Macro Help

16 more replies
Relevance 49.2%

Hello,

Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have two colums A & B. I want to be able to find the value from Column A and replace it with the value in the same row in column B. The values are diferent sizes.

Thank You
 

Answer:VBA Excel Macro Help

Welcome to the forum microfire.

Can you provide a sample of dummy data, with a little more info - perhaps a before and after of what you want achieved.
 

1 more replies
Relevance 49.2%
Question: Excel Macro Help

I'm working on creating a macro in Excel to automate some of my daily tasks. For the most part, I was able to make a successful macro using only the record function. However, I ran into a snag when I tried to get the macro to record my Ctrl + F function. I don't have too much (read: any) experience working with either VBA or macros, so I was wondering if someone here could help me out. This is what I would like for the macro to do
1. Search for cells with the value "12345"
2. Highlight all of the cells with the value "12345"
3. Delete all of these cells, and shift the remaining cells up
Any help would be greatly appreciated!
 

Answer:Excel Macro Help

Hi and welcome to TSG

Here's a little macro for you:

Code:
Sub Delete_12345()
Dim Rng As Range, WS As Worksheet, DummyString As String

'this DummyString is just necessary, you will see why :)
DummyString = "gibhu, etho et&#337;zohketok&#337;u&#337;ou&#337;(=%/HN "

'modify the sheet and cell range as needed
Set WS = Sheets("Sheet1")
Set Rng = WS.Range("A1:J10")

'changing/comuflaging the empty cells by filling them with a dummy string
Rng.Replace what:="", replacement:=DummyString, lookat:=xlWhole

'clearing the cells we look for (change all "12345" values into empty string)
Rng.Replace what:="12345", replacement:="", lookat:=xlWhole

'deleting the new empty cells (note: they were 12345 values just a moment ago)
Rng.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp

'recreatig the original empty cells by replacing the dummy strings by empty strings
Rng.Replace what:=DummyString, replacement:="", lookat:=xlWhole
End Sub
I'm sorry for leaving out Step 2., but highlighting the cells with the value "12345" doesn't make much sense, if they are all to be deleted anyway...

Jimmy
 

1 more replies
Relevance 49.2%

I'm trying to update (& create) some macros in Excel (for WinXP) and all of a sudden it's using absolute values instead of relative values. Fine, okay, I select the relative value button and try again, but now the macro is skipping a column between each entry, when it should just be going to the next column. I'm at my wit's end and I need to get this fixed NOW. Help!!!!
 

Answer:Excel Macro - Help!

13 more replies
Relevance 49.2%
Question: Excel Macro Help

I have a complex database file that the users require to be in excel. I have created a master part list and the attached sheets in the workbook are for loading Bill of Materials (BOMs) into. The attached BOM sheets have formulas which reference the Master Part List via VLOOKUP formulas and there are also complex macros. I would appreciate help with some or all of the following:

1)find the lowest price on the parts master
2)create an order of operations with which to populate one column on the BOM sheet (for example: first search for most recent price, if not then lowest price, if not then a particular vendor, etc...
3)create buttons to execute 2)

I know I am asking for a lot and if anyone can help with a small fragment of the above, I would be so eternally grateful!!!
 

Answer:Excel Macro Help

Please don't post duplicate threads, you're already being helped here: http://forums.techguy.org/business-applications/529763-excel-database-help.html
 

1 more replies
Relevance 49.2%
Question: Excel Macro

I have turned on the Macro to automatically update a spreadsheet every time it is opened. I need to be able to stop this so I can save a copy and add notes to this worksheet. How do I disable this macro? Even when I save it as another name, when I open it next time is updates the data.

I am using Excel 2000.

Thanks
Nancy
 

Answer:Excel Macro

Generally, holding the shift key while opening an Excel file that contains workbook_open or auto_open macros should disable it from running until you close and open again, this time without using the shift key.
 

3 more replies
Relevance 49.2%
Question: Excel Macro help

Hi guys, i was wondering if you could help.

I simply cannot get my macro working,

I need the macro to take the value from a cell, e.g. "D9" and search for it in a data set which is in A5 to A9, once found it then needs to offset by two collums and enter a number 1 into the column.

I know this sounds simple, but i have given up hope

Please help

Thanks in advance

Jon3sey
 

Answer:Excel Macro help

6 more replies
Relevance 49.2%
Question: excel: macro help

hello, i got a little problem with my macro i wrote... simply put, i wrote a macro to parse through data from a transportation program and it gives me back certain rows and columns which im interested in.

now i want to go through each cells by row and columns, and search for "<" and ">" signs in each cell.
for example,
c3 c4 c5
r2 EBT 1> 15
r3 EBR 1 176

so i search with for r to lastrow step 1 and for c to lastcolumn step 2
if i have >, then it will take the first 2 letter of th string in r2c3 (hence "EB"), and save it to a string named temp. Then it will add "L" or "T" or "R" to the temp string depending on whether "<" or ">" are present.
and then i will replace r2c3 with the temp string.

that was the logic, and now th eproblem
i worked thru the whole logic part, but i got an object error when i run thru the program
the line was something like...

(line which selects cell r2c3) <--- forgot...
cell.value = temp

i havent used vb in a while so im pretty clueless as to how to fix it...
can anyone help??

thx in advance

oh just forgot... another thing i want to do is change the value in c5, so say if I search thru the column and row, and found > in r2c4. THen i would want to add the values r2c5 and r3c5 together and replace the value in r2c5 with the new value. i can probably figure out how to do it myself after some helpful person teaches me the proper way to replace th... Read more

Answer:excel: macro help

It's too general. Please copy here the error message and the problematic code section.
 

1 more replies
Relevance 49.2%
Question: Excel Macro Help

Looking for some help trying to create a macro that could complete the following example (Each row is a row in Excel, and each large spacing is a column):

Going from...
IDxxxxx Region 1xxxxx Region 2xxxxx Region 3xxxxx Channel 1xxxxx Channel 2
1xxxxxxxx Xxxxxxxxxxxxxxx xxxxxxxxxxxxxxXxXxxxxxxxxxxxxxxXxxxxxxxxxxxxxxx X

To this...
IDxxxxx Regionxxxxx Channel
1xxxxxx Region 1xxxxxChannel 1
1xxxxxx Region 1xxxxxChannel 2
1xxxxxx Region 3xxxxxChannel 1
1xxxxxx Region 3xxxxxChannel 2

Any assistance would be greatly appreciated.
Thanks,
 

More replies
Relevance 49.2%
Question: Macro VBA Excel

View attachment Example 1.xls
View attachment Example 2.xls
In the worksheet "example 1" macro "sum columns" sum the columns values, but only if the code is numeric. As I do so that it works on worksheet "example 2" whose alphanumeric codes. Attachment both worksheets.

Thank You
 

Answer:Macro VBA Excel

Feliç Nadal

I simplified your code, it's easier to read this way.
 

2 more replies
Relevance 49.2%

I'm needing to create a macro so that excel auto saves as a PDF given a range of cells (so that it'll only save pg 1 of the document and not the whole sheet). I've got this portion figured out but I also would like a specific cell to be the filename that gets saved. Also, is it possible to have the PDF automatically open after saving? I know you can do that in excel when not using a macro, but is it possible with doing the macro also?This is what I have so far...Sub SaveAsPDF() Dim strFileName As String strFileName = Application.GetSaveAsFilename(FileFilter:="PDF files (*.pdf), *.pdf") If strFileName <> "False" Then Sheets("Sheet1").Range("A1:E50").ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFileName End IfEnd SubI know NOTHING about VBA's or macros and have derived this from many google/youtube searches. Hopefully this isn't too much to ask...

Answer:Macro excel to PDF and more

Try this...
Sub SaveAsPDF()
Dim strFileName As String
Dim myShell As Object
'Use A1 value as filename
strFileName = Range("$A$1")
'Export Range
Sheets("Sheet1").Range("A1:E50").ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=strFileName
'Open PDF
Set myShell = CreateObject("WScript.Shell")
myShell.Run strFileName & ".pdf"
End SubClick Here Before Posting Data or VBA Code ---> How To Post Data or Code.

10 more replies
Relevance 49.2%
Question: Excel Macro

I have two worksheets within the same workbook. WkSheet "Data" as my source and wksheet "Form" that I need to populate from the source.

I need to be able copy the quantity from my "Data" wksheet along column A, starting with A5 to my "From" wksheet in column A, starting with A9.

I need the quantity from the 'Data" wksheet to be pasted to the next open cell along the column A in my "Form" wksheet.

Can someone please help!!! Thanks.
 

Answer:Excel Macro

Hi, welcome.
A 'picture' tells more that a thousand words, but it's quite simple.

The other thing is; Which version of Excel are you using?
Code:

Sub CopyData2From()
Dim xRow As Long, yRow As Long
xRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
yRow = Sheets("From").Range("A" & Rows.Count).End(xlUp).Row + 1
Sheets("Data").Range("A5:A" & xRow).Copy
Sheets("From").Range("A" & yRow).PasteSpecial
Application.CutCopyMode = False
End Sub
 

2 more replies
Relevance 49.2%
Question: Excel Macro

I think what I'm trying to do is a macro, and anyways, I know you can do it all the way back to Excel 95.

Here's what it is, it's so simple:
1) I have 3 spreadsheets
-a) Master list of all accounts
-b) terminated accounts
-c) active accounts

I need to make a) search both b) and c) and color a row according to where it finds the name.

There are a couple other provisos/problems but I can get that sorted out by hand, or work on it later, the vast majority of the data is just going to be "Is it here? No. Okay, is it there? Yes, okay, color it blue"

I tried to google some walkthroughs, but like I said, I'm not even sure if "macro" is the right feature that I'm looking for.

Answer:Excel Macro

You will want to use Live Search and Office Online to find answers for Microsoft Products. Google doesnt spider Microsoft sites.

how to create Excel Macros - Live Search
http://office.microsoft.com/en-us/ex...047111033.aspx

5 more replies
Relevance 49.2%
Question: Excel VBA/Macro

I am trying to design an automated shopping list for Housebound elderly and disabled persons.

In Excel I have a shopping list that is multiple worksheets with one column and then with different categories for each worksheet. Ie. Produce, Dairy, Meat, Baking, Staples, Canned, Frozen, Cereals and so on

To use it I want to use VBA or a Macro to Turn the Filter on, let the user select the items wanted from the list. Copy the filtered list to the WorkSheet "ShoppingList" then go to the next work sheet and again turn on the filter and select the items the client wants on the shopping list then copy the filtered results to Column B of WorkSheet "ShoppingList". I want to do this for all the worksheets. Produce, Dairy, Meat, Baking, Staples, Canned Goods, Frozen Foods, Cereals, and so on.

When I try to make a macro it records the items selected when the filter is turned on. I need the macro to pause at that point while the individual items on the list are selected and then continue once ok is pressed.

Any one know how to edit the macro to make it work correctly?
 

Answer:Excel VBA/Macro

16 more replies
Relevance 49.2%
Question: Excel Macro Help

Here is a snippet of my code....
Code:

If ActiveCell.Column = 3 Then
FileName = Target & ".m4v"
FilePath = m & ":\Movies\" & Range("C1")
RetVal = ShellExecute(0&, "open", FileName, vbNullString, FilePath, 1&)
Application.Quit

What I would like to figure out is....if the shellexecute does not open the file due to the file extension is not correct, open the same file with different extension.

Can this be done?
 

Answer:Excel Macro Help

6 more replies
Relevance 49.2%
Question: Macro in excel

I would appreciate help with a macro to separate numbers in a line of text within a cell, and also to change the value of that number,
for example remove the text and keep the number or
remove the text and change the number to another value

Mining & Energy Plate-C3
Ryan & McNulty Sawmilling (68)
Black & Black - Bm65

Thank you
 

Answer:Macro in excel

6 more replies
Relevance 49.2%

I have a macro written in VB that works but not "perfectly".
Basically it looks at a range of cells and replaces X text with Y text. Very simple.
The problem that I have is I would like for it to replace the text with a literal match.
For example:
Replacing "B" with "BOMB" on a row that has "BOMB" on it would result is "BOMBOMBOMB"

What happens is it takes every "B" and replaces it with "BOMB".
What I would like it to do is to replace cells that have literally only "B" in them with "BOMB".
If a cell has anything else than just "B" it is ignored.

Here is the code:

Code:

Sub Replace_ALL()
Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
Cells(r, c).Value = replace(Cells(r, c).Value, "B", "BOMB")
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

Answer:Excel VB Macro help...

Here's a quick and dirty solution, just test for the length of the cell before substitution:
Code:
Sub Replace_ALL()

Dim lr As Long, c As Integer, r As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
End With

lr = Cells(Rows.Count, "A").End(xlUp).Row

For r = 1 To lr 'cycle rows
For c = 1 To 1 'columns A to A
If Len(Cells(r, c).Value) = 1 Then
Cells(r, c).Value = Replace(Cells(r, c).Value, "B", "BOMB")
End If
Next c
Next r

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
End With
End Sub
 

2 more replies
Relevance 49.2%

Hi,
Could someone tell me if a macro would be capable of the following?
A file contains a number of spreadsheets, each of which has three columns of unique data. I would like each set of data (=3 columns) listed all together on one spreadsheet (preferably in a different file), separated by an empty column for clarity. Previously I've just copied and pasted the relevant columns, but now the number of spreadsheets per data file is upwards of 40, and it is becoming highly non-productive.

I have looked online for macro and VB coding help, but I think I am a bit out of my field...I know a bit of HTML, but this seems very different.

Thank you for any help,

nezhka
 

Answer:Will a macro do this? (Excel)

Yes, a VBA macro will do it, but you'll have to work at it a little. The following site has some excellent help:

http://www.xl-logic.com/

Included on that site are two examples that will give you most of what you need, but you'll have to tailor it to your requirements. Click on MAIN MENU, then VBA Code (Macros). The two files you want to download are:

consol loop.zip
consol mckillop.zip

They contain VBA scripts which should give you the basics for what you need. If you're not too familiar with VBA, then stick with something like in the consol loop script. It's simple, fast, and not too difficult to understand.
 

2 more replies
Relevance 49.2%
Question: Excel Macro Help

Hi I am wondering if someone could help me with an excel macro.

Basically I want to search a worksheet between two columns to see if t shirts exists between shorts & jeans and to exit if it does or if not insert a blank column.

Thanks Jumbo
 

Answer:Excel Macro Help

Jumbo , do you want to select T shirts from the excel sheet?
Just press ctrl and F,then type "T shirts ",you will get the results.
 

3 more replies
Relevance 49.2%

I'm trying to use an if statement and I can't get Excel to recognize a formula similar to above. I need it to read if this range is greater than value A AND less than value B, then do this. How do I get the if statement to recognize two constraints? Is there a "between" function? I've searched all over the help file and I can't make it work. Please help. Thanks.
 

Answer:Excel Macro - 0 < X < 10

6 more replies
Relevance 49.2%
Question: EXCEL macro's

OK here is my problem i have i would like to select a range of cells then copy them on to a new sheet. the problem is that the range is different every time due to qty. of items. But the 10 row header before & the symbol " " at the end is consistent.

I would like all the row's between the header and the symbol. any suggestions
 

Answer:EXCEL macro's

Stop posting duplicate threads! You already have posted at least three of these! Stick to one thread and just maybe it'll last long enough to get solved!

Continue here: http://forums.techguy.org/business-applications/532303-excel-usage.html
 

1 more replies
Relevance 49.2%

I am designing a ?basic? database in work and having trouble with some macrosThe database is a record of production. Information is added at pre and post production. Adding the pre-production information I have sorted. A form is filled in, submit button hit and a new row is created with the information in. Along this row there are gaps where post production information is entered, but trying to get this information to paste into the correct row I cant do.I have a form to fill in the information (one each for pre and post production) which I then want adding to the database against the reference number. Converting the information from the input form into the database I can do for pre production, but getting the post-production data entered into the database along the correct row I don?t know how to get there.I believe I want a search function that takes the database reference number (on the post-production form) and finds this in column A. once this ref number is found, the cell is active and I can to paste the information from the post production form into the gaps on the correct row Anyone have a clue?

Answer:help with Excel macro

Can you clarify a point here?  Your title is "help with Excel macro".  Excel is a spreadsheet program, yet you refer to a database over and over and do not once mention a spreadsheet or other terms, such as Workbook or worksheet, which imply Excel.  Macrosoft Access a database and is part of the Pro version of Microsoft Office.  So, are you using Access?  If not, I'm curious why you keep referring to database when you are using Excel, a spreadsheet program.  Or, are you using both for that application you are working with? 

10 more replies
Relevance 49.2%
Question: Excel Macro

I have values entered in the C column that look like the following

'MAK, REP'

I need to find a way to make the values like the following:

'MAK','REP'

Any suggestions would be very much appreciated.
 

Answer:Excel Macro

14 more replies
Relevance 49.2%
Question: Excel Macro Help

Hi all,

I've recorded a macro where I switch from one sheet to another to copy something and then switch back to the previous sheet to continue working, so in the macro it's recorded as:

ActiveWindow.TabRatio = 0.473
Sheets("Template").Select
Range("A1:X31").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveWindow.ScrollColumn = 2

Can anyone help me to edit this so that, rather than selecting "Sheet1" again, it just selects the previous sheet, without needing the name of the sheet to be "sheet1" everytime? This way I will be able to run the macro where I jump to "Template" and return back to whatever sheet I was working in originally, whatever the name is. Make sense?

Hope someone can help!

Thanks!
 

Answer:Excel Macro Help

Do you have at least a little vba knowledge?
You can use variables and assign these to the sheet's names
For eample:
Dim ws1 As worksheet
Dim ws2 As worksheet
set ws1 = sheets("Template")
set ws2 = Sheets("Sheet1")

to select one or the other ...
ws1.select
ws2.select

many time you do not have to change sheets, you can address them anf take the data from one and copy it to another

another example:

ws2.Activate
ws1.range("A1:X31").copy
ws.Range("A2").Pastespecial

Play around with this I can't tell you much more becasue I don't know your sheet's layout etc.

I don't undestand the TabRation and the ScrollColumn=2 can be changed too.
 

3 more replies
Relevance 49.2%
Question: Excel Macro

I am using a program called MUST. It is for the electrical transmission industry. It uses excel to do its computations. It runs macros based on the buttons that were pushed on a form. It calls a .xla spreadsheet that has all of these macros. The worksheet is password protected. The outputs are excel based worksheets.

It has a feature to allow you to record the buttons that are pushed in it own .asc file format. If you run the code within this file format it gives you back a text file that is very hard to use. If you try to do the same function more than once it writes over top of the text file. It works while in excel but if you run it in .asc format it overwrites the file.

I would like to be able to record the buttons I press in this form in python so that I can recreate my steps quickly. Everything in this program is password protected, so I can't do a call on button push, because I don't know what the buttons are called. Excel will not record what I do within must.

I am thinking of possibly a program that can record various buttons I push. If possible I would like it to be python based, because that is the main program in my industry. If you have other suggestion, I would greatly appreciate the input.

Any suggestions would be greatly appreciated.
Thanks,
Joe
 

More replies
Relevance 49.2%
Question: Excel Macro

I have a CSV file that exports to excel and produces and sorts various pieces of data with some simple macros, what I am then left with is a sheet of information that I need to paste into a MSWord table, the table already has the font and style formats that I need, but because the data is based on the information from the Excel workbook I cannot predict the number of rows needed, although this number is displayed in a single cell in the workbook. When the table has been pasted I would need to be able to rename and save the Word document using a week number, displayed in the workbook as the file name. Is it possible to write a piece of VBA code to handle this?
I should have said I am running office 2002
 

Answer:Excel Macro

8 more replies
Relevance 49.2%
Question: Excel Macro

So I'm pretty green with the Macros. Here is my question. I want to write a quick macro to insert rows in file. Basiclly there is a lot of data and I want Excel to search in a column and when it comes on a new number it stops and inserts a row above the number. The goal is to separate Item Data by Part number.

I'm working on a business platform with Windows & Office 2000.

Thanks!
 

Answer:Excel Macro

9 more replies