Save copy of active sheet
I use the code below to save a copy of my Workbook in a specific file
path (testing to see if the folders already exist along the way) with a
specific, data dependent file name. It works, but I'd like to tweak it
a bit and I'm not sure how to write the code.
I'd like to save only the active sheet instead of the whole workbook.
I'd also like to save only the values and formats to the new file, not
the formula and macros.
Anyone have any advice on how to accomplish this?
Here's my current code:
If Not Len(Dir("g:\users\one\" & Range("h6&...Date conversion question
Am trying to understand why is that when I have a variable MaxDate
(undeclared) and is has a value of 38697 and if I use
=TEXT(MaxDate,"mm/dd/yy") I get the correct value of 12/12/09 but if I
use function =CDATE(MaxDate) I get 12/11/2005 as an output? Also the
format command didn't work either to convert MaxDate with the code
=format(MaxDate, "mm/dd/yy") as it also gave 12/11/2005 when the
correct output was 12/12/09. I am trying to understand this.
Assuming you are using XL2003 or lower... if you click to Tools/Options =
on the menu bar, th...Testing for an error in a range of cells...
I searched through the previous posts on this topic, but have almost
no experience using VBA in excel, so I was wondering if there was a
simpler way to achieve this:
I want to write a formula in B2 that will basically say, "If the range
of cells B3 to B24 contains an error, return x if true, and return y
I know how to do this for a specific cell rather than a range, for
Any help in achieving this without delving into VBA would be much
appreciated (I just don't have the time to start learning this stuff
as I am w...Combine Text from Multiple Cells
How do I combine text from mutliple cells into one cell?
one option - well two really:
if the information is in cells A1, B1 & C1 and you want it combined into D1
then in D1 type
=A1 & " " & B1 & " " & C1
this will give you the contents of each with a space between
=CONCATENATE(A1," ",B1," ",C1)
which will give you exactly the same thing
Hope this helps
"Carter" <email@example.com> wrote in message
> How do I combine tex...Report Writer and effective dated tables
Hi, I am new to using report writer. I am trying to write a report that will
just pull back the latest effecitve dated row by employee.
A SQL statement that can do this would be something like:
SELECT MAX(effective_date) FROM myTable WHERE effective_date <= Now();
I can not figure out how to do this in Report Writer. Any suggestions?
Anyone know if this is possible?
> Hi, I am new to using report writer. I am trying to write a report that will
> just pull back the latest effecitve dated row by employee.
> A SQL statement that can do thi...Paste to adjacent cells
If I have Data in cells A1 to A10
or C1 to C23 etc
and I want to paste more Data along side, to B1 to B10 or D1 to D23. Even
better would be if I could paste to either side of the data.
Is there away to do this without having to highlight those cells, this would
save me allot of time.
Thanks in Advance
When you paste, it pastes the amount of stuff you've copied. If you've
copied 10 vertical cells, that's what it's gonna paste. You need only
select the top-left-most cell before you do a paste.
mvpearl omitthisword at verizon period ne...Due Date in Statement of accounts??
Can i print the sales statement of account showing the
DUE DATE ? If yes, kindly please help me to create the
field. Thanks in advance.
...Invisible Formula in Cells !!!!!!!!!!!!!!!!!!
I have this strang excel file. When I enter formular into it, it don't show
thae value but when I enter anything else in the cell, that is visible. The
formula is in the cell, its just not visible.
On Apr 21, 7:41=A0am, Ayo <A...@discussions.microsoft.com> wrote:
> I have this strang excel file. When I enter formular into it, it don't sh=
> thae value but when I enter anything else in the cell, that is visible. T=
> formula is in the cell, its just not visible.
Probably the sheet is protected and Hidden check box of Protection tab
of Format Cell dial...how do I free units cells?
I try to put in alphabetical order a column but appear a messagge about cells
joined togheter. How can I free the whole page ? thanks and sorry for my
Try selecting the whole sheet Ctrl A then Format Cells Alignment and
make sure the merge cells box is unticked. then try your sort
Dav's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27107
View this thread: http://www.excelforum.com/showthread.php?threadid=513559
...format numbers #2
I have a list of audio books in an excel 2003 spreadsheet.
One column should show the duration of the book in this format:
8 hrs 23 mins.
But what shows in the column is a 5 digit number, such as 54463 as an
I cannot figure out how to format this column to translate this number
into the hrs and mins of the actual book.
Could someone please tell me how to do this?
Thanks for your help
Where does this number come from? It does not seem to relate to 8 hours
23 minutes at all (eg it isn't the total minutes for example or
seconds). What is the source of this informa...Outlook 2007 Edit Business Card Format
I've just started using Outlook 2007 with the Business Contact
I'd like to edit the format of the Business Card in both Outlook and
Business Contact Manager. I have discovered how to edit an individual
card but not how to edit all the existing cards and future cards.
Can this be done and if so how?
It can't be done; that functionality just isn't built into Outlook. I =
expect, though, that some enterprising programmer will produce an =
inexpensive tool or sample application to apply a particular format to =
Sue Mosher, Outlook ...sort dates
My dates use the system dd/mm/year
if i press the sort button.. it will sort by day->month->year
I want to sort by year->month->day
how do i do this??
Message posted from http://www.ExcelForum.com
The best thing to do is to store your data as real Excel dates. Then they
will always sort properly, regardless of which format you display the date
As you have numbers, rather than dates, sorting isn't so easy. The choices
1. Create a Date field (=Date(year,month,day)) and sort on that.
2. Split out the day, month and year, then sort on the three separa...Custom Format text XX:XXXX:XX
I would like to have text custom formatted so that if a 8 character
words is typed, it is automatically broken up like this XX:XXXX:XX.
This works just fine with numbers with a ##\:####\:## custom format,
but it will not work for text. Any suggestions?
mustard's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=20017
View this thread: http://www.excelforum.com/showthread.php?threadid=346023
Number formatting only works on numbers.
But you could use a little macro waiting for y...Date in the query is always short date
I want to capture the LONG date from a query, so I create a parameter for the
date (mm ddd yy) . In its properties, it's format is long date, BUT it always
shows up the short date. WHY?
Dates are stored as decimal number counting days from 12/31/1899 midnight
with time as the decimal fraction of a day.
Formats are just different ways to display the information. So you got to
set the format. In design view of the query click the field of the grid that
has the datetime field, right click, scroll down and select Properties.
Click 'Format' and then in the pull down...format of emailed excel file
In excel 2003 when I email a a particulat worksheet in a workbook I lose all
the formating when it is recieved in Outlook 2003. Is there anyway to
preserve the excel "look"?
...Conditional format based on data from another column
Yesterday I asked about the possibility of conditional formatting a range of
20 random numbers into 2 different formats. Thank you for that, but now it
seems that 3 of my 20 numbers are now requiring a different cell to be
highlighted in yet another colour.
Currently the situation is as follows:-
Column G has a list of random numbers ranging from 1-20, with columns either
side pertaining to the random number located in the relevant cell in Column
It now appears that 3 numbers in this random list do not require
highlighting in Column G and have amended the condition formattin...cell won't wrap text
Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text.
All other cells seem to wrap text fine. One cell in particular won't wrap
text. It displays all XXXX instead. I did an Edit|Clear and re-entered the
text and it did it again.
I adjust the height of the cell to no avail. 70+ other cells in this SS
work just fine with the same amount of text.
Try changing the format from Text to General
> Very simple Excel file. 3rd column over is is formatted as Text | Wrap Text.
> All othe...Format a cell to display as all caps
Is there any way to format a cell so that text entered will always display in
Not Directly !......but you can use the UPPER function.
If your data is in A1, put =UPPER(A1) in A2.
This will convet whatever you type in A1 to Uppercase in A2
> Is there any way to format a cell so that text entered will always display in
You cannot format a cell to display caps.
You can use event code in the worksheet to change the text to caps when you
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column &g...Extract data in the right part of a cell
I have some cells contains "*" , I want to extrat the text to the right of the "*". For example, if A2 contains "txt*123", I want it turn out to be "123".
Try the following formula,
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"plumstone" <firstname.lastname@example.org> wrote in
> I have some cells contains "*" , I want to extrat the text to
th...Excel should have the option to merge contents when merging cells.
When Excel merges cells, it keeps only the upper-left-most contents and
deletes the others. It would be nice to have the option (maybe a toggle
button in the options menu) to concatenate the contents of all the merged
cells, so that the information in the lower-right cells is tacked onto the
end of the information inthe upper-left-most cell. This is not a big deal,
but it would be a nice bell/whistle to save some steps.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I...Compare cells for identical values
I need to be able to compare a range of cells and highlight if the same
value appears to 2 cells in the same row. Does anyone know the VB code
that will help me do this?
red_debs's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14753
View this thread: http://www.excelforum.com/showthread.php?threadid=263746
check out Chip Pearson's website he has lots of stuff on dealing with
I need to be able to set set a deadline based on prior dates. These can go
back years. I would like to set up a current 'next appointment, for instance
ever 90 days, based on a date of arrival. For example, if date of arrival is
2/13/03, how do I make a formula that is effective now. In other words, the
cell updates to a new deadline every ninety days, and stays current.
Thanks a bunch.
Maybe this which calculates the number of 90 day periods that have elapsed
since the start date.
Understand though that working with dates is tricky. There i...Modify general format to date format.
When importing from payroll, it gives me all dates formatted as general
numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to
convert these numbers into dates?
If your Windows Regional Settings recognise a format of mddyyyy, you can
apply the below formula and format the formula cell to date..
Jacob (MVP - Excel)
> When importing from payroll, it gives me all dates formatted as general
> numbers (i.e. 07/17/2009 is presented as 7172009. Is there any way to
> convert the...Conditional Formatting OR Expression??
I am using all 3 conditions so is there a way to combine these 2 lines of
code into 1 condition? They both result in turning the cell red:
'(this line looks to see if there is a percent other than 0 or 100 and
calculates the percent where we should be versus the actual, cell H33)
'(this line looks to see if there are still enough days to complete)
Cell H33 is a % Comp, H32 is End Date, $C$2 is Today's Date and $H$8 is #
days to complete, just FYI.
Do an OR
=OR(cond1...How do you change the data type of a cell using VBA?
For instance, with VBA I am trying to place "February 2007" in a cell
but it keeps showing up as Feb-07. I want it to show up as literal
Range("A1").Value = Chr(39) & "February 2007"
Gary''s Student - gsnu2007c
You could keep the value a date and just format the cell the way you like:
.numberformat = "mmmm yyyy"
.value = dateserial(2007,2,1)
You could also format the cell as text first:
.numberformat = "@" 'text