Copying and pasting with a macro
I posted this up yesterday and am very pleased with the corrections I got. I
do have another question though:
How can I make it so that the copied information is inserted into sheet 2
and takes on the format of the target cell rather than keeping the original
x = 3
y = 24
Do Until Cells(x, 12).Value = "END"
If Cells(x, 12).Value <> "" Then
Cells(x, 12).Copy Sheets("2").Cells(y, 1)
x = x + 1
y = y + 1
...Pivot Table Formulas 06-06-10
I need to create a variance formula for the data selected in a pivot table.
My problem is that the data is coming in from one column and I don't know how
to distinquish the data by year in the formula. I have put a small selection
of my data below. I would like to show the variance between 2009 and 2010
for all months selected in the pivot table.
WDS# EA UOM PRICE EXTENDED Month Year
0623864 6 EA 9.73 58.38 Jan 2010
4286879 2 EA 2.784 5.568 Jan 2010
0623864 9 EA 9.73 87.57 Feb 2010
4286879 1 EA 2.784 2.784 Feb 2010
3514738 1 EA 13.72 13.72 Feb 2010
0623864 5 EA 7.85 39...."Filling Down" Formulas without changing values
I want to copy a formula down to the next 20 cells. When I highlight the cell
and the next 20 blank cells below, and hit the "fill down" button, it copies
the cells, but also becomes additive. For instance, the D45 changes to D46,
D47, etc. Before reformatting recently, that only did as above if I dragged
a formula down by grabbing a corner. How do I change the fill-down command so
that it only copies-down?
change the formula to
"Portland Ken" <Portland Ken@discussions.microsoft.com> schrieb im
Newsbeitrag n...Hided formulas appearing with copy/paste
Is there a way to prevent copying hiden&protected formulas (cells) on
When copy/paste (on a different worksheet) is used all the formula
appear on, you need only to select the range where the hiden formula
Because the files may have different names it is impossible to link th
formulas to a base
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com
~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements
If I did Format|C...copying data validation to another workbook
am i able to copy my data validation cell that i created in one workbook over
to a new excel file not associated with the original. I have no problem
copying validation from one area to another within a workbook but can't seem
to bring the validation commands over to a new file.
Just select the column or range on which you are having the Data Validation
and give Cntrl+C and Press Cntrl+N to open a new workbook or open the
workbook on which do you want to paste the Data Validation. Now select the
Column or Range of cells and do Right Click>>Paste Special>>select...Excel and Notepad: how avoid additional inverted commas after copy and paste?
If I write a multi-line text in a cell (then go down with alt+enter)
and after copy the cell pasting on Notepad, it display before inverted
commas and after textual content.
Instead, if I select directly the content from the formula bar, it
Do exist a way for copy and paste directly from the cell without
select from the formula bar?
Maybe with a macro?
I used the PutOnClipboard rout...Copies of sent mail go to my Sent Items folder instead of other mailbox Sent folder.
I have two Exchange 2000 mailboxes, of which I use the Outlook client to
access. In this other mailbox, from which I occasionally send email, I
noticed copies of sent mail go to my own mailbox Sent Items folder instead
of the other mailboxes Sent Items folder.
Is this a feature that can be changed?
...copy of users emails
how can I as the administrator get copies of a user's "sent" or "received"
email put into a copy folder?
Do you want "all" users, or just a few? If it is everyone, then you can
simply enable Journaling. On the properties of the Mailbox store, there is
an option to archive a copy of all messages sent or received by the store to
a specific mailbox. Of course, you need to be aware that this will be
absolutely UGLY, as it just creates a copy of all messages and sticks them
in the same Inbox. If you want a better sol...Formula #40
I need a formula that would allow me to type a cost center and once I type
the cost center the cost that I type in a seperate column will roll into
Assume you enter the cost in B1 and also want that cost to
populate cells A20,F15 and H2.
In A20,F15 and H2 enter this formula:
>I need a formula that would allow me to type a cost
center and once I type
>the cost center the cost that I type in a seperate column
will roll into
Thanks Bif!! Here is a anot...Copy Contacts from Outlook 07 to Outlook 07 ??
I'm running Outlook 07 in Win XP on one partition and in Vista on the other.
(Dual boot Hdd-0)
How can I copy my Outlook 07 Contacts from one to the other?
What is the file extension?
Please help I'm frustrated enough as it is with Vista.
Why copy them? simply point Outlook 2007 to your .pst file in the other
Milly Staples [MVP - Outlook]
Post all replies to the group to keep the discussion intact. All
unsolicited mail sent to my personal account will be deleted without
After furious head scratching, Dave K wrote:
| I'm running Outlook 07 in Win...Can you copy an Excel cell without the automatic line break?
Whenever you copy a cell (CTRL+C), Excel automatically appends a line
break at the end. This makes it impossible to paste the cell directly
into a single-line text field.
Can you copy an Excel cell without the automatic line break?
Is there a hidden line break in the cell that I can remove?
Is there way I can get Excel to place the result directly in the
clipboard without the automatic line break?
Is there a way I can get VB to interface with Excel so that I can get a
copyable field that doesn't contain an automatic line break?
I don't know if you can stop the way excel works, b...basket with fruits : numbers and formula
Hello all !
I m back with another pb...
I have a basket with fruits : 5 apples, 2 oranges, 9 tomatoes. Some of them
are ripe, others go rotten...
How to get a chart with :
the number of rotten apples and the number of ripe tomatoes ??
Before trying, I thought it was easy... :-)
Thanks for your help !!
To create a chart, you could enter the data in columns, e.g.:
Type the numbers for each category. Then select a cell in the list, and
use the Chart Wizard to create a chart.
> Hello all !
> I m back with another pb...
...Need help with formulae comparing date-based numbers
I'm trying to write a Excel formula that enters an integer value for a cell,
the value representing an age in days. To get this value, I'm comparing
values in two cells. One cell may or may not have a value, but it establishes
the upper limit for the integer value.
The problem is this: I have tried using both the IF and DATEDIF functions,
and neither returns an integer value. Both give me date values, such as:
4/29/1900. (Kudos to the programmer who sent my data aging in reverse;
perhaps Einstein could learn something from this relativity.) The syntax in
my formulas (bel...Downloading Multiple Copies of Messages from Server
I just got a new computer and upgraded to Outlook 2003,
and I check email from 3 different accounts (all POP3).
I have it set to leave copies of the message on the
server, but every time I check for new mail, each message
gets downloaded again. I've never had this problem
Any help is greatly appreciated.
Are you using dialup or broadband?
If outlook gets disconnected before sending the final commands, it doesn't
know it already downloaded the messages and redownloads them.
Diane Poremsky [MVP - Outlook]
Author, Teach Yourself Outlook 2003 in 24 Hours
I have recently purchased i new legal copy of office 2007 but it keeps
telling methatthe copy is not legal how do i stop this hasppening when i know
i purchased a legal copy
you should contact the
the software you have
may been activated on a
DatabaseBen, Retired Professional
- Systems Analyst
- Database Developer
- Veteran of the Armed Forces
- nntp Postologist
~ "share the nirvana" - dbZen
"Auz&qu...Can inserted rows automatically include existing worksheet formula
I frequently need to insert rows to a worksheet which contains a formula
column. Is it possible to insert a row without having to manually copy down
the formula from the previous row?
Have a look at David McRitchie's site for a macro to insert rows and maintain
Gord Dibben Excel MVP
On Tue, 20 Sep 2005 11:49:03 -0700, "tgdavis"
>I frequently need to insert rows to a worksheet which contains a formula
>column. Is it possible to insert a row wi...Help with autofilling a formula. TIA
Ok, I have a Excel sheet that is 42 row by 28 columns. The Rows each
reference a kH (carbonate hardness) value from 0.5 to 20 in 0.5 degree
steps. the Columns each reference a pH value from 6.0 to 8.6 in .1
degree steps. The formula I'm using 3 * KH * 10( 7-pH ) calculates
the CO2 level of the water. I would like to be able to just somehow
fill all these cells with the formula that references the correct row
and column and not have to enter the C and R values in each formula.
I'm sure this is possible but I'm not that experienced of an Excel
I assum...Copy Last 13 items #2
I have 2 worksheets no1 is a continuous row after row of monthly listings
i.e. date, amount, no of items from 1999 to date.
Worksheet no2 just shows the last 13 months.
I would like a formula to go in worksheet 2 so it will automatically update
its self after the current months figures have been added to the bottom of
I was told =offset(a1,count(a;a)-1,0) would give me the last item in a row
less 1, but have not been able to get it to work successfully,
Any other suggestions gratefully received.
Sorry originally posted this to programming board by mistake - not cross
p...Formulas don't work in certain cells #3
One formula is: =SUM(C10:C15)
these same formulas work in the cells right next to these wit
different cell references. However, no matter which cell I put th
above formulas in, the result is the same - either blank with a dash i
the cell or zero. I checked all the cells that the formulas refer t
and none has any errors.
kalik247's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1594
View...cell reference in a formula is called
Please confirm the correct answer to the below question
cell reference in a formula is called?
I'm not sure what you are asking for. In a user defined function
in VBA, Application.Caller will return a Range reference to the
cell which called the function.
In a formula, ADDRESS(ROW(),COLUMN()) will return the address of
the cell containing the function.
Microsoft MVP - Excel
Pearson Software Consulting, LLC
"???????????" <???????????@discussions.microsoft.com> wrote in
news:22EA36DA-A680-4414-87DF-EBD3BA96820...Formula for different sheets
I would like to create a formula that relates to different sheets in m
workbook when a selection to it's name is made.
The book contains 13 sheets.
Sheet 1 - sheet where I do the lookups etc
Sheets 2 to 13 are the 12 months of the year.
Could I create a formula in Sheet 1 that would automatically refer to
Sheet with the name of the month I'm looking for if I created a dro
Sheet 1 in A2 = drop down list with month names
A4 - a lookup or IF formula finding data from one of the other sheets
The data in each sheet is exactly the same.
Message p...Copy cell appearance but not conditional formatting
I have a spreadsheet with some conditional formatting. How can I cop
the appearance of the cells without the formatting condition
Message posted from http://www.ExcelForum.com
AFAIK this is not possible
>I have a spreadsheet with some conditional formatting.
How can I copy
>the appearance of the cells without the formatting
>Message posted from http://www.ExcelForum.com/
I suggest you copy the formatting and then remove the conditiona
This latter should be fairly quick, using t...Using VBA to copy a chart into Powerpoint
Does anyone know how to do this?
I've got to the point where I have powerpoint open but I can't create
new slide. The following opens powerpoint then falls over on the lin
"pres.slides.add 1 , pplayoutblank".
Set ppt = CreateObject("powerpoint.application")
ppt.Visible = True
Set pres = ppt.presentations.Add
pres.slides.Add 1, pplayoutblank
Message posted from http://www.ExcelForum.com
Check out the example...Confirm File Replace when copying mails to network drive?
Is there a "trick" to overcome the problem that arises when copying a mail to
a network drive that has the same name as an existing mail already in the
I save useful mails onto my network rather than only in Outlook archives,
but as each project has the same name I very often get the "Confirm File
Replace" messge that says "this folder already contains a file named xyx.
Would you like to replace the existing file with this one?" As these are two
separate and different mails, I actually need to keep both!
It seems that if all mails are sav...Copy only visible cells after applying "Subtotals"
in an XL-list I need to copy only a block of subtotals to another area,
without the detail rows in between. I believe there once was a command like
"paste only visible cells" (or similar), but I can't find a solution.
Anybody who knows how to do this ?
Thank you in advance,
You can copy the visible cells.
Show just the subtotals
select the range to copy
Edit|goto (or F5 or ctrl-g)|special|visible cells only
and paste where you want.
"H.G. Lamy" wrote:
> in an XL-list I n...