Macro filter on query
I need to run 8 append and update queries using the same date range. I
created a Form with the "Month" field as the filter and a command button to
run the macro to run the Append & Update queries for records in the month
range. I can't get the Apply Filter macro to work on the Query Date field.
Apply Filter action will not do what you seek.
Your queries must have a WHERE clause that refers in some way to the textbox
on the form where you've entered the Month value/data that you want to use
in the queries.
To assist you further, you need to post ...Search and replace macro needed
I need a macro that will replace a space with a non-breaking space <b>for the
selected text only</b>
Why do you need a macro, when you can use a simple Find/Replace operation?
Select the desired range then:
Find: ' '
Click 'Replace All'.
[Microsoft MVP - Word]
"GregNga" <GregNga@discussions.microsoft.com> wrote in message news:FBBBB44D-6B2D-4445-9DFA-50EBC8BF20FE@microsoft.com...
>I need a macro that will replace a space with a non-breaking space <b>for the
> se...Arrange Footer
I have a file with 31 sheets and every sheet has a footer ABC 6/1. How can I
arrange this footer in all sheets to ABC 6/0.
Thanks in advance.
Select all sheets. To do this, select the first sheet then hold the Shift
key down and select the last sheet.
Note: Selecting the first sheet and holding Shift before clicking another
sheet selects all sheets in between. Holding Ctrl key, you can individually
select additional sheets.
Open Page Setup and edit the footer and it will apply to all selected sheets.
&...Macro to affect all sheets
I want to select all sheets in a workbook, insert a column, and put a formula
in that column. It works fine when doing it manually, but if I try to have a
macro do this, it only affects one sheet. Is there any way to do this?
The general principal is shown in the following code.
Pls note: the code places the formula: =2+2 in cell F1 of every sheet.
I'm sure it will b no problem 4 u yo change it 4 your needs.
For Each SH In ActiveWorkbook.Sheets
SH.[F1].Formula = "=2+2"
&...Footer Settings in an Excel Sheet
I am setting up the footer to an Excel page through VBA
Script but sometime, the left footer is not starting from
the extreme left (i.e., it leaves some spaces and seems
like started from the column B). The following script
used to set up the footer.
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = analyst
.CenterFooter = filename
.RightFooter = mydate
.LeftMargin = Application.InchesToPoints
Whenever I try to run my macro, nothing happens. When I try to change the
security settings through the Tools > Security option, nothing happens. I
can do it at home but not at work. Is there a specific reason? I'm the
administrator of the computer and I am not connected to any domain.
We see this sometimes when another application starts Outlook before you =
do and VBA never quite loads properly. Try shutting down Outlook and =
using Task Manager (Ctrl+Alt+Del) to make sure it's really, really shut =
down before you restart it. You may also w...macro to save new file (same as tab name)
I am recording macro and want to save the new macro- recorded sheet as the
same name as the sheet tab.
I do have the tab name in one of the cells A1, using =cell("filename",A1)
Example : sheet name week1, want to save new file as week1
I Can copy and paste into existing macro
thanks in advance
Message posted via OfficeKB.com
(remove nothere from the email address if mailing direct)
"Brian Thompson via OfficeKB.com" <u1568...How do I move a chart using a macro?
I have a nice little macro that creates two charts on several worksheets in
My problem is that it places one chart right on top of the other. I just
need to offset it enough to see both charts, or at least to remind myself
that there are two of them there.
I tried variations of the "Activechart.parent . . . ." commands I found
elsewhere on the site, but I always get the debug screen when I get there.
I'm using OFFICE 2007 and Vista.
Assuming you know what cell you want as the top left cell positio...inserting file's location in footer
I often get word docs with the file's address inserted in on the bottom of
the page such as the following example:
C:\Documents and Settings\Lenovo User\My Documents\doc name
How do I insert a file's location in the footer of a document?
In Office 2003: Click View->Header and Footer. The Header and Footer menu
should appear along with the Header box. Click on the icon "Switch between
Header and Footer" (ninth icon) and the Footer box will appear for you to
type your text. Close box when done. When editing you can simply click on the
foot...errot in macro
I am trying to run a macro that opens up several other spreadsheets and
copies and pastes selected data into the spreadsheet where the macro resides.
Sometimes this works. Sometimes I get the message 'Microsoft excel has
encountered an error and has to shut down' and it then shuts down Excel.
Whenever I step through the macro, I never get the error.
What is going on here?
The only time that this has happened to me when doing simple copy/paste
operations using a macro is if I copy and then paste special as values.
Other than that without lots more detail its hard to t...e-mail macro
I have a report which i want to send it to different people and I have
created a macro which can do that. Now I want to include a query
object (it is a long number) in the subject line or report filename so
that I dont have to type it again and again. Does anyone have any
Thanks in advance
I'll give you a VBA coding of what I am talking about.
In this VB code,
Option Compare Database
How do I add personalize message like my telephone numer, address My name at
the end of my message instead of me having to write it all the time
It's called a signature. Use the help button and you will find all the
steps on how to make one.
Next time you post here, please state your version of Outlook as the
solutions vary depending on your version.
Judy Gleeson - MVP Outlook
Acorn Training and Consulting
see what Outlook training can do to improve productivity:
I can't seem to get this sorted out right but what I need to do is have
a macro that isnerts the date into a document as text. At the moment
I'm using =NOW() and =NOW()+7 for the two dates, but that of course
updates everytime I open or modify the spreadsheet.
Any idea where I can go for this? I haven't done any VB in a while and
forget my way around, so a website or such would be fine.
The macro would be run when a new document is made from the template.
Just type Ctrl+; (Ctrl and semicolon). That will insert today's date.
In second cell, enter: =A1+7
In Col. A there are dates, formatted as dates in the format of Sun - Jan 01,
2010 and it goes to the end of 2010. How do I delete all the Saturday and
Sunday's in one shot? Connie
Format the column as Special/ ddd, filter, custom, equals Saturday or equals
Sunday, delete rows.
"Connie Martin" wrote:
> In Col. A there are dates, formatted as dates in the format of Sun - Jan 01,
> 2010 and it goes to the end of 2010. How do I delete all the Saturday and
> Sunday's in one shot? Connie
Here is a non macro solution. Change the format of your dates ...Footer
I have my email address as the footer in many of my print outs.
Is there a way I can change all of them to my new address at one go?
They are in various files and folders.
Thanks for any help.
Alternatively could I have my email address as the default footer? And then
change all footers to this?
not really possible to change all in one go. Would require a macro
which loops through all your workbooks.
But for setting a default create a new workbook with this footer and
save the file as template (*.xlt) and use this template
&qu...Sum subform footer with criteria
I have a continuous main form. In the form header I have an unbound
combobox ([cboLot_Num],1 col only) wth approx 6 selections from a totals
query row source.
I would like to select an item like "LotNo 3" from the combobox, and in the
main form footer, show the count of alll records that meet that criteria in
[Location]. Can't seem to get the syntax right. Footer field name is
=sum(iif([Location] = "LotNo 3",1,0)) --works
=sum(iif([Location] = [cboLot_Num],1,0)) --fails with #error
There are 20 "LotNo 3" [Location] records.
Tried all kinds of ...unpassword macros
I have an Excel spreadsheet with macros which are password protected. The
password has been lost and we need to edit and create new macros. Any
suggestions for working around the password protection?
There is no easy way around a lost VBA password...
You will need to get someone to hack it for you...
> I have an Excel spreadsheet with macros which are password protected. The
> password has been lost and we need t...Macro to Find Column
I have several spreadsheet with a column for e-mail address. Some cells have
two e-mail addresses. I need to separate the addresses so that there is only
one address per cell, then delete the second address. This is easy to do
using text-to-columns and I recorded a macro to do just that.
The problem is that the column where the addresses are located can change
between files. In one file it's column A. In another file it's column D.
When I record a macro, it's the same column every time (always column A), so
the macro doesn't work for every file.
The report has PCODES header and PCODES footer. I used the textbox to create
the COUNT([PCODES]) at the PCODES' footer.
At the result, I got 3, 4, 2, 1, ....25 ( it counted each PCODE). For
example, the PCODES will be 121213, 121214........121225
EX: PCODE 121213 121214
= 3 121214
I would like to sort these results so that the biggest number will be
appeared on the top of the report. ...CONDITION IN MACRO #2
This is an update to an earlier question I posed. I am using Access 2002. I'm
trying to use a Condition in a Macro to determine whether or not to SetValue.
It appears that the Condition will not allow me to use a "wildcard"
(asterisk). The Field I'm checking is filled with entries like CRUZ SA or
CRUZ SB or CRUZ SC. I simply want to set the Condition to look for
CRUZ*...meaning any entry that begins with CRUZ. The Condition I set up is
[CLASS]="CRUZ*". It doesn't work. But, when I set the Condition to
[CLASS]="CRUZ SA" it works. Access Help seem...Macro problem 12-08-09
I have had someone write me a macro as below. This basically copies the top
10 numbered rows from Sheet 1 and pastes into Sheet 3. However it pastes the
rows starting from Row 1 in Sheet 3 therfore not allowing me to put any
column headings/titles etc. in Sheet 3. I need the info to be pasted from row
8 and below rather than row 1. Any help?
Dim lngRow As Long, ws As Worksheet, lngNRow As Long
Set ws = Sheets("Sheet3")
For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Range("A" & lngRow) > 0 And Range("A" &...automatically running a macro
I have written a macro which operates by means of ctrl + Q. (This letter was
merely an arbitrary choice!)
The action is as follows:-
1. Enter new data in a cell then press <ENTER>
2. The data is then automatically compared with the contents of a cell on
3. If the new cell data is greater than or equal in value to that data in
the cell on the other sheet then change the font colour of the new cell data
to red and display the difference between the two cells in a cell in another
4. Otherwise just display the new contents of the cell in a black font
colour as ...Protect Footer on Worksheet
I want to be able to protect only the footer on an excel
worksheet. Is it possible to do this without protecting
Effectively I want to put a disclaimer in the footer and
ensure that it does not get deleted.
If there is not a way, is there another way to put the
disclaimer in (i.e. a watermark on the sheet),
I don't think that protection will help, either.
I've found that the only way I could insure that the header/footers were what I
wanted them to be was to use a macro that set them right before I printed.
If you record a macro that sets the header/foot...Need Outlook Macro
Hi all ,
since it is not possible to record macros in Outlook ( like in Excel ) and
I am very new in this field I need a little macro to execute following
When I start ( run ) the macro it should delete all tasks in an specific
task folder and after that copy a certain amount of tasks which are marked
and / or selected by a user defined field in to the same task folder where
previously other tasks were deleted before.
I woul appreciate any help. May be someone can send me this little macro
so that I will only implement it by changing / adding the names of my
Again thanks in ...Excel Headers and Footers
When I save an Excel document as an html (web page), the
headers and footers will not display when you open it up.
You can do a print preview on the document and see the
header and footer just fine. You can even get it to
print, but you can not get it to display on the web. Any