Computer Support Forum

MS Access 2007 - Macro > Excel > Auto Email

Question: MS Access 2007 - Macro > Excel > Auto Email

Hi there - Using MS Access 2007.
I have a FORM with a macro that runs a query to generate ONE excel file and attaches to email using MS Out look to send out. - That Works great.

Problem: Multiple users to send email & attachment.
I would like for the macro to generate excel file (S) - based by the UID.

Can someone suggest a better way of handling this that is not time consuming?

Thanks In advance!

Relevance 100%
Preferred Solution: MS Access 2007 - Macro > Excel > Auto Email

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

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

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

Answer: MS Access 2007 - Macro > Excel > Auto Email

Access can open Outlook and send an attachment to mutiple recipients either as a list or as individual emails.
So are the attachments all the same for the recipients?

1 more replies
Relevance 92.25%

Hello all;

I am new to this forum and also to Excel macros. I have a large Excel file detailing financial accounts with my small business.
Anyway, I would like to email any customer who has an overdue balance as per column V in my attached sample file (any customer with a greater than zero value in V). I would like to send an email; something like this:

Joe Wilson;
As of 2/28/11 (latest date in the row that is on/after the current date), your account is currently overdue and you owe $1500.00 (G3).
Thank you.
Treasurer
I found several other threads explaining how to send an automatic email from Excel, but I haven't found one for sending an individualized email to a list of recipients. If this question has been answered, please feel free to redirect me. Otherwise, I'd appreciate any suggestions. Please let me know if I can detail my needs any better.
Thanks!
 

Answer:Excel Macro: Auto Email

welcome to the board.

I put some code in the sheet

The macro name is CheckAndMail

Let me know if this is what you need

PS: I just corrected something I came accross and have re attached the corrected sample
 

2 more replies
Relevance 91.84%

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

I'm trying to run an Excel 2007 macro from Access 2007, when I get to the point where I want the macro stored in the active workbook I'm getting the following run-time error: run-time error '91' object variable or with block variable not set . It won't run the macro at all. I would greatly appreciate it if someone could help me fix my code so the macro will run. The code is listed below; step 10 is where I get the error.
Code:
Option Compare Database
Sub GetJournal_Entry_Data_transfer_to_Excel()
'Step 1: Declare your Variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MyQueryDef As DAO.QueryDef
Dim MyDatabase As DAO.Database
Dim MySQL As String
Dim MyRange As String
Dim s As String


Dim Db As Database
Dim xl As Excel.Application
Set xl = CreateObject("Excel.Application")
Dim xlwkbk As Excel.Workbook
'Dim xlworkbk As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim i As Integer


'Step 2:Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info = False;Data Source= P:\FINANCE\Balance Sheet\Inventory\Project TAN\Project TAN.accdb; User ID = Admin;"

'Step 3: Build Your SQL Statement
MySQL = "Select* From [mtb-TantasticJE's]Where [mtb-TantasticJE's].[Dscrptn_Text]='Culls_Stat34'and [mtb-TantasticJE's].[Co_Code]='1381'"

'Step 4: Instantiate and specify your recordset
... Read more

Answer:Access 2007 VB code to run Excel 2007 macro in active sheet

7 more replies
Relevance 88.56%

Hi:
I am very new to Excel 2007 and macros. I have a spreadsheet that I am trying to get to send an email reminder to the point of contact [ col b ] 5 days prior to the closure document due NLT date [ col m ]. I am looking for assistance in writing a macro which will accomplish this if it is possible. I have attached the spreadsheet that I referenced.
Your assistance would be greatly appreciated.
Thanks in advance.

desantisj
 

Answer:Excel 2007 Macro to Send Reminder Email

desantisj, welcome to the Forum.
There are already 3 or 4 posts on this forum that have the VBA code (Macro) that you can modify for your Workbook if you can read the code. Zack has written the code so it is a bit complicated, but it should be a case of substituting your Cell references that hold the data for the ones that others have used.
Otherwise it is a case of waiting for an Excel guru to come along and help. If none of them come along I can probably help you, but I normally work with Access.
 

2 more replies
Relevance 88.15%

Hi all

Looking for some help! Working on an excel document which contains a button I need to send to multiple people, that's fine what I need is for the subject field of the email to take the contents of a particular cell in the workbook and display that. My current code is;
Code:
Sub SendThis_ActiveWorkbook()

Dim Recip()
With ActiveWorkbook

ReDim Preserve Recip(0)
Recip(0) = "[EMAIL="[email protected]"][email protected][/EMAIL]"
ReDim Preserve Recip(1)
Recip(1) = "[EMAIL="[email protected]"]example[/EMAIL][EMAIL="[email protected]"][email protected][/EMAIL]"
.SendMail Recipients:=Recip, _
Subject:="Vacancy Authorisation " & Format(Date, "dd/mmm/yy")
End With

End Sub
Basically I want the subject to be "Vacancy Authorisation [cell contents] 01/01/2000" so I want the contents of a cell to appear in between the date and the "vacancy authorisation" part of the subject line. Assistance appreciated!
 

Answer:Solved: Excel email macro auto-populate subject line

Subject:="Vacancy Authorization " & Range("A1").Value & " " & Format(Date, "dd/mmm/yy")

Regards,
Rollin
 

2 more replies
Relevance 85.28%

First time poster so bare with me.

My issue is: My customised QAT in Excel 2007, that includes 'not in ribbon' buttons, doesn't save when I add a macro button for default or specific docs.



The macro button and the macro will work fine when I add it, but when I close and reopen the file or email the file, the button has gone

The bizarre thing is, it was working a few weeks ago. I even had some customised macro buttons in the file that I was trying to add more to, and when I saved and reopened the file, ALL the custom macro buttons disappeared.

I've already searched google for days now trying to find a solution.....

I've read ron debruin's site back to front http://www.rondebruin.nl/qat.htm and http://www.rondebruin.nl/qat2.htm

The instructions are there for the process but no solution as to why the button wont stay put.

I'm running Windows 7, and obviously Office 2007. Also, this is a work PC on a network (I'm not the network admin). The network admin hasn't done anything to the system that he thinks would have done the changes i'm whinging about.

Any help, ANY!!!!, will be greatly appreciated.

Thanks

D
 

Answer:Excel 2007: Macro Quick Access Toolbar Not Working

WoW

I've either not explained myself properly or i've stumped even the brightest of PCReview forumers.

Guess i'll have to live with the issue

Thanks anyway
 

13 more replies
Relevance 81.18%

Hello folks. Just a general opinion required at the moment, please. I might need to create something to monitor due delivery dates against actual delivery dates. It's pretty easy to use an Excel wbook and conditional formatting to highlight late deliveries, but what I'd like is an automated email sent to a couple of relevant people as soon as an item becomes late. That also might not sound too hard, but what I think might be a problem, is this. Is there a way for this to happen even if the program is not currently open and running? And would this sort of thing be easier to achive in Access or Excel? (Assuming it is possible at all)Thanks

Answer:Excel or Access to auto send email

If the program is not running, then that's it. The only thing I can suggest is that you run the program automatically using Schduled Tasks.

5 more replies
Relevance 76.26%

Hi all,

I have a report in Microsoft Access that I have to regularly email out to people. I use a SendObject macro to create the email content and automatically attach the report as a .xls document. I recently changed the report and associated query column headers, which are working fine. When I look at the report in Access, it looks exactly howI want it to look. When I email it, however, the automatically attached Excel document has the old column headers (the content is fine). This does not happen if I hit the "Export" button and export the document straight to Excel. The same problem occurs whether I use the macro or the e-mail button on the Windows drop down menu. Can someone please explain what is happening and how I can fix it?

Many thanks,

Rebecca
 

More replies
Relevance 74.62%

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

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

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

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

ActiveDocument.AttachedTemplate.BuildingBlockEntries seems to have a problem ... to generate the macro I simply clicked INSERT then Headers and selected the "ANNUAL" header and closed headers. Here is the macro generated...

Sub Macro5()
'
' Macro5 Macro
'
'
If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow.ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
ActiveDocument.AttachedTemplate.BuildingBlockEntries("Annual").Insert
Where:=Selection.Range, RichText:=True
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
End Sub
--------

I removed the line contuation marks for clarity here, when run it generates this error 5941 the requested member of the collection does not exist and then highlightes this line

ActiveDocument.AttachedTemplate.BuildingBlockEntries("Annual").Insert _
Where:=Selection.Range, RichText:=True

---------

Help please
 

Answer:Auto Generated Word Macro doesn't run (2007)

I get exactly the same error but If you delete

ActiveDocument.AttachedTemplate.BuildingBlockEntries("Annual").Insert
Where:=Selection.Range, RichText:=True

It does go to Headers and Footers, so it looks like the Macro cannot recreate the "Annual" selection for some reason. VBA expert(s) needed please!
 

1 more replies
Relevance 72.57%

I have a access database thAT exports data to an excell file. A macro in access then opens this file and another in excell. The second excell file reads data from the first then uses a macro to format it properly and print the info. Right now I can only run this macro from the excell file. Can I run this macro (called Button2_Click in Module 3) from access? I have been using the OutputTo and RunApp action to run this macro so far.
 

Answer:Solved: How do I run an excell 2007 macro from access 2007?

11 more replies
Relevance 72.16%

Hello,

I've got this code to get rid of all leading apostrophes in the Excel (2003) Cels:

Code:
Sub Auto_Open()

Dim S As Range, temp As String
Worksheets("qryOfficeNetForeign").Activate
With Worksheets(1)
If MsgBox("Are you sure you want to remove all leading apostrophes from the entire sheet?", _
vbOKCancel + vbQuestion, "Remove Apostrophes") = vbCancel Then Exit Sub
Application.ScreenUpdating = False
For Each S In ActiveSheet.UsedRange
If S.HasFormula = False Then
'Gets text and rewrites to same cell without the apostrophe.
S.Value = S.Text
End If
Next S
Application.ScreenUpdating = True
End Sub
This code runs fine when I use the shortkey for it (CTRL + R), but when I try to autorun it I get an error:

Code:
Runtime Error 91: Object Variable Or With Block Variable Not Set
When I click the debug button, this line is selected:

Code:
For Each S In ActiveSheet.UsedRange

How can I autorun this code on opening of Excel, I would also like to run the code only if the sheetname is: "qryOfficeNetForeign"
I am no VBA expert, so am getting only errors
Thanks,
Wouter
 

Answer:auto run macro on excel start

7 more replies
Relevance 72.16%

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

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

I'm trying to write a macro that will go down a column and fill in the blank cells by adding 1 to the cell value above. The macro will need to skip cells that already have a number. Column goes down ~11,000 rows

Before:

102
blank
blank
201
blank
blank
104

After:

102
103
104
201
202
203
104

Thanks.
 

Answer:Solved: Excel Macro to Auto Number

No macro required.

Select the first blank cell, then the whole column (CTRL+SpaceBar).

Press F5 > click "Special" > select "Blanks" > click "OK".

Type "=" (the "equals" sign), press the UpArrow key (formula bar says "=A1", for example), modify to "=A1+1", press CTRL+Enter to enter the formula into all selected cells simultaneously.

To "top & tail" copy and Paste Special > Values the entire column.

Full instructions here. HTH, welcome to the board.
 

2 more replies
Relevance 71.34%

I hope you guys can help me out again with this. It seems similar to the the issue that I posted last time. But I think it is a little bit more complicated.
I receive two notepad - files (for example importfile 1 and importfile 2) via mail, I copy paste the data in an excel sheet.
Each line in the notepad - files is an order. The format of the file is :
- Character 1 to 2 (included) = Plant
- Character 3 to 5 (included) = Route
- Character 6 to 8 (included) = Suffix
- Character 9 to 18 (included) = Delivery Date
- Character 19 to 27 (included) = DunsNo
- Character 28 to 35 (included) = ContainerNo
- Character 36 to 40 (included) = Quantity
- Character 41 to 49 (included) = Total Weight
- Character 50 to 61 (included) = Volume
- Character 62 to 69 (included) = Planned Date
A csv-file ( has to be created for each line with the same route, suffix, delivery date and DunsNo.
The name of the csv-files to be created has the following format : Route_Suffix_Delivery Date
(for example DG2_3HB_22-12-2010
The output file consists of following format :
Cell A1 is always T , Cell A2 = the data in collumn D that matches the routeNo in collumn A of a excel-file (for example Trailer_Types1)
Then each row begins with P in collumn A for each line with the same route, delivery and DunsNo
Collumn B = Plant of notepad - file
Collumn C = Route of notepad - file
Collumn D = Suffix of notepad - file
Collumn E = Delivery Date of notepad -file
Collumn F = DunsNo of notepad - file
Collumn G = Container... Read more

Answer:VBA - Excel 2007 - Macro

16 more replies
Relevance 71.34%

Hi,
i'm using excel 2007 for the first time. i download a few spreadsheets every day that require a lot of reformatting and i use the "record macro" button to write the macro so that i can same about 1 hour every day (i have very limited knowledge of vba). the problem is that even if i save the worksheet the macro disappears the next day.

please walk me through on how to exactly record a macro that i can use on a daily basis. i tried putting it in the personal macro workbook but it doesn't take.

regards,
 

Answer:Excel 2007 Macro help

10 more replies
Relevance 71.34%
Question: Excel 2007 Macro

Hi everyone,

I need a few macros (I have no experience with VB or writing macros):

To search a column and add a period (.) to the end of any fields that do not have one.
To search a column for any instances of ellipses (more than one consecutive period) and reduce them to a single period.
To search a column for any instances where there is a period or comma followed by anything other than a space, and to add one.

To have one macro that could do all three would be ok, or separated into separate functions.

Thanks in advance!
Joel
 

Answer:Excel 2007 Macro

One to eliminate multiple consecutive spaces would be useful as well.

With the others, the functions can be at cross-purposes: for example, if a field ends with a period, I don't want to add a space; similarly I would want to eliminate ellipses first, because otherwise when I search for periods followed by not-spaces, ellipses will be caught in that and changed.

Forgive my lay-speak. I'm not a dev or anything, just an editor who wants to make his work faster by eliminating these common errors.
 

2 more replies
Relevance 71.34%
Question: Excel 2007 Macro

Hi everyone,

Thanks to Zack Barresse for replying to my last thread; I have a similar question this time.

I need a macro that will quickly convert all text in a given cell (or range of cells or column) into uppercase. Up to now I've been manually entering the =upper(cell) formula, copying the contents, and then Paste Special -> Values for each one I have to do; this is obviously time-consuming.

(also a link to a beginner VB or Excel macro course might be useful, heh..)

Thanks!
Joel
 

Answer:Excel 2007 Macro

11 more replies
Relevance 71.34%

Can somebody please help? I have very little VB knowledge. I am trying to create a macro that reads a column of data (column A which is labeled Territory) and creates new worksheets for each distinct Territory. Everything would start from the first sheet of data and then need to be transposed to new worksheets under the respective Territory. There are 11 total columns of data; 10 distinct Territories; and multiple rows for each Territory. Thank you in advance for your help and assistance.

Answer:Excel 2007 Macro - HELP

Hi,This macro takes each of the Territory names from column A on the source worksheet.If there is no worksheet with that Territory name, it creates it.The macro copies the data from each row to the worksheet with the same name (column A)This is a one-time macro, meaning that it creates and copies all the data. If you add data to the end of the source worksheet, all the data on the source worksheet is appended to the existing Territory worksheets data.Depending on how you use this workbook you could add code to clear each Territory worksheet before it is re-run or you could delete all the Territory worksheets before re-running it.To run the macro, I suggest you add a button to your source worksheetFrom the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)In Developer - Controls select Insert and choose the button icon.Draw the button on the worksheetIn the 'Assign Macro' dialog box select 'New'In the code window that opens enter this:Option Explicit

Private Sub Button1_Click()
Dim strSrcWSName As String
Dim rngSrcStart As Range
Dim rngSrcEnd As Range
Dim rngDestEnd As Range
Dim rngSrcCell As Range
Dim objWS As Object
Dim blnWSPresent As Boolean

On Error GoTo ErrHnd

'turn off screen updating to increase speed
'and remove screen flicker
Application.ScreenUpdating = False

'get name of this worksheet
strSrcWSName = ActiveSheet.Name

'set sta... Read more

3 more replies
Relevance 71.34%

View attachment 212815
Hi,

Please find the attached word document for my query on excel macro. The current data and expected results are mentioned in the attached document. Please help me in creating a macro for my expected results.

Regrads,
Akki
 

Answer:Macro help for excel 2007

6 more replies
Relevance 71.34%

I have been trying to figure out if it was possible to do a fairly complicated replacement.

Just a description of the file:
- There are columns a-q in the file, with rows going up to about 9000.

Each day I have to upload several hundred files onto an outside website which I then have to copy down by hand the numbers of the files. Those files are organized similar to "5 bay - 99,84,77,33,45 STR 555 - AA.2012.file type". The only unique piece of each file is the number following STR, which is the part that i write down. This number is in column E.

My job after writing down the numbers of each file that fits into a corresponding row in column E, is to go into columns O and P and put my initials in column O and the current date in column P.

I was wondering if it was possible to make this quicker through the use of some macro. I can always type the numbers into a separate excel sheet and feed the macro that list somehow. The columns are not always O and P either. It can also be J and K, or Q and R as well depending on what I had done.

So all in all, I'm asking if it is possible to create a macro that I can feed the number that corresponds in column E and have it add my initials and the current date to the two columns of my choosing. Thanks in advance, even if it isn't possible!

PS: A blanked version of the worksheet we use is included
 

Answer:Excel 2007 Macro Help

Hey there, just registered to help you out! Check this and tell me if it works, just make sure to backup your files beforehand *wink*

Sub Book1()

Dim Initials As String
Dim StoreColumn As Integer
Dim InitialsColumn As Integer
Dim DateColumn As Integer
Initials = "KM"

For i = 1 To 30

If ActiveSheet.Cells(1, i).Value = "Store No." Then
StoreColumn = i
Else
If ActiveSheet.Cells(1, i).Value = "Assignment Completed by:" Then
InitialsColumn = i
End If
If ActiveSheet.Cells(1, i).Value = "Date Assigned" Then
DateColumn = i
End If
End If

Next i

For j = 1 To 10000

If ActiveSheet.Cells(j, StoreColumn).Value = "5526" Then
ActiveSheet.Cells(j, InitialsColumn).Value = Initials
ActiveSheet.Cells(j, DateColumn).Value = Now()
End If

End Sub

Im not sure of your experience level with excel but paste it in VBA in excel and run it. The code searches through columns to find the ones with stores, initials and dates and remembers where they are. Then it searches through stores to find the 5526 value and puts the current date and initials that you can set at beginning of the code. Let me know what you think.

PS. Before you run it make sure to check the names of the columns. I wrote what I saw but it isn't necessarily what there is. Just copy the names from columns and paste them to the code
 

2 more replies
Relevance 71.34%

I am pretty much a beginner with using Excel for anything other than simple sums, but I now have a budget spreadsheet to work on. I need a macro that will update a simple sum formula eg =SUM(B2:O2) to include the next adjacent column, eg =SUM(B22). I have recorded a macro that does that, and extends the formula change to the rest of the column, but I need to do this every month, to include the next column.
The spreadsheet is roughly like this:

Jan Feb March April May June Total To Date Projected Total
Salary1
Salary2
Salary3

Every month I have to update the Total to Date column to include the past month. At the moment all my macro does is extend the formula to a specific column (March) and that's it, rendering it useless for next month. Is my only option to record a new macro every month, or is there VB language to indicate the next adjacent column, rather than a specific column?
Apologies if this is a very simplistic question - I just feel sure there's a way of doing this that I just don't know about yet!
Thanks for any advice.
 

More replies
Relevance 71.34%
Question: Macro Excel 2007

I wish to make a macro that will paste All from the clipboard Excel 2007 ( do the same as the Paste All Clipboard button )
The cell is A1 sheet "H"
after it has pasted there another macro to clear All (do the same as the clear all Clipboard button)
any ideas on the macro code??
Many thanks
Steve
 

More replies
Relevance 71.34%

I am trying to create a Macro in which I have data in sheet 2 that I copy and paste into Excel 2007 from a website that changes on a monthly basis that I need to place into sheet 1 at the bottom of the page.

Currently, sheet 1 column A is the date. Currently the 9/30/12 date is on row 44 and row 45 would be 10/31/12, etc...each row is a new end of month. I do this on a quarterly basis and for multiple tabs so the macro would be for all the sheets, but I figure if someone could tell me the macro for sheet 1, I can change the sheet names in the macro for the other sheets.

I need to create three rows at a time starting on row 45 for the next three months, then row 48 for the three months after, etc... Cell g45 will come from sheet 2 cell F3; g46 will come from sheet 2 cell F4 and g47 will come from sheet 2 cell F5. I need these cells to be copied and pasted with values only because I will change Sheet 2 on a quarterly basis and I need the "old" numbers to stay and be fixed. This means I can not use the equal sign to setup my formula. Cell H45 will use the formula from cell h44 (I normally just drag down) and this will continue until cell h47. Cells M45, N45, & S45 use the same drag down feature for the formula that the "H" cells do. There are no other columns with active data in it.

Does anyone know how to create a Macro for this? I tried to do the Macro Record feature, but it does not move down, it only create three new blank rows and does not give me... Read more

Answer:Excel 2007 Macro Help

Post a sample workbook and I'll be happy to take a look and offer my assistance.

Rollin
 

2 more replies
Relevance 71.34%

How do I edit the macros of a document if the macro that closes the program is executing right when the document opens? It doesn't allow me enough time to go to View --> View Macros to delete or edit the auto-exec macro.
 

Answer:Solved: Word 2007 auto-exec macro closing document

If you open a brand new document without any macros attached you should be able to change the application security level to high so that no macros will run which will then allow you to open the document with the code attached to edit it.

Rollin
 

2 more replies
Relevance 70.93%

Have a spreadsheet, with a macro that runs on start up.

It uploads data from the web and places it in a worksheet named data, and from that worksheet certain info is used in another worksheet all of which are in the same workbook.
Macro is such that when a new month commences a new worksheet with month/year is opened

All has been working until yesterday 1/6/12 (Aust) .

The data uploaded is always 1 day behind actual date so on 1/6/12 the latest data would have been for 31/5/12 and would have gone into the May 2012 worksheet.

The problem I have encountered is that the file becomes blurry and I am unable to close it down .
Have had to manually shut down and reboot to use computer.

Ran a quick scan with Malwarebytes and it cam up with one infection "Pup.Bundleinstaller.Bi " which I had removed.
Have since run full scan with no infections

Details from Hijack this log show
Logfile of Trend Micro HijackThis v2.0.4
Scan saved at 10:22:20 AM, on 02-Jun-12
Platform: Windows 7 SP1 (WinNT 6.00.3505)
MSIE: Internet Explorer v9.00 (9.00.8112.16421)
Boot mode: Normal

Running processes:
C:\Program Files (x86)\Common Files\Acronis\Schedule2\schedhlp.exe
C:\Windows\vVX1000.exe
C:\Program Files (x86)\Google\GoogleToolbarNotifier\GoogleToolbarNotifier.exe
C:\Program Files (x86)\Skype\Phone\Skype.exe
C:\Program Files (x86)\Google\Google Desktop Search\GoogleDesktop.exe
C:\Program Files (x86)\Google\Google Desktop Search\GoogleDesktop.exe
C:\Program Files (x86)\TaskPlus\taskplus0... Read more

Answer:Problem opening Excel fiel with auto run macro

Problem solved it was not a virus .

Thanks to all who looked at asisting

Pedro
 

1 more replies
Relevance 70.93%

Hi,

I have a worksheet containing various data including an expiry date, this is created by adding 364 to the start date and then then using an auto fill to fill in all other rows (over 50,000).

I would like to make a macro to do this alongside a number of actions but when making the macro I am specifying only a certain range to fill (A1:A50000). The problem I have is this list is consistently growing and so this range would then not include any new records added. Is there a way that I can make it so it auto fills from A1 to the last present row?

Thanks for your help

Dan
 

Answer:Solved: Auto Fill Data in Excel Macro

8 more replies
Relevance 70.52%

I am having a problem I cannot resolve when recording macro in Excel 2007. I am trying to record macro in a file which has the following:1. Sheet one has several columns headed as suppliers code, nominal a/c code date, gross amount, tax and net amount, etc. In each row below there are the actual codes, dates values etc for each transaction. 2. Sheet 2 has several columns but the main ones for my purposes are headed "supplier code" and "supplier name". Below in each row is the supplier code no. and the respective supplier name in the adjacent cell3. Sheet 3 is similar to Sheet 2 but it has details of all the nominal accounts instead of supplier accounts.The data in Sheets 1, 2 and 3 have been copied from 3 separate files and included in 1 file mentioned above with 3 worksheets. I now start to record macro and I tidy up sheet 1 and insert new columns where required and unmerge cells and do other stuff and by using the VLOOKUP Function I am able to state the respective Supplier name and Nominal account name in each row which is what I want to achieve. I save this file as a macro enabled Excel file. My problem is when I clear the contents in Sheet 1 and run the Macro again I can see the cursor moving everywhere but most of the original data that was there gets lost somehow.Also I want to run this routine regularly every month by updating the data in sheet 1. How can I get the macros to give me the correct layout that I have achieved previously. The number of rows in subsequent... Read more

Answer:Recording macro in excel 2007

The bottom line is that the recorder is simply going to record the exact steps that you perform while the recorder is running. If you start the recorder before you copy the data from another file, it may help, it may not.If the data is not in the exact same location, with the exact same format each time you run the macro, you may not get the same layout when it is pasted into your new sheet. Then, when the macro starts Cutting/Copying/Pasting, it simply going to perform those operations on the exact ranges it saw when you recorded it. If there is different data or a different layout in those ranges, you will not get the results you expect. There are ways to have the macro find the exact data you want to Cut, etc. but that takes manual code writing. You wouldn't be able to record that.One way to understand what the recorded code is doing is to single-step through it, watching your sheet as each instruction is executed. I suggest that you read the Single Stepping portion of this How-To. I think it will help you understand what I'm talking about.http://www.computing.net/howtos/sho...Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

7 more replies
Relevance 70.52%

I keep getting a runtime error with the following macro, which is part of a list of routines when I convert an excel spreasheet to a csv file for uploading data into another program. The line highlighted below is the one shown when I click on the debug button. Range a4 is the last row of my data.

'inserts the word end below the last data

Range("a4").Select
Selection.End(x2Down).Select
ActiveCell.Offset(1, 20).Select
ActiveCell.FormulaR1C1 = "end"
Selection.Copy
Range(ActiveCell, ActiveCell.Offset(0, 50)).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
 

Answer:Excel 2007 macro problem

Morning and Welcome to the PCReview Forum!

Please could you provide the full details of the Runtime error.
 

7 more replies
Relevance 70.52%

Hello,
Macro's for excel is new for me so I hope somebody can help me.
I receive a notepad file via mail. The content of this mail I copy/paste in an excel sheet.
This Notepad file consist of H-lines (=header lines - begins with H) and B - lines (Goods lines - begins with B).
For each H-line I have to create a new file in Excel. In this file, for each B-line that comes after the H-line,I have to get data in different cells:
In Cell A, I need characher 420 to 422 (included) of the H-line
In Cell B, I need character 2 to 10 (included) of the H-line
In Cell C, I need character 23 to 31 (included) of the H-line
In Cell D, I need character 27 to 35 (included) of the B-line
In Cell E, I need character 2 to 9 (included) of the B-line
In Cell H, I need character 348 to 351 (included) of the H-line
In Cell I, I need character 61 to 69 (included) of the B-line
After all the B -lines of the H-lines are processed the file may be saved as CSV-file (comma delimited)
Then the next H-line can be processed and a new file may be created.
Then the process is the same as above. This continue until no H- or B-lines are found in the file.

Can anybody help me?
 

Answer:Solved: VBA Macro - Excel 2007

16 more replies
Relevance 70.52%

I have a spreadsheet of a lot of Data and I am having troubling finding a guide to take any cell and take 10% of the value away from it. I'd like to just select a cell or row and then simply click the button (or others who use the spreadsheet) and it reduce the current value by 10%.In Addition, I'd like to somehow create a similar button that also adds 10 or 2 to the current selected cell or row.Please let me know if this is possible.Thank you in advance.

Answer:Button and Macro Help in Excel 2007

Google something like Excel Command Button for lots of hits on how to create a Command Button and assign a macro to it.This is just one of many sites: http://www.mrexcel.com/tip068.shtmlAs far as the code required, your question isn't clear.I understand the part about reducing the values in the selected cells by 10% and I'll provide code for that below.However, this part is not clear:a similar button that also adds 10 or 2Do you mean 10 or 2 percent or the actual value 10 or 2?Do you want the code to randomly decide whether to add 10 or 2?Do you want to be able to choose the value that is added after you click the button?Do you a button specifically assigned to adding 10 and another button specifically assigned to adding 2?Please clarify.Note: The following code checks to see that the value in each selected cell is a number. If it is, it reduces it by 10%, if not, it skips it. However, if the value is the result of a formula, it will replace the formula with the value minus 10% and the formula will be lost. You can't subtract 10% from a formula generated value and retain the formula since the formula would no longer be accurate.If you study this code and understand how it works, you should be able to modify it to add your 10 or 2.Sub Subtract10Percent()
'Loop through selected cells
For Each myCell In Selection
'Check to make sure cell contains a number
If IsNumeric(myCell) Then
'Add 10%
myCell.Value = myCell - myCell * 0.01
End If
Next
End SubClick Here Befo... Read more

4 more replies
Relevance 70.52%

Hi, I'm trying to make a macro that will go to a comma in a cell and then delete everything from the comma to the end of the line and then do this for the whole worksheet. The comma must also be deleted. Is it possible to do something like this or must it be done manually? Thanks for your help.

Example:
Red, Gold and yellow with Green Stripes

The final result:
Red
 

Answer:Solved: Excel 2007 Macro Help

13 more replies
Relevance 70.52%

hi hi,
could you help me to solve my macro problems with excel 2007.
i had the following macro in 2003 which is not working now:

Sub Journal()
'
' Journal Macro
' Macro recorded 28/01/2008 by od28689
'
' Keyboard Shortcut: Ctrl+j
'
Sheets("Upload").Select
Sheets("Upload").Copy
Dim div As String
div = Range("A1")
ChDir "Z:\UPLOAD\US1"
ActiveWorkbook.SaveAs Filename:="Z:\UPLOAD\" & div & "\JOUR25.prn", FileFormat:= _
xlTextPrinter, CreateBackup:=False
ActiveWorkbook.Save
ActiveWindow.Close
Sheets("Journal").Select
Range("D8:E8").Select
End Sub

Many thanks in advance!
cheers,
tekike
 

More replies
Relevance 70.52%

I have created a roster that I want to barcode scan into. I want to run "text to columns" automatically when data is scanned into a1, then tab to a2, scan data, have "text to columns" auto run again. I know I can use the shortcut key but I would rather not. I want the user to simply scan into a1, a2, a3 and so on.
 

Answer:Excel 2007 How to automatically run a macro

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If Target.Count > 1 Then Exit Sub

'Your code goes here.

End Sub
 

3 more replies
Relevance 70.52%

I have researched my issue but other threads posted here did not solve my problem. I have an Excel 2007 spreadsheet that was first designed in Excel 2003, and it has a macro that generates 93 spreadsheets with prepopulated data. The macro works, it generates the files but then I cannot open them due to the following error:
'Excel cannot open the file 'xxx.xlsx' because the file format or file extension is not valid.'
I have tried changing the macro to save as .xlsm, but get the same result. I have conditional formatting that will not work in Excel 2003, plus most of the people who will be opening the file are on Excel 2007, so I don't want to save in xls format; however, I did change the macro to see if it worked for Excel 2003 and it does.

Sure hope someone can help.

Here is a sample from the macro:

Range("B1:I1").Select
ActiveCell.FormulaR1C1 = "HP Argentina"
Range("C2").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Data\P2012 Templates\P2012 Country Summary and Cost Forecast - HP Argentina.xlsx" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
 

Answer:Excel 2007 Macro Issue

If the macro is being run in 2007 you should first try changing the FileFormat value that you are passing. Change the portion below.

FileFormat:=xlNormalClick to expand...

So that it reads

FileFormat:= xlOpenXMLWorkbookClick to expand...

If the workbook is a macro enabled workbook use the line below instead.

FileFormat:= xlOpenXMLWorkbookMacroEnabledClick to expand...

Rollin
 

2 more replies
Relevance 70.52%

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

I am trying to create a macro in Excel 2007 that will sum two different columns of numbers. The columns will always be the same length. I am new at "trying" to create macros and I can't figure out how to keep the same range to sum just move it 3 columns to the right. A sample of the table data is below. Thank You!!

Sub Test()
On Error GoTo CancelExit:
With Selection
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 0).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
.Offset(.Rows.Count + 1, .Columns.Count).Cells(0, 3).FormulaR1C1 = "=SUM(" & .Address(, , xlR1C1) & ")"
End With
CancelExit:
On Error GoTo 0
End Sub

Sale Date Sale Price Adj Sale Price 2010 Land 2010 Imp 2010 Total
7/7/2009 10,000 10,000 13,300 0 13,300
7/7... Read more

Answer:Excel 2007 Macro Sum 2 Columns

13 more replies
Relevance 70.52%

Hi. My name is Chuck and I am new to the forum. I am working on an Excel 2007 Workbook in which I am saving certain activities to macros. When creating these macros, I am storing the macros in "This Workbook". My ultimate goal is to be able to save this workbook with the macros I have recorded and assigned to buttons within the workbook to a thumb drive and give it to a friend who can then open the file on any machine that has Microsoft Excel 2007 running on it, and use the workbook and have the macros work. However, it appears that when I close the file and reopen it later to continue working on it (on the machine I am creating the file on), it is as if I never even recorded the macros... they are gone. Please help.
 

Answer:Excel 2007 Macro Question

9 more replies
Relevance 70.52%

I am using Excel 2007. I want to hyperlink several stock symbols to open stockcharts.com. I know how to hyperlink, but think it would be quicker since I have over 50 to create a macro. The problem is the display text stays the same each time I run the macro - so basically each cell reads AAPL instead of continuing. What should I do?

I have Windows Vista installed.

Thanks in advance!
 

More replies
Relevance 70.52%

Hi, I have got an Excel spreadsheet which has incident numbers in column A. For each of these incidents I need to put in hyperlinks which are very very similar. The first part of the hyperlink is the address to the database and the second part is the Incident number itself. I'm trying to create a macro which automates the process of putting in the hyperlink plus the incident number in. Can this be done?
 

Answer:Excel 2007 Hyperlink Macro

7 more replies
Relevance 70.52%

Hello,
I am an analytical chemist and I am dealing with very large data matrix (11,000 x 3).

I am looking for a macro that can reduce the data in following way

Column 1 Column 2 Value
A, B 1
B, A 1
A, C 1
C, A 1

With a Result output

Column 1 Column 2 Value
A, B 1
A, C 1

In other words in this data set A to B is the same as B to A and would like to delete the duplicate row.

Any help doing this with Macros would be appreciated. You can imagine going through 11,000 line looking for these types of entries
 

Answer:Solved: Excel 2007 Macro help

8 more replies
Relevance 70.52%

Thanks to Derby and Humar for their help on the macro yesterday. Today, I've come across another problem. Based on the information that is separated into different worksheets based on years, I need to show that the money collected in a graph format. The problem that I need help with is, I need a macro that would graph column E (money collected) of each spreadsheet on the y-axis and then graph the months based on column B (date) of each spreadsheet. It also needs to be able to update itself from information in sheet 1. I know it sounds complicated, but i just can't figure it out. Any help would be appreciated.The macro that is already being used for this workbook to separate information from sheet 1 to other worksheets based on year is pasted below:Option ExplicitPrivate Sub Button1_Click()Dim rngStart As RangeDim rngEnd As RangeDim rngCell As RangeDim strYear As StringDim rngSearch As RangeDim rngFind As RangeOn Error GoTo ErrHnd'stop screen updating to increase speedApplication.ScreenUpdating = FalseWith Worksheets("Sheet1")'set start as row after cell with 'Copied' in it'if 'Copied' not found use B2 i.e., after heading row in column BSet rngSearch = Range("B2:B" & CStr(Application.Rows.Count))Set rngFind = rngSearch.Find("Copied", LookIn:=xlValues)If rngFind Is Nothing Then'Copied not found - so start at B2Set rngStart = .Range("B2")Else'Copied found'set start to row after 'Copied'Set rngStart = rngFind.Offset(1, 0)'delete the row containing 'Copied'rngFind.EntireRow.Delete... Read more

Answer:Graph Macro in Excel 2007

Hi,I am not sure why you are looking for a macro to create a chart from existing data.Assuming that you have dates in column B (say B2:B100) and amounts in E2:E100),then create a table of months.In cells G1 to R1 enter dates for the first of each month for the year in question.Format G1 to R1 as months "mmmm"In cell G2 enter this formula:=SUMPRODUCT((MONTH($B$3:$B$101)=MONTH(G1))*($E$3:$E$101))Note the $ signs.Now drag to extend the formula to cell R2R2 will contain:=SUMPRODUCT((MONTH($B$3:$B$101)=MONTH(R1))*($E$3:$E$101))Cells G2 to R2 will contain the monthly amounts.Select cells G1 to R2 and from the menu bar select 'Insert' - 'Chart'and create a chart of amounts by month.When completed, you can select the category axis, right-click, select 'Format Axis...' and select the number tab, and change the date display to just months if required, e.g., mmmmRegards

4 more replies
Relevance 70.52%

How can i make macro to print the worksheets of a workbook.?

Answer:print macro excel 2007

Please provide some more details.You can print multiple sheets of a workbook just by selecting multiple tabs via Shift-Click for contiguous tabs or Ctrl-Click for non-contiguous tabs, and then choosing PrintWhat exactly do you want the macro to do?P.S. Don't forget to included a Please and Thank You with your requests. We're all just volunteers here trying to help other members of this site.

2 more replies
Relevance 70.52%

I have a spreadsheet with macros that I use for work which works flawlessly on Win XP, but when you try to run the macros on a Vista/7 machine it gives this error:

"Compile error: Can't find project or library"

It seems to be hanging up at "Trim" on this line:

NewSheet = Trim(InputBox(Mesg, Title))

Our tech support seems lost, any help?

Thanks!

More replies
Relevance 70.11%

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

Hi, I was coding in VBA and it runs fine there but when I run the same function using a macro (which was working before), it will not do anything. I check that macros are enabled. Does anyone have any ideas as to why the macro button would not work?Hi, I was coding in VBA and it runs fine from there but when I do the same by clicking the macro button (which was working before), it will not do anything. I checked that macros are enabled. Does anyone have any ideas as to why the macro button would not work? Thank you.
- Jeff
 

More replies
Relevance 69.7%

Hello. I am attempting to develop a macro that will allow me to find a number in column D and replace that value with a text name. I have 31 - 5 digit codes with 31 corresponding text names. The spread sheet is 10,000+ entries and growing every day. For example, I would like to find all instances of "21021" (in column D ONLY) and replace them with the word "Boyle." And so on, for all 31 codes. I'm new to Macros and could really use a little help to get me started!

Thanks!
 

Answer:Excel 2007 Macro for Find and Replace

7 more replies
Relevance 69.7%

I have about 20 macros on an Excel 2007 spreadsheet. I have added buttons into the QAT. Unlike Office 97 - the buttons aren't labelled and the design of each button is limited to the choice of 180 available. You would think this was enough, but it isn't. What happened to the ability to design one's own buttons? At the very least I would expect to be able to use letters of the alphabet, but unless you need an "A" the choice does not exist. Is there a way round this problem ?
 

More replies
Relevance 69.7%

Hi,

Every macro enabled file that I try to use on my computer gives me the same error: Runtime error 9 subscript out of range.

I get this error on files that work on other computers, including files I wrote on my computer, they don't work anymore and the all give me the same error.

Even writing a very simple macro I get this error, for example:

Sub MyInfo()
Workbooks("CropTool").Worksheets("RawData").Range("A1").Cells(2,4) = "hi"
End Sub

I have checked about 10 times to make sure that the workbook is indeed called CropTool, and the worksheet is called RawData.

This error has nothing to do with what I write in the module....

Please help.
 

Answer:Solved: excel 2007 macro error

7 more replies
Relevance 69.7%

I have a workbook that has no macros. Every time I open the file I get this message: "Security Warning Macros have been disabled" Is there a way I can get excel to show me what it is disabling?
 

More replies
Relevance 69.7%

I had been using a spreadsheet in Excel '97 which has a toolbar containing about 20 macro command buttons. This toolbar resides down one side of the spreadsheet - not across the top like normal toolbars.

I have now started using Excel 2007 which is quite different. The spreadsheet works and the macros have been copied across. However, the toolbar appears as on "Add-In" tab as a "Customized Toolbar". This only contains 12 command buttons - the other 8 or so are missing. The missing 8 can only be run by displaying the macros and then hitting the run button, or assigning a shortcut key. Of the 12 remaining buttons I cannot modify them or delete them, and I certainly can't add new ones. One of them doesn't run because it claims to unavailable '..in This Workbook', yet it will run by hitting the Run button.

I really don't know which way to turn for advice - except perhaps to revert to Office '97. Is there anyone there who can point me in the right direction?
 

Answer:Excel 2007:Toolbars and Macro Problem

This site has a download that adds a button to the QAT which allows a list of your macros to be held. This will appear as the old menus used to in 97. It is very easy to use and self explanatory.
http://www.rondebruin.nl/qat.htm
I certainly found this to be very useful as I regularly need access to about 10 macros.

Hope this helps
 

6 more replies
Relevance 69.7%

Hello, I am very new to Excel 2007 and even newer to Macros. I have a spreadsheet that is created automatically that can contain many rows of data. The First column always holds certain words, such as "pending" or "Resolved". I would like to create a Macro to sort all of the rows with the same word in the first column into new sheets. For example, let's say their are 20 total rows in this sample, 8 rows have Pending in the first column and 12 have Resolved in the first column. I would like to search for all the rows that have pending in the first column and cut and paste the entire row to a new sheet. Then do the same for Resolved. I do not have much coding experience, what would be a good example?
 

Answer:Solved: Excel 2007 Sort Help - Macro

Welcome to the board.

Here's some code, whether it's a good example would be debatable. Post back if you need help with installing/running.

Sub pending_resolved()
'add new sheet, 'Pending'
x = ActiveSheet.Name
Sheets.Add
ActiveSheet.Name = "Pending"
Range("A1") = "Status"
Sheets(x).Select
'get last row on main sheet, for range to loop
LastRow = Range("A" & Rows.Count).End(xlUp).Row
'loop range; if cell = 'Pending', copy row then blank cell
For Each Cell In Range("A2:A" & LastRow)
If Cell = "Pending" Then
Cell.EntireRow.Copy Sheets("Pending").Range("A" & Rows.Count).End(xlUp).Offset(1)
Cell.Clear
End If
Next Cell
'delete all rows = blank in column A
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'rename main sheet 'Resolved' (only 'Resolved's remain)
ActiveSheet.Name = "Resolved"
End Sub
 

2 more replies
Relevance 69.7%

Greetings!

I have a macro in Excel to filter out rows with zeros. That works good. I want to change the "0" to a text string "(A1)". How do you do that? I tried putting both double and single quotes and it wouldn't work.

TIA for your ideas!

---------------------------------------------------
Option Explicit

Sub HideZeros()
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd End Sub

Sub ShowZeros()
Selection.AutoFilter Field:=1
End Sub
 

Answer:Office 2007 Excel Macro has me stumped...

Try something like this....
Code:


Selection.AutoFilter Field:=1, Criteria1:="=[I]TextGoesHere[/I]", Operator:=xlAnd

 

2 more replies
Relevance 69.7%

I am looking for a macro that willl do the following:
I have a date in cell A12
I want to put that date in each cell of column b as long as the corresponding cell in column a has a number in it.
After all dates are entered, I want the macro to skip two lines and start again, but this time add one day to the previous date.
Can someone help me on this?

Here is a screenshot which will hopefully clear up what Im wanting to do.


 

Answer:Solved: need help writing macro in excel 2007

16 more replies
Relevance 69.7%

Hi All,

I'm trying to create a macro that will fill cells within a column with a zero when the cell is blank and leave the cell as-is when it is populated with a value. I figured out how to fill a column with zero down to the last active row in the sheet but I need to revise it to skip the cells with values.

Please help, I'm stuck at this point and it is the last part of the macro that I cannot figure out.

Thank you!

Jen
 

Answer:Solved: Excel 2007 Macro Question

9 more replies
Relevance 69.7%

Hi All.

I have an attendance list for a course I'm running. It is essentially an array with the names of the attendees down the first column and a series of columns to the right (so that there is a block for the "X" i use to mark that they attended)

The problem is that if a person leaves early or starts the course late, I need to add their name and so I need the whole array to auto sort in rows by the first column. Obviously I can do this manually but I would prefer that it happen automatically if the first column list is changed. I have tried using a macro but the calculation takes ages. VB code is the same...

Can someone help me with a code which will do this for me?!?

I have attached the worksheet in question

TIA

drj
 

Answer:Excel 2007 Sort Macro Problem HELP!

7 more replies
Relevance 69.7%

I need some help with a macro. I need to save data from certain cells in 1 worksheet and to another worksheet. Details belowI want to grab the data in worksheet "survey" from cells A3, A4 and A6 and save them to worksheet "data" in cells A2, B2 and C2. After the save is done I then want to clear the data from cells A3, A6. I created an object button in my workbook that is clickable. I just need to assign a macro to it. I have been working on this for over a week and I am new to macros so it is a struggle. I have even went as far as purchasing a program call teachmemirosoftoffice and still can't figure out the whole thing. I get portions of it to work but not all of it. I would greatly appreciate anyone who can help me write this macro. I would forever be indebt to you my friend. Thanks.

Answer:Need a macro for excel 2007 to save data

Since you do not appear to have a contiguous range of cells in your source document, you have to do each cell individually. A contiguous range could have been copied and pasted Transposed into your destination, but since you skipped A5, you can't use that method.Try this:Sub CopyThenClear() Sheets("data").Range("A2") = Sheets("survey").Range("A3") Sheets("data").Range("B2") = Sheets("survey").Range("A4") Sheets("data").Range("C2") = Sheets("survey").Range("A6") Sheets("survey").Range("A3, A6").ClearContentsEnd Subre: "I would forever be in debt to you "Forever is a long time to be indebted for a few lines of VBA code. A simple thanks and a promise to pass it forward will suffice.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

6 more replies
Relevance 69.7%

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

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

Answer:Excel 2007 Macro/Folders & Files

Hi

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


Sub Get_File_Names()

[B]'1st directory[/B]

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

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


[B]'2nd directory[/B]

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

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

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

2 more replies
Relevance 69.7%

I'm working on a macro that does several things with budget reports. I have zero knowledge of Visual Basic, just recorded my macro and played with the code.
It creates a line called "total expenses" then, six cells to the right, I need it to add the value in a row titled "total coaches" and another value in a row called "total directors". these values are 6 cells to the right of the titles. so what I had tried to do was search for the "total coaches", move over six cells, take that cell's location and add it to the "total directors" value with the same process. however, the macro just used RC to record the cell's position. the position of the value needs to be dynamic as the reports will change.

I can get it to create the "total expenses" line and make the cell six to the right of it active. I just can't get the formula to be dynamic based on a text search.

here's the relevant code:
ActiveCell.FormulaR1C1 = "Total Expenses"
Range("A247").Select
Cells.Find(What:="Total Expenses", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(6, 0).FormulaR1C1 = "=(R[-147]C+R[-1]C)"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFo... Read more

Answer:Solved: Dynamic Excel 2007 Macro

This should do what you want, but I don't have an example to look at so I just made up my own example

You can adjust the column numbers to match yours. Note that I made some additions. I think in your recorded macro you went to Row 247 at the start, which I am assuming is the last row...I added code to find it for you.


Code:
Public Sub test()


Dim Coach As Integer
Dim Direct As Integer
Dim totalExp As Integer

Dim ws As Worksheet

Set ws = ActiveSheet

On Error GoTo err_Hand


' finds the last row in column 1
lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'fills in cell 2 rows below last empty
Cells(lrow + 2, 1) = "Total Expenses"

totalExp = lrow + 2


Cells.Find(What:="total coaches", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Coach = ActiveCell.Row


Cells.Find(What:="total directors", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Direct = ActiveCell.Row


Cells(totalExp, 2) = Cells(Coach, 6) + Cells(Direct, 6)


err_Hand:
' 91 is the error number when the search value is not found
If Err.Number = 91 Then
MsgBox "Search value not found"
Exit Sub

End If

End Sub

 

3 more replies
Relevance 69.29%

I'm sorry if this has been discussed elsewhere but I have been looking all over for this answer. I feel like this is my last resort; I hope you can help.

I am in charge of developing forms on Excel. With ONE worksheet containing only ONE form, each workbook will have any number of worksheets (from 1 to over 200).
I have created a macro designed to insert the required header and footer information, for each form (AKA each worksheet), simultaneously for the entire workbook.
Now here is my challenge. Within the required footer information, each worksheet (form) must have a unique number (e.g., 7510001); the department is 751 and the first form number will be 0001.
The next form (worksheet) must have the number 7510002 in its footer information, and so on.
What I would prefer is the ability to insert the first number (possibly in a pop-up screen) and then have the macro populate the increment for the rest of the worksheets within the workbook.

Thank you in advance.

Bob
 

Answer:Solved: Macro for Auto Inserting a Number Incrementally (+1) in the footer of Excel 2

6 more replies
Relevance 69.29%

Hi Guys,

I'm using with much profit a code that I have found in this forum:

Sub ReplaceString()

StartRow = 1
EndRow = 10
For i = EndRow To StartRow Step -1
Columns("C:C").Cells.Replace What:=Range("A" & i).Value, Replacement:=Range("A" & i).Offset(0, 1).Value, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

Acutally this search coloumn "C" for values in Coloumn "A" and if found replace those values in "C" with what you have in "B:".
Example:

Before MACROAA1ABB1TCC1NB1B2B1After MACROAA1A1BB1TCC1NB1B2B2

what I need is the replaced value in "C" be highlighted as already in the example (iether by red fonts or green interior is ok).
The code need an addition to do that.

Could you help me please?

thanks!


 

Answer:Macro Multiple Find and Replace in Excel 2007 (2nd leg)

Hi, Why not post a sample file with the code you're using and also show what you need.
It's easier to work with a sample file.
Make sure the data is dummy data
 

1 more replies
Relevance 69.29%

Hi There I Have conditionally formatted column A with 2 conditions I want to be able to switch this formatting on or off as required with a macro please

Answer:excel 2007 macro to switch conditional formatting on and off

1 - Record a macro as you set the Conditional Formatting.2 - Record another macro as you delete it.3 - Assign each macro to a button to toggle between them.The recorded code will be be bloated, meaning that it will have a bunch of "default" settings that aren't really needed. Cleaning up recorded code is a great way to learn VBA.Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

2 more replies
Relevance 69.29%

I just bought a new laptop running windows 8, downloaded and installed Excel 2007. I have the Developer tab active, did the Macro Security changes to allow activex, enable all macros etc., but the Record Macro, Visual Basic and Macros buttons are inactive. That is, they are not clickable. I updated to windows 8.1 - no joy! Otherwise the install of Excel 2007 seems okay. Spreadsheets from my old computer & a much older version of Excel open okay, formulas work, etc. The Macro issue is the only problem I am aware of at this point. The symptoms are the same on a new (blank) workbook or on an existing spreadsheet. Any suggestions? - I guess a re-install is my next option. Thanks in advance. Bob
 

Answer:Solved: Excel 2007 Macro & Visual Basic Not Available

A re-install of Office 2007 did the trick. I enabled functions this time that I had not enabled the first time. Don't know which one solved my problem but the macro buttons are now active & I can get on with it....
 

2 more replies
Relevance 69.29%

Hi, I am trying to run a macro from 2003 in Excel 2007 and it gives the following error:Run Time Error 1004Method 'AcceptLabelsInFormulas' '_Workbook' FailedThe line in the macro that it is referring to reads:.AcceptLabelsInFormulas = TrueDoes anyone know what the line should be in Excel 2007?I don't know if this is the only problem as it stops at this point. But it appears everything prior is ok and runs to this point.

Answer:2003 Excel Macro in 2007 Excell Problem

A quick Google search reveals that the option to set.AcceptLabelsInFormulas = True has been removed from 2007.2 reasons were given:1 - seldom used2 - prone to errors when column labels are moved, etc.Apparently the "standard" workaround is to use Named Ranges instead.

4 more replies
Relevance 69.29%

Hi all,
This is just a preliminary request for advice before I undertake writing a fairly substantial VBA macro.

My plan, in short, is as follows:

- User receives a daily Invoice file. The data from this is to be dropped onto an Excel Table I've prepared (let's call this the Invoiced Table) in the Master File, which will allow some formulae, such as IFs and Vlookups to static tables, to auto-populate. Each day's data is to be appended to the bottom of the table.

- User receives a weekly Paid file. The data from this will be dropped into a second Excel Table (let's call this the Paid Table) on another worksheet in the Master File.

- Once the weekly file data has been dropped in, the user will run the macro. This will compare records from the Invoiced Table against the Paid Table using at least 4 (probably 8) different sets of lookups. In a simplified example, these might be 4 different customers.

- Each set of lookups will result in some matches. For each matching record for a specific customer, fields from the Invoiced and Paid tables need to be combined into a new record on a seperate worksheet.
So in a simplified example, Record 1 might have an Invoice Number in the Invoice Table that matches the Invoice Number in the Paid Table. I'll need to copy the Invoice Number, Customer Number and, say, the Invoice Date field from the Invoiced Table, plus the Paid Date from the Paid Table.

- I'll then need the macro to delete those lines from both t... Read more

Answer:MS Excel 2007 - VBA Macro to split large file

Is excel the only option? Access would do a lot of this without the having to write a macro every step.
 

2 more replies
Relevance 69.29%

Hi
I need a macro for sorting name and adresses;
Orginal format in cells like this
A1 Name
A2 Last Name
A3 Adress 1
A4 Adress 2
A5 Po number
A6 City
A7 "EMTY"
A8 Name
A9 Last Name
etc....
Some times there is a emty cell between adress and sometimes not.
Also the number of cells may variate (downwards). One complete adress could sometimes be 6 cells and other times 5 or even 4. Mixed in same file

I want it to be sorted so that it appear in a new worksheet sorted like this
A1 Name, B1 Last name, C1 Adress 1, D1 Adress 2, E1 Po Nr, F1 city
A2 Name ...............etc.......

Some ideas ????
 

Answer:Solved: EXCEL 2007 - macro - Real Challenge !!

15 more replies
Relevance 69.29%

Ok, first of all I'm new to your site and WOW! I am incredibly impressed with the wealth of knowledge I've come across while searching through various forums! I've already found all kinds of great macros and ideas that will make my workload a lot easier. That being said, I have a problem with efficiently sorting some data and I hope one of you macro gurus can assist :)I've found several macros that do something similar to what I'm looking for, so hoping to get something a little more specific. Basically, I have some weather equipment that monitors up to 5 things in the air and then transmits data to my computer via radio, and spits out text files that I then copy into excel. It creates one text file for each thing I'm monitoring. The format is:Date (mm:dd:yy:hh:mm:ss), WhatIsMonitored, Unit#, ConcentrationSo for example if I'm using this equipment to monitor the concentration of oxygen in the air over the course of a couple minutes using just one unit (and by unit I mean the machine that is doing the monitoring), it will give me something like:09/01/2010 17:02:55,O2,U_10_O2,20.90000009/01/2010 17:03:09,O2,U_10_O2,20.90000009/01/2010 17:03:25,O2,U_10_O2,20.90000009/01/2010 17:03:40,O2,U_10_O2,20.90000009/01/2010 17:03:55,O2,U_10_O2,20.90000009/01/2010 17:04:10,O2,U_10_O2,20.90000009/01/2010 17:04:25,O2,U_10_O2,20.900000So for in this case it's not too much trouble to just copy the text into Excel, then use the "text to columns" feature to put it in columns.The problem I'... Read more

Answer:excel 2007 macro to sort data to new sheet

After you use the text to columns feature, I assume your data looks like this:
A B C D E
1 09/01/10 17:02:55 O2 U_10_O2.txt 20.9
2 09/01/10 17:03:09 O2 U_11_O2.txt 20.9
3 09/01/10 17:03:25 O2 U_11_O2.txt 20.9
4 09/01/10 17:03:40 O2 U_11_O2.txt 20.9
5 09/01/10 17:03:55 O2 U_12_O2.txt 20.9
6 09/01/10 17:04:10 O2 U_12_O2.txt 20.9
7 09/01/10 17:04:25 O2 U_13_O2.txt 20.9
This will probably need some tweaking, but it might get you started:Sub SplitSheets()
Dim ws As Worksheet
Dim lastDate, srcRw, dstRow As Integer
Dim newdate, wsName As String
'Determine last row with data
lastDate = Range("A" & Rows.Count).End(xlUp).Row
'Loop through data
For srcRw = 1 To lastDate
'Remove slashes from dates
newdate = Month(Cells(srcRw, 1)) _
& "-" & Day(Cells(srcRw, 1)) _
& "-" & Year(Cells(srcRw, 1))
'Build worksheet name
wsName = newdate & " Unit " & Mid(Cells(srcRw, 4), 3, 2)
'Create sheet if it doesn't exist
On Error Resume Next
Set ws = Sheets(wsName)
On Error GoTo 0
If ws Is Nothing Then
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = wsName
End If
'Copy row to next open row in corresponding sheet
dstRow = Sheets(wsName).Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets(1).Cells(srcRw, 1).EntireRow.Copy _
Destination:=Sheets(wsName).Cells(dstRow, 1)
Set ws = Nothing
Next
End SubClick Here Before Posting Data or ... Read more

10 more replies
Relevance 69.29%

Hi Guys,

First of all thanks for the great support provided by your forum!

I'm using with remarkable profit the following macro edit by Rollin_again (sorry in case the nick is wrongly written!)

PHP:

[SIZE="5"]Sub ReplaceText()



For 
Each vCell In Range("A2:A200").Cells



Columns
("C:C").Cells.Replace What:=vCellReplacement:=vCell.Offset(0Read more

Answer:Macro Multiple Find and Replace in Excel 2007

To get a green fill the code you could use would be
vCell.Interior.Color = vbgreen

but is you place this before Next cCell, I think all the cells for vCell would turn green.

You turn the colouring back to normal by using

vCell.Interior.Color = xlNone

Give it a shot
 

3 more replies
Relevance 69.29%

Hey guys,I need some help. All of my data is in Sheet one listed as:Column A - NameColumn B - DateColumn C - Amount PledgedColumn D - Amount ReceivedColumn E - Balance OwedAll of the headers of those columns would be the same on each worksheet. What I need help with is that I need to create new worksheets for each new year in the Sheet 1 data. For example, If column B is 06/01/2006 then it needs to go to the "2006" sheet. 07/05/2008 would need to go the the "2008" worksheet and so on. This project is huge. Nearly 8300 lines. Any help would be greatly appreciated. Thanks in advance.

Answer:Excel 2007 Macro To Copy Rows to Tabs

Do you know how to write/modify VBA code?The code in this thread does pretty much what you are asking for, although the ranges it uses as the list for the new sheets is different than yours.Let us know if this helps...http://www.computing.net/answers/of...

13 more replies
Relevance 69.29%

Greetings -

Given:

Target Complete Money
A B C
1 1/1/09 1/7/09 50.00
2 1/5/09 1/12/09 100.00
3 1/7/09 1/14/09 50.00
4 1/17/09 1/17/09 25.00
5 2/2/09 1/26/09 25.00
6 2/5/09 150.00
7 2/6/09 150.00
8 2/5/09 75.00

Currently i have the following formula to calulate how much money that I might make.

{=SUM((MONTH(A2:A100)=1)*C2:C100)}
This works fine as I have no blank cells in column A

If I try to apply this formula to column B I get an incorrect number as I have blank cells in there with no values.

Is there a way to continue to use this formula but have it check for blank cells and if it finds any, don't add those values??
Thanks!
 

Answer:Excel 2007: Date/Sum Macro/Blank Cell

Welcome to the board.

How about the attached? Contains 1 "master" dynamic named range & 2 others based on the "master". Use E2 to define the month you're after.
 

3 more replies
Relevance 69.29%

I wrote a Macro in Excel that will open an e-mail and send it using the following code.

ActiveWorkbook.EnvelopeVisible = True

With ActiveSheet.MailEnvelope
.Introduction = Range("T22")
.Item.To = Range("T15")
.Item.CC = Range("T16")
.Item.Subject = "CST CARRIERS"
.Item.Send
End With

I want to attach a file to this e-mail before sending but don't know the syntax to do this. I know exactly where the file will be stored as well as the file name. Does anyone know the syntax for performing the attachment of the file?


I know you can attach files to these e-mails because if you insert a "Stop" statement just Prior to the ".Item.Send" statement and run the macro you can see and manually send the e-mail. There is an attachment button on the e-mail that I have successfully used to manually attach the file prior to sending the e-mail. I tried recording a macro to get at the code but it didn't copy anything while I performed the manual steps.
 

More replies
Relevance 68.88%
Question: Excel Email Macro

Hi

I am trying to write a macro in Excel 2003 to automatically send an email using Outlook to a list of individuals asking them to confirm their personal details using the attached template. Could someone show me where I am going wrong with the script please?

Many thanks
 

Answer:Excel Email Macro

16 more replies
Relevance 68.88%

I have an Excel model (office 2000) which has a range of cells that contain if/then statements. I'm trying to get Excel to automatically send an email if any of the cells within that specified range were to provide a value that was false. Is this possible? Thanks for any insight.
 

Answer:Email Macro in Excel??

16 more replies
Relevance 68.88%
Question: Excel Email Macro

Hi All,

I have a spreadsheet and i am needing to create a macro that will opena new message in Outlook and populate the 'To' Field with one cell and then the 'CC' field with 2 email addresses, one from one field and the other from a different field. then the subject from another field and then the body from another. it then leaves the message window open so i can check it right and hit send.

I found the script the other day but can not find it anywhere including this site.

Any help on this will be great.

JPL

Any
 

Answer:Excel Email Macro

Hi JPL,

You could use this function, just pass the values to it
Code:

Function MailData(mSubject As String, mMessage As String, Sendto As String, Optional CCto As String)
Dim eSubject As String, EBody As String
Dim app As Object, Itm As Variant
Set app = CreateObject("Outlook.Application")
Set Itm = app.CreateItem(0)

With Itm
.Subject = mSubject
.to = Sendto
If Not IsMissing(CCto) Then .CC = CCto
.Body = mMessage
' .Attachments.Add (NewFileName1) ' Must be complete path
.Display ' This property is used when you want
' .send
End With
Set app = Nothing
Set Itm = Nothing
End Function

It's from one of the posts, I do not remember which one
 

1 more replies
Relevance 68.88%

Any way of controlling the time a email is sent via a excel spread sheet using vba?
 

Answer:Excel - macro email-

6 more replies
Relevance 68.47%

I have XP and just installed Office 2007. In order to get a 2003-like environment, I set up a customized Quck Access Toolbar that is meant to come up every time I open Excel. I also have a Personal Macro Workbook I wish to automatically open every time I open Excel.

To do this, I set them up as Add-ins in the ...\Application Data\Microsoft\AddIns\ directory:
* PersonalMacroWorkBook.xlam
* MyQAT.xlam

The issue is that when I launch Excel directly (say, from the Start Menu), the two add-ins open and work. However, if I launch Excel by directly opening an Excel file, neither of the two add-ins are opened. [Note that this happens whether I'm opening file with extension .xls, .xlsm, or .xlsx.]

One other point - this used to work just fine. The issue arose recently - and I think I can trace it back to Excel crashing a few days ago. It crashed, incedentally, independent of any action by me - I was actually working in a different application at the time.

Can anyone tell me what's going on? Can you help me fix this?

Thanks,
angrybuddha

More replies
Relevance 68.47%

How do you make a macro that does the following:

go to next cell down in Column D
copy contents (a single word) of that cell
select entire Column A
find first instance, in Column A, of the word that was copied from Column D, above, (the word is part of a sentence in a single cell in Column A),
underline just that word (not the whole sentence) in Column A
loop

 

Answer:Excel 2007 macro - locate specific word & underline it

Hi Brad, welcome to the forum

I just recorded a amcro that does that and translated it to vba code
Code:

Function ULinetxt(cVal As String, uStr As String)
Dim tp1 As Integer, tp2 As Integer
If InStr(1, LCase(cVal), LCase(uStr)) > 0 Then
tp1 = InStr(1, cVal, uStr)
tp2 = Len(uStr)
With ActiveCell.Characters(Start:=tp1, Length:=tp2).Font
.Underline = xlUnderlineStyleSingle
End With
End If
End Function

Code:


Sub test()
Call ULinetxt(ActiveCell.Value, "underline")
End Sub

You could use this function in vba macro and it's done

Select a cell with your data string and rin the test macro, replace the word "underline" with the text you want underlined and the first occurrence (if found) will be underlined, case is not matched.

Let me know it it works for you.
 

1 more replies
Relevance 68.47%

I'm really new with macros, but I want to know if it is possible to do the following scenario and how to accomplish it:

I have two sheets in one workbook. On sheet 1, there is an inventory which contains codes, dates, titles, and most importantly, subjects in 6 columns (G-L). On sheet 2, I put the old subjects (from G-L on sheet 1) in column A and the new subjects in column B. I want to utilize a macro to find all the "sheet2 column a" subjects on sheet 1 (match entire cell contents) and replace them all with the new "sheet 2 column B" subjects. Can this is done? How would I do it?

Thank you for any advice you might have, I truly appreciate it!

A
 

More replies
Relevance 68.47%

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

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

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

More replies
Relevance 68.47%

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.
 

More replies
Relevance 68.47%

I have a recorded excel (MS Office 2003 professional) macro that does not run at all in MS Office 2007 professional is there an easy edit that I can do to make it work?

SmallFines macro:

Sub SmallFines()

Range("AQ2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.FillDown

Range("AQ3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Rows("2:2").Select
Range("AM2").Activate
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=43, Criteria1:="CO"

Columns("A:AQ").Select
Selection.Copy
Sheets("Carry Over").Select
Range("A1").Select
ActiveSheet.Paste
Rows("1:2").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

Columns("AG:AG").Select
Selection.Insert Shift:=xlToRight
Range("AG1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*100"
Range("AG1").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.FillDown
Columns("AG:AG").Select
Selection.Copy
Columns("O:O").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("R:R").Select
Range(Selection, Selection.End(xlToRight)).Sel... Read more

Answer:recorded 2003 excel macro no longer works in 2007

I think a sample sheet would be better.
Does the macro do anything at all?
Do you get an error?
Remember that Macro's are disbled by default in 2007.
Check that first.
If you have a sample, I'm now using both 2003 and 2007 so I could take a look at it.
 

1 more replies
Relevance 68.47%

Hi,

Can someone please help? I need a way to be able to find and replace in Excel on a mass scale. I have three colums A, B, C. 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 wherever it appears in Column C

Column A ********Column B ******** Column C
Cat ************Dog************* www.lynchie.com/cat/UK
etc etc**************************www.lynchie.com/horse/uk
*******************************www.lynchie.com/cat/uk
*******************************www.lynchie.com/mouse/uk

(Apologies for the bad rendering of an Excel Sheet)

This is a simple find and replace. However, I have approximately 1,000 pairs in column A and B which need to be found and replaced in Column C (and Column C is 100,000 rows+).
Is there any macro / process which I could use or I'm I doomed to do a find and replace 1,000 times?

Any help / suggestions would be hugely appreciated,

Thanks

Lynchie
 

Answer:Solved: Macro Multiple Find and Replace in Excel 2007

9 more replies
Relevance 68.47%

Hi,

I'm new to this, so any help would be appreciated.

My task is this - make checkboxes that would swoh only wanted rows and then copy any wanted rows to another sheet which has a specific design.

So I have 2 columns "Description" and "price".To each row I made 15 checkboxes with macro that should remove unwanted rows using this macro:

Sub CheckBox2_Click()
Rows("1:3").Select
If Selection.EntireRow.Hidden = False Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Range("$A$1").Select
End Sub

And this actually works (to my surprise), but the thing is I want to make a button that would have macro which would copy only checkboxed (marked) rows to another worksheet. I tried to record macros, but that didn't work because it seems that I have to link all checkboxes to that button and only then I'll get a result. Now it copies all rows, but not the marked ones.

SO PLEASE HEEELP ME
 

Answer:macro excel 2007 that copies filtered rows to another sheet

13 more replies
Relevance 68.47%

Hello,

I was hoping someone could provide me with some detailed instructions on how to create a macro to automatically sort some columns I have and then enable it so that I can place a button on the worksheet which will activate that macro.

Any help would be greatly appreciated!
 

Answer:Solved: Running A Macro Using A Button In Microsoft Excel 2007

Is anyone able to help me with this?
 

3 more replies
Relevance 68.06%

My sense is this is a pretty simple request, but I'm completely illiterate when it comes to VB.

I have an excel spreadsheet with a number of data points. I'm looking to export the entire sheet to a CSV file and send it via Outlook. Any suggestions?
 

More replies
Relevance 68.06%

Windows 7 --------------------------------------------------------------------------------First thing...I know nothing about macros!! I do know a very little about formulasI have created "IF" formula in excel 2010, based on a date it will create a send due in column "E", =IF(D5=$A$2,HYPERLINK(mailto:"&$K$1&"?subject="&A5&-B5&"&body="&$C$3,"sendworks great but, I have to go thru 86 rows in column "E" and hit "Send Due" numorous times,then hit send again on all the emails, can we automate this some how, like a macro that engadges when I open my outlook every morning?Thank u

Answer:Need Macro to automate email from excel

I have written code in Outlook, which is essentially a template, you fill in the details, all sorts of stuff happens accordingly to what you have selected, and you press send and it will send the emails to whoever it is you want. Iv done this in Outlook, so my question is, why do you need this to be done in Excel? is Outlook acceptable?

3 more replies
Relevance 68.06%

I have a user who wants to create a macro which will automatically send an excel spreadsheet into outlook and email it.
Any suggestions welcome. Thanks.
 

More replies