Copy Cells #2
I have 2 workbooks:
# 1 Workbook.("TBD.xls").worksheet("Sheet1")
#2 is a workbook that I open with this VBA macro:
fName = Application.GetOpenFilename
I also have this VBA Macro for fname
blastrow = Cells(Rows.Count, "B").End(xlUp).Row
isum = Application.WorksheetFunction.Sum(Range("b1:b10000"))
Cells(blastrow + 2, "B").Value = isum
My Question is: How do I copy the value of "Isum" in the
Workbook.("TBD.xls").worksheet("Sheet1") in cell "F34"
Workbooks(&q...deleting blank rows for up to 60000 rows of data
I have worksheets with up to 60000 rows in one column I have tried using the
following macro from this site and it does not do anything. Any ideas?:
Dim i As Long
.Calculation = xlCalculationManual
.ScreenUpdating = False
Lastrow = ActiveSheet.UsedRange.Rows.Count
For i = Lastrow To 1 Step -1
If WorksheetFunction.CountA(Rows(i)) = 0 Then
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
The code works f...Problem with Data Types
hi to all
I am writing a function whose parameters are the form that is active when
the function is called (frmFROM), and the form that the function then opens
My problem is that the frmTO is used as a string in line 5 and 8, but as a
form in line 6. Whichever way I Dim frmTO, I get a crash. How can reslove
1 Function ReturnToMenu(frmFROM As Form, frmTO As String)
2 frmFROM.Visible = False
3 Dim prj As Object
4 Set prj = Application.CurrentProject
5 If prj.AllForms(frmTO).IsLoaded Then
6 Forms!frmTO.Visible = True
8 ...Replace cell letter
Not sure how to explain this, but im looking to save a lot of manual entry.
ie. sheet1 is fetching its data from sheet2 A1:A1000 and displays the data
all over sheet1 to many different cell positions in different columns/rows.
How do I point to new data from sheet2 B1:B1000 by replacing all the cells
"=+Sheet2!A####" to "=+Sheet2!B####" in sheet1 while keeping the same cell
positions??? Hope it makes sense...please HELP!
Try Ctrl+H to launch Find and Replace dialog..Make sure you select 'Formulas'
&qu...Jump to formula destination ? ? ?
If I have a very large workbook, and say on Sheet1 a formula "=Sheet12!a1"
is there a shortcut way to jump directly to sheet 12 cell A1 ? ? ?
Thanks in Advance
It depends on what your definition of "jump" is.
"Dodge Brown" <firstname.lastname@example.org> wrote in message
> Hi All
> If I have a very large workbook, and say on Sheet1 a formula
> is there a shortcut way to jump directly to sheet 12 ...Hiding "0" values in pivot table
In a worksheet I have a listing of stock records with ItemNumber, Date,
StockLocation and NumberOnStock. When I make simple Pivot Table with
ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock"
I would like to hide rows where the sum is "0". But this does not seem
to be possible.
Tried to follow the guidelines in the Microsoft article:
But no luck :-(
You could use conditional formatting on these cells - highlight the
cells,...Sharepoint services, Infopath and Excel formulas
I created a form in Infopath and pulled the information into a list in
Sharepoint. when i import becasue of the design of the form I am getting
multiple reponses which are being stored in one cell. I need to count the
number of responses.
e.g. Column 1 Column 2 Column 3
Not applicable Yes Not
Row 1 Yes No Not
No Not applicable Not
Not applicable...Check Book Formula
I'm a real green horn when it comes to excel, I've
searched the data base and am pretty baffled by some of
the questions...Wow....anyways, I feel kind of silly
asking as this is probably very simple. I just don't know
how i would go about setting up a check book style
spreadsheet. The last three colums would be Income,
Expense, Balance. I understand how to add or subtract,
what I don't know is how to I tell the spreadsheet to
SUBTRACT the expense to get the Balance or and ADD the
income to get the balance in each row. Do I have to type
a formula in each row to do ...Vertical Scroll Includes Empty Cells
On one of my Excel spreadsheets, all of the empty cells
are being included in the vertical scroll (which
increases the document size immensely, making opening,
closing and saving an extremely tedious process).
How can I change the view so that only the cells
containing information was included in the vertical
You need to reset your used range. Have a look here for info:
"Diana" <email@example.com> wrote in message
> On one of my E...Hiding formuals in cells question
Hi. I was wondering if there was a way to hide the formulas in cells without
protecting the sheet. I am making something for other teachers at my school
and I want to hide the formulas so they won't mess anything up on the
spreadsheet. Any help would be greatly appreciated. Thanks!
not without protecting the sheet but why is this a problem for you?
"JP" <JP@discussions.microsoft.com> schrieb im Newsbeitrag
> Hi. I was wondering if there was a way to hide the fo...HELP: big problem with Excel, source cells, crashing and general madness
I have been on microsoft.public.excel.crashesgpfs but so far just the
one reply (from someone who has exactly the same problem as we do and
has not yet solved it either).
Anyone here can help???
My boss has this massive Excel spreadsheet with a number of worksheets
linked with other spreadsheets etc.
He is using Excel 2000 on Office 2000.
His computer was crashing with the above whenever he tried to do work
on it. We bought him a brand new computer, 1 gig of RAM, 80 GB of HDD.
We're a small company - a charity - so this was as powerful as we
This was a week ago and it...Printing Shaded Cells
I suddenly can't print out the cells that I have shaded with grey with my
black and white printer. It shows it in Print Preview but then it doesn't
print the grey. HELP!
Usually it will print what shows in preview. Probably related to printer
Did you try it before?
> I suddenly can't print out the cells that I have shaded with grey with my
> black and white printer. It shows it in Print Preview but then it doesn't
> print the grey. HELP!
Yes, I have lots of experience with Excel. It turns out I have to shade the
cell w...Cells Locking up
For some reason on all Excel documents that I open or
create, when I just select a cell and move my cursor the
sheet highlights wherever my cursor goes. I am unable to
stop this and unable to close Excel or select anything
from the Tool bars. The only way can close is through
Is there some sort of setting that I have selected or is
my Excel corrupt?
This might seem an odd solution, but try clicking
1) your middle mouse button (we have the same at work with drive
2) at the bottom; near the sheets tabs (I've seen this before when i
loads the ...Using IF in formula
I am trying to build up a YTD value in a cell on a
worksheet named YTD. The YTD value is the sum of cell
ref "A17" on each worksheet named JAN, FEB, MAR...etc upto
DEC. The value of "A17" is only taken into consideration
if the value of cell "R27" on the respective worksheets is
greater than "0". eg. if cell "R27" is value 0 on w/sheet
MAR then the YTD value is the sum of cells "A17" on
w/sheets JAN and FEB, if the value is 0 on w/sheet APR
then the YTD is the sum of cells "A17" on w/sheets JAN,
FEB and MAR...and...How do I stop cells from expanding?
Hey all. Just got Excel 2003, and have a real basic question.
I'm working in large files with very long cells, and everytime i click into
one to edit it, it expands to take up almost the entire screen. Problem is, i
need to look at cells in other columns, and this is a huge pain. How do i get
it to stop?
Thanks in advance!
If it is the formula bar that bothers you can turn it off temporarily under
"frodomojo" <firstname.lastname@example.org> wrote in message
news:FD91C628-9456-454D-9E2A-EF42F6F7F494@microsoft.co...importing bin data
Since Integration Manager does not offer the ability to import Inventory
items along with their bin locationsand quantities, is it worthwhile to try
to import bin quantities into GP via Table Import?
Table Import certainly offers that advantage over IM and any other
integration product. Take a look at my article on Table Import at
http://dynamicsgpblogster.blogspot.com/2008/10/often-overlooked-yet-powerful-table.html for a complete overview.
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
The D...hide my name
When replying to an email or creating one from scratch how do i hide my name
in the "from" field...thx
fred <email@example.com> wrote:
> When replying to an email or creating one from scratch how do i hide
> my name in the "from" field...thx
Are you saying you want the reply to be anonymous?
Brian Tillman [MVP-Outlook]
no, I don't mind if my email is revealed.. just my name. I have tried by
removing it from the "User Name:" field in the email account but it
continues to show up. I have also removed from the header informati...Need Help with a Formula #4
I am new to excel. Trying to set up a formula for the following 2 examples
1. Semi Annual payments of $440,000 for 20 years, or 8Million dollars now,
what is the interest rate that was used to arrive at the 8 million dollars
2. Semi annual payments of $440,000 for 20 years, an interest rate of 7%,
how much should the payment be in a lump sum today?
Thank you for your help, if you could provide me the formula so that I can
play around with various amounts or interest rates that would be helpful.
=RATE(40,-440000,0,8000000)*2 = -9.3945%
=PV(0.07/2,40,440000) = ($9,396,231.83...Hiding and Un-Hiding Data fields
I am using a form for people to select data via dropdown menus and then
having it run through a query to filter results. Is there a way that I can
have certain feilds hidden until a toggle button or something of the like is
Basically I want have three choices of buttons: Year, Quarter, and Month.
What I ideally want to happen is when someone selects the button for year, a
hidden drop down menu appears where they can then choose from the available
years to filter through the query.
I would say I am above average with using access, but I have no idea how to
hide fields and then...Available Resources
I have a user with W2K and Office 2003. She runs macros in Excel in a
spreadsheet that takes data and then creates a new spreadsheet with that data
in it. It is very large. Other users with similar resources can run this
just fine. (She has more memory, speed and space than anyone could ever use..)
1/2 way through the routine Excel locks up. Then it says, "Excel cannot
complete this task with available resources. Choose less data or close other
Then she gets a Microsoft Visual Basic Window that says 400. Then she gets
two further windows, one winmgmt.exe and ...Put Active Worksheet's Name in a Cell #3
Is there a way to put the active worksheet's name in the cell? Not the
file name of the whole workbook, just the name of the currently
Thanks for your help.
There are a few ways this can be done using either macros or functions,
here's one of each:
(sourced from Chip Pearson's site, http://www.cpearson.com/excel/excelF.htm)
I recommend checking this page out as it also shows ot...How do I hide gaps in an Excel 2007 chart?
I have a bar chart that is showing gaps and i don't want them. The select
data > hidden and empty cell button does not give an option to hide gaps. I
think by default they are hidden but if you even click on this to see what it
does the gaps are shown and no way to turn off.
If you hide the actual rows then with the Plot visible cells only
setting on the gaps will be removed.
In xl2007 the setting is on the Select Data dialog, Hidden/empty cells
> I have a bar chart that is showing gaps and i don't want them. The s...How to enable auto height with merged cells?
I previously asked a question regarding auto height with merged cells and was
given a code to enter into visual basic. I entered the code and copied &
pasted a narrative into the row. It originally seemed to work. But after
closing the workbook and re-opening it another day the 'referenced' row is
back to the original height.
Is there a way to for the row height to expand and remain sized so anyone
accessing the workbook can read the narrative? Additionall, I need the row to
remain at the full height when printing the worksheeet.
...Hiding a worksheet #3
I want to hide a worksheet. So, I went to the format menu and the "worksheet" option is grayed out. Why? And, how do I "ungray" it.
is your worksheet protected ?
> I want to hide a worksheet. So, I went to the format menu and the
> "worksheet" option is grayed out. Why? And, how do I "ungray" it.
I think Frank meant to ask about the Workbook--not the worksheet.
(Check under Tools|protection|Protect workbook or Unprotect workbook)
My question is: Is there another sheet in the w...Sumif where source values are assigned by "if" formula
I am trying to sum the values in a column (Q20:Q300) based on an assigned
designation in another column (N20:N300).
The destination cell is Q11.
The criteria value is found in cell N11.
Values in cells Q20:Q300 are assigned via an "if" condition dependent on a
value assigned in column "O".
When I place the placed the following formula in my destination cell (Q11),
it returned a value of "0" which is incorrect. My assumption is that the
formula is recognizing the numeric value in column Q as text and is not
formula in Q11 ... =sumif(N20...