Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

How to set up an excel formula copy cells another worksheet Results

I need to know how to set up formula that If a conditional formula is true,
then it will copy certain required data into a new worksheet in the same
workbook in a certain order. Is this even possible?

confused,

i know your project is probably over by now but i came across your post
today and had to reply. i too suffer from "data slip" and you are the first
person to talk about. i am wondering if you have had any luck resolving the
problem. i understand select all needs to be done before sorting and i am
assuming you do this as well.

i have a similar spreadsheet in terms of size but mine deals with projects
and milestones so there are lots of dates on a single row. i began to find
dates that were way out of place. soon i began testing. i would enter a date
in a cell, save the ss, close, reopen, and the data was in a different cell.
with mine it didn't just slip to empty cells. i suspect your data copied over
other data also but it was harder to spot. my ss is shared by multiple users.
i have also noticed that the change history will not record changes correctly
(i.e. a cell which has not been changed will show a change made to it while
the cell whose data was changed does not show a change). the only way i can
resolve the problem is to unshare the ss, fix the cells and then reshare it.
this has really become a problem.

none of the techs i have spoken with have been able to recreate the problem.
the latest suggesting i have been given is to make sure all versions of excel
which edit the ss have been fully updated. like you i sort and filter the ss
all the time.

as a side note, i too am a novice compared to the average person answering
posts on this site but i had a few thoughts about your project if it could
still help. first, don't sort while the ss is filtered, this will hang your
computer forever - sort first then filter. second, this is a low tech
solution for comparing a ss without having a unique key column - what about
creating one ss which at least has all the last names filled in and make
another which has at least the zip and so on. compare each to another ss
which has been prepared the same way. when finished merge them back together.
i'm not sure it that makes any sense but maybe it will help.

anyway, anything you can share about "data slip" would be appreciated. thanks,

scott

"ConfusedNovice" wrote:

> Thanks, Dave. This thread is getting unwieldy, but I
> *think* I understand what you're advising.
>
> Sticking with the issues at hand, yes, the records are all
> one per row. Last Name is probably the best key, but there
> are some records with only a Company Name, & a few with
> only an email name. I have been using Last Name & Zip
> Codes to try & match things up.
>
> I discovered that when sorting some of the intermediate db
> versions by "new" entries, those newest entries were
> mostly correct. I am thinking of isolating these records &
> copy/pasting them on to the oldest db version. The idea is
> to try for a cleaner sheet to compare to the current one.
> Maybe it would be better not to merge these? It's crunch
> time - I will try your formulae on a test file today.
>
> And moving ahead: If I can execute the comparisons you
> suggest, and if I can achieve a cleaner list, then what's
> the best way to prevent data from slipping into the empty
> cells in the future? Should I make a new index column,
> number each record, and append & number all new records?
> Can people safely sort & filter the data? What if they
> update a record in a filtered list & then re-sort? Or
> Find/Replace & then re-sort? I still don't understand how
> some of the data slipped out of one record & into another.
> Until I do understand, I'm afraid it will continue to
> happen & all this work will be for nothing.
>
> Thanks again! It's an ugly way to learn Excel.
>
> Jane
>
> Subject: How to sort/update large excel db
> From: "Dave Peterson" >
> Sent: 9/30/2004 1:05:46 PM
>
> Just to clarify, is your data laid out one record per
> row? You don't use
> multiple rows to represent one "logical" record, do you?
>
> If you do, then this will make it even more difficult.
>
> But to match/merge two different worksheets, I would think
> that you'd need to
> have a unique key/index into the data.
>
> If you don't have this key, how do you match up your data
> to look for
> differences?
>
> I wouldn't put too much faith in having that unique key in
> the last name
> column--but maybe you can use a couple of columns that
> could serve as that key.
>
> For instance, if you had the lastname, firstname, address
> in 3 separate columns,
> you could use a helper cell and do something like:
> =A2&char(10)&b2&char(10)&c2
> (The char(10) probably won't appear in your data and will
> serve as a field
> delimiter--just in case there's records that might look
> identical when
> combined.)
>
> But even if you did this, you'd have to make sure that
> these fields weren't
> changed. If any were changed, then you couldn't use them
> for a match between
> worksheets.
>
> To find out if the records in one worksheet are in the
> other (based on any
> single column you can use--in your data or derived from
> your data):
>
> =isnumber(match(a2,sheet1!a:a,0))
> and drag down.
> You'll see True if it appears on that other worksheet.
>
> And use the equivalent to check the other worksheet.
>
> Once you've found your Falses--you'll want to inspect them
> to see if they should
> be cleansed--cleaned up to make them match.
>
> You could end up fixing the data on either worksheet--just
> to find the matches.
>
> Once you get rid of all the Falses, you can do the =vlookup
> () stuff to match
> merge.
>
> I'm not sure if all this work can be done on a quick
> schedule. This kind of
> stuff usually means fix, check, fix, check....until you
> can't find any more
> differences. Then you let someone else review it and it
> starts again.
>
> Good luck,
>
>
>
> ConfusedNovice wrote:
> >
> > Still trying to understand what I've got. There is no
> > column with all unique entries. Every column has blank
> > cells someplace. On 2 of the earlier database versions,
> > the leftmost column (Column A) is set up as "Index
> > Column," but not all the records have an "Index" number
> in
> > this column - some of them are blank, & some have zeros
> in
> > Column A. The most recent master does not have Index
> > column, it starts with a Last Name field.
> >
> > This brings me back to the question of how to prevent
> > names/values from slipping into the blank cells when
> > sorting & updating records. I have been reading a lot of
> > Excel info, but this is *not* my area at all, & I want to
> > fix these problems, not compound them. Should I be able
> to
> > identify key column(s)? If yes, how? And if no, is there
> > any safe way to sort when blanks exist in every column?
> > (Or can it be a Key & contain multiple blank cells?)
> >
> > There are ~17,000 records, & when the wrong values move
> > into a blank cell, that record doesn't indicate that it
> > has been changed. (Probably because it was not opened?)
> >
> > I'm seriously running out of time. I've got a pretty big
> > list of changes ready to enter directly on db records. I
> > also have isolated a few hundred recent entries by date,
> > verified them, & placed them in a separate worksheet.
> > (Hoping to append to a clean main list & sort into
> place.)
> > I don't think it's possible to verify every row.
> >
> > Yes, no matter how you look at this, it's a pain! I'm not
> > really dumb, just a total rookie, & your help is
> > invaluable.
> > To summarize:
> > Whatsup with "Index Column"?
> > How to identify/designate Key columns?
> > Implications of having blank cells throughout sheet?
> > Fastest way to add/edit records if I go back to an older,
> > cleaner version of the database?
> >
> > Thanks again!
> >
> > Jane
> >
> > >-----Original Message-----
> > >I'm saying that if you have two "live" copies of the
> > workbook, it'll be a pain
> > >to merge one of them back into the other.
> > >
> > >There's nothing built into excel that will force you to
> > make sure your key
> > >column entries are unique.
> > >
> > >When I do this, I spend some time verifying that the
> data
> > is at least
> > >consistent--no additional entries with the same key were
> > added. If you can
> > >believe/verify this, then, yeah, you can do =vlookup()'s
> > to merge the updated
> > >values into the one real workbook.
> > >
> > >It's just in my experience, avoiding this is usually
> > better. (Just give the
> > >file to someone else and tell them to return it when
> > they're done--and put a
> > >freeze on any other changes.)
> > >
> > >
>
>
>

I am not an Excel expert, so forgive me if some of my terminology is confusing/incorrect.

One of the most widely used basic features of Excel is the fill/drag feature. I am wanting to use this feature, but would like to implement it as an Excel function. What do I mean? See the example below.

• Suppose column A is filled with 100 numeric data entries, A1 through A100.
• Suppose that cell B1 will *eventually* be used for a function (e.g. - "=A1^2").
• Suppose that I want the cells B2 through B100 to have the same formula as cell B1, except replacing "1" with [their row #] (e.g. - if B1 is "=A1^2", I would want B2 to be "=A2^2", and so forth).

Usually I would use the drag/fill feature to drag cell B1 down to cell B100. Works great. However, what if I want to set up cells B2 through B100 beforehand so that they will equal whatever gets entered into B1 (in a drag/fill sense, so that they equal B1's formula, except using their own row #).

I know there are keyboard shortcuts one can use instead of dragging; that is not what I am looking for. The idea is that I will have a worksheet that involves a series of drag/fills all based on a formula from a single cell, and that I may want to go back and change that initial formula time and time again (but I don't want to keep having to re-dragging the necessary rows/columns every time the initial formula changes).

In brief, my problem really just boils down to this:

I would like to first type an Excel function into cell B2 that "copies" cell B1 (i.e. - like how the Edit -> Copy/Paste usually works), so that later when I type a formula into B1, the analogous formula instantly occupies cell B2.

e.g. - If I type "=A1^2" into B1, I want B2 to become "=A2^2".
Later, if I change B1 to "=1+A1^3", I want B2 to become "=1+A2^3". Etc.

Another way to articulate what I want is that I simply want a *relative* version of the "=" (it's an *absolute* "=", as is). What's funny is that most of Excel's other features are, by default, *relative*, except for "=". Is there some sort of "=RelEqual([cell])" function I don't know about?

Excel already has this functionality with their drag/fill feature; I'm shocked that I can't find an Excel function for this feature ... it seems like a pretty basic thing to ask for. I'm sure this "VBA" I've read about could do the trick, but VBA sure seems like overkill (and, I'm not in a position to use it).

Thanks in advance for the help!

Von Pookie's HTML Maker FAQ
Last updated: 07 July 2009

07 July 2009: Downloads are currently unavailable for Colo's site. If you would be interested in testing the new HTML Maker designed specifically for the MrExcel forum, please see this thread:
http://www.mrexcel.com/forum/showthread.php?t=399380

PLEASE NOTE: This FAQ was written while we were using phpbb forum software. I have not checked these instructions for accuracy with the new vBulletin software we are currently using. It is possible that some items listed here may no longer apply.

Please note the following:
This is not the FAQ for the VBHTML Maker nor the YaBB Table Maker.These directions were written using Windows 2000 and XP, and Excel 2002 and 2003. I do not (currently) know the information for Macs or any other versions of Windows or Excel.

How can I show a small image of my worksheet in my post?
You can simply download the handy HTML Maker add-in! The HTML Maker is available for download at Colo's site. Currently unavailable

I have downloaded the .zip file. What is the next step?
You may need to download an extraction program such as WinZip to open the .zip file and extract the add-in file. For now, just extract the HtmlMaker.xla file to your desktop. Once you have this, you no longer need the .zip file.

Next, you need to locate the Addins folder on your computer. There are several places you may see a folder with this name. From the Excel help file: "Add-ins are stored by default in one of the following places:
The Library folder or one of its subfolders in the Microsoft OfficeOffice folder.The Documents and Settingsuser nameApplication DataMicrosoftAddIns folder."If you're uncertain which folder to use, open Excel, go to the Tools menu and select Add-ins. In the dialog that will display, click the Browse button to see the path for the AddIns folder it is using. Once you've located this, you can simply drag and drop the HtmlMaker.xla file into it.

Note: If you cannot find the Application Data folder, you probably just need to change a setting on your computer.Open Windows Explorer (or any folder) and select Folder Options from the Tools menu.On the View tab, look for "Hidden files and folders" under the Advanced SettingsMake sure the option "Show hidden files and folders" is selected, click OK, and look for that pesky Application Data folder again.

I have added the file to the AddIns folder, but I am unable to activate it in Excel!
Start Excel, go to the Tools menu and select Add-Ins. You should see a dialog box with a list of available add-ins, with a checkbox next to each item. Simply find the one labeled Htmlmaker, check the box next to it and click OK. You should now have a new menu between the Window and Help menus called HTML. You have now installed and activated the HTML Maker, and should be ready to go!

How do I use this add-in?
Using the HTML Maker to post a "snapshot" of your worksheet on the message board is relatively simple.
[list=1][*]Make sure you have enabled HTML in your board profile.Click the "profile" link--which is in the group of text links at the top-right of every page of the board.In the list of preferences, make sure the option for "Always allow HTML" is set to Yes.Click the "Submit" button to save any changes to your profile[*]Make sure the HTML Maker add-in is active (see "I've added the file to the AddIns folder, but I can't figure out how to activate it in Excel!").[*]Go to the HTML menu and select "Convert sheet to HTML"[*]Select the range you want to copy
Please select only the range (preferably with little to no formatting) that is crucial to the question you are asking.[*]Click OK[*]On the message box that pops up, click "yes" (I honestly have no clue what this does, but I always click yes and it works just fine)[*]Another message box will appear, letting you know how many characters the HTML Maker code will use, and how many characters that are remaining for you to use in typing the rest of your message. Click OK.[*]A new browser window will open, displaying what your copied cells will look like when posted to the board.[*]In this new window, click the "Copy code to clipboard" button.
If this does not work...[list=a][*]If you are running Windows XP with Service Pack 2 installed, make sure you allow popups on the page (they will be blocked by default) and try clicking the button again[*]As an alternative:In this same window click the button that is on the page that is labeled "View Source." This is located right next to the "Copy to Clipboard" button.Copy the entire code that is displayed there.[/list:o:4e41f3da2f][*]Come back to the board where you are entering your message, and put your cursor in the area where you type the body of your message.[*]Press Ctrl+V in the area where you type. If you don't know HTML, it will look like a lot of gibberish. That's normal, don't worry about it. You can still edit and add to your message if needed at this point. Do not edit/add to the code from the HTML Maker.[*]When you are finished, click the Submit button to post your message. The table should appear in your post just as it did on the browser page you copied the code from.
Do not preview a message containing HTML Maker code. You will not be able to use the buttons on the preview page to submit your message.Do not quote a post using HTML Maker code. You will receive errors (when quoting a message, simply edit out any extra HTML Maker code).[/list:o:4e41f3da2f]Back to top

Known Issues & Errors

I installed the HTML Maker add-in, but the "HTML" menu you say should appear on the menu bar is not there.
It is possible that you have installed the wrong add-in. This FAQ is for Colo's HTML Maker, NOT the VB HTML Maker or YaBB Table Maker. Making sure you have installed the correct add-in is the first step.

If you have confirmed that you installed Colo's HTML Maker correctly, make sure you have enabled the add-in. Go to ToolsAdd-Ins, click the checkbox for the HTML Maker and click OK. The menu should then appear.

If you have performed both of the previous steps and the menu is still not appearing, post your questions in the Questions About This Board forum.

I followed the directions, but after posting the page is not displaying correctly.
You can either edit the post yourself and remove ALL of the HTML code, or report the post by clicking the "Report" button () on the post to notify the moderators of the problem. A moderator will edit the post to remove any/all HTML Maker code so the thread will be able to display correctly.

As to why the page displayed strangely in the first place, it's usually one of two things: either you didn't follow the directions and copied the source of the page the HTML Maker generated instead of using the "view source" button on the page (yes, there is a difference and we can tell from the code if this is the case), or you have inadvertently posted incomplete HTML Maker code--which is much more common.

HTML requires both opening and closing tags; if you do not post the entire code, the closing tags are not included and the board cannot interpret the code correctly.

When using an HTML Maker example in your post make sure that you have copied all of the generated code and that it is pasted into the message in its entirety. There is also a character limit for each message (but to be completely honest, I have no idea what that limit actually *is*). If you try to use multiple examples within a single post, you can hit the limit quickly and the end of the code could be cutoff.

Error: "Could not load an object because it is not available on this machine"
You may be missing a file called ietimer.ocx, which is required. Click here to go to Colo's site and check if the file is already installed on your computer. This link will also automatically install the file for you if it is needed.
The file is also available for download from Microsoft: http://activex.microsoft.com/control...86/ietimer.cab After downloading the file, it must be registered with regsvr32. The easiest way to do this is to right-click the file and select Install from the context menu.

Error: "Compile error." or "Can't find project or library..."
Open the Visual Basic Editor by pressing Alt+F11. Once in the editor, select the HTMLMaker in the Project window then go to the Tools menu and select References.
If there are any references that start with "MISSING," uncheck them and don't forget to save.
More information on this error can be found in this thread: http://www.mrexcel.com/board2/viewtopic.php?t=107231

Error: "Error Number: 91, Object Variable or with Block variable not set..."
Click OK to close the dialog box with the error and go to Tools->Macros->Security. Click the Trusted Sources tab and check the box next to "trust access to Visual Basic Project," and click OK.

In Excel 2007, the directions are slightly different:
Click the Office Button and go to Excel Options->Trust Center->Trust Center Settings->Macro Settings->Developer Macro Settings. Then you can Check the "Trust access to the VBA project object model" box and click OK.
(Thanks to Bradlee_S for posting this, because I would have never thought to add it otherwise)

Error: "Runtime error: 5 Invalid procedure call or argument."
When this error occurs, the following line will be highlighted in the debugger:
Code:
Open the Visual Basic Editor (Alt+F11) and go to Tools->Options. Select the General tab and make sure "Break on unhandled
errors" is selected for "Error Trapping."

First mention of this error (and solution) can be found in this thread:
http://www.mrexcel.com/board2/viewtopic.php?t=242967

I am unable to view formulas after clicking the formula link in the posted example
There are two or more posts in the thread with HTML Maker examples, which could cause a conflict in the Javascript code being used. There is no real "fix" for this issue that I know of. The only real workarounds are to not post a message using the HTML Maker code in a thread already using an example elsewhere, or if you absolutely need to show how the data is set up, use the HTML Maker to show your sheet and indicate what formulas are in what cells within your message. Users viewing the examples in Firefox may notice this problem as well. (I have this issue with Firefox no matter what, personally).

I am unable to copy the generated code to the clipboard.
As was discovered by board member Parry: "If you have installed Windows XP Service Pack 2, a Pop-Up blocker is installed [in Internet Explorer] as part of the pack and by default this will block active content.

When you select your cells and run HTML Maker it will create an HTML page of your image as expected, but the pop-up blocker will prevent you copying the code when you click the button to send Source to the clipboard. Simply click on the pop-up security information bar, then select Allow Blocked Content and you can now copy the HTML to the clipboard."

Back to top

Miscellaneous
[list][*]This add-in is simply to show a small "snapshot" of the selected area of your spreadsheet. However, some things will not display when posting an HTML Maker example, such as:Drawings and objects (i.e., items created from the Drawing toolbar)Clip art and other imagesCharts and graphsConditional formatting[/list:o:4e41f3da2f]Questions concerning the HTML Maker should be posted in the Questions About This Board forum.
If you have not used the HTML Maker add-in before, please post in the Test forum before making your actual post to make sure the example will display correctly. If any posts with HTML maker code end up "breaking" the page (including posts in the Testing forum), they will be edited by a moderator or administrator to remove the problem-causing HTML code from your post or the post itself will be deleted.

Find an error or something I missed?
Click to send me a private message

How to use Visual Basic for Applications (VBA) to change UserForms in Excel

How to Programmatically Manipulate a UserForm

How to Programmatically Create UserForms in Visual Basic for Applications

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
β€’ How Can I Import a Fixed-Width Data File into Microsoft Excel?
β€’ How Can I Sort a Spreadsheet By Cell Color?
β€’ How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
β€’ How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
β€’ How Can I Read Custom Summary Information Properties for an Excel File?
β€’ How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Search For Values in an Excel Worksheet?
β€’ How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
β€’ How Can I Determine the Value of the Last Cell in an Excel Range?
β€’ How Can I Convert an Excel Serial Number to a Date and Then Back?
β€’ How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
β€’ How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
β€’ How Can I Update and Then Break All the Links in an Excel Spreadsheet?
β€’ How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Add a Background Picture to an Excel Worksheet?
β€’ How Can I Replace Numeric Values in an Excel Spreadsheet?
β€’ How Can I Copy Data From One Spreadsheet to Another?
β€’ How Can I Replace Text in an Excel Spreadsheet?
β€’ How Can I Set the Print Area in an Excel Spreadsheet?
β€’ How Can I Delete Specified Rows in an Excel Spreadsheet?
β€’ How Can I Color Every Other Row in an Excel Spreadsheet?
β€’ How Can I Copy Selected Columns From a CSV File to an Excel File?
β€’ How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
β€’ How Can I Export an HTA Table to Excel?
β€’ How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
β€’ How Can I Assign a Background Color to Cells in a Spreadsheet and Then β€œSum” Those Cells?
β€’ How Can I Sort a Row in an Excel Spreadsheet?
β€’ How Can I Remove the Password When Opening an Excel Spreadsheet?
β€’ How Can I Import an Excel Spreadsheet Into an Access Database?
β€’ How Can I Save a Table in an Access Database as a Spreadsheet?
β€’ How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
β€’ How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
β€’ How Can I Configure Excel to Autosave Every 5 Minutes?
β€’ How Can I Save an Excel Chart as a Picture?
β€’ How Can I Change the Font Color in Excel If a Specified Condition is Met?
β€’ How Can I Convert an Excel Spreadsheet to XML?
β€’ How Can I Set the Default File Path in Excel to a User’s Home Directory?
β€’ How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
β€’ How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
β€’ How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
β€’ How Can I Add a Total Row to an Excel Spreadsheet?
β€’ How Can I Create a Custom Date Format in Microsoft Excel?
β€’ How Can I Keep the Screen From Updating While Running an Excel Script?
β€’ How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
β€’ How Can I Determine the Background Color of a Spreadsheet Cell?
β€’ How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
β€’ How Can I Copy a Worksheet to a New Spreadsheet?
β€’ How Can I Use Windows PowerShell to Automate Microsoft Excel?
β€’ How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
β€’ How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
β€’ How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
β€’ How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
β€’ How Can I Create a New Excel Spreadsheet at Midnight Each Night?
β€’ How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
β€’ How Can I Convert a Number to a Date in Excel?
β€’ How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
β€’ How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
β€’ How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
β€’ How Can I Determine the Last Row in an Excel Spreadsheet?
β€’ How Can I Remove All the Formatting from an Excel Spreadsheet?
β€’ How Can I Change the Footer in an Excel Spreadsheet?
β€’ How Can I Add Additional Worksheets to an Excel Workbook?
β€’ How Can I Sort Worksheets in a Workbook?
β€’ How Can I Import a Fixed-Width File into Microsoft Excel?
β€’ How Can I Add a Hyperlink to an Excel Spreadsheet?
β€’ How Can I Build an Array from a Column of Data in Excel?
β€’ How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
β€’ How Can I Center Text in an Excel Cell?
β€’ How Can I Change the Background Color of a Cell Depending on the Date?
β€’ How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
β€’ How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
β€’ How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
β€’ How Can I Insert a Column into a Spreadsheet?
β€’ How Can I Save a Single Excel Worksheet to a CSV File?
β€’ How Can I Password-Protect an Excel Spreadsheet?
β€’ How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
β€’ How Can I Randomly Assign a Font to Characters in a Word Document?
β€’ How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
β€’ How Can I Count the Number of Sentences and Paragraphs in a Word Document?
β€’ How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
β€’ How Can I Tally All the Items in a Microsoft Word Document?
β€’ How Can I Reset the Revision Number of a Word Document to 1?
β€’ How Can I Retrieve Field Values in a Microsoft Word Document?
β€’ How Can I Change the Paragraph Case in Microsoft Word?
β€’ How Can I Insert a Manual Line Break into a Microsoft Word Document?
β€’ How Can I Search For and Modify Formatted Text in a Word Document?
β€’ How Can I Search For Red Text in a Microsoft Word Document?
β€’ How Can I Change the Default File Save Format in Microsoft Word?
β€’ How Can I Put the File Name in the Footer of a Microsoft Word Document?
β€’ How Can I Add a Page X of Y Footer to a Microsoft Word Document?
β€’ How Can I Save Each Paragraph in a Word Document as a Database Record?
β€’ How Can I Replace a Specified Font in a Microsoft Word Document?
β€’ How Can I Apply a Theme to a Microsoft Word Document?
β€’ How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
β€’ How Can I Insert a Date Field in Word?
β€’ How Can I Add a New Item to the Microsoft Word AutoCorrect List?
β€’ How Can I Clear All the Formatting From a Microsoft Word Document?
β€’ How Can I Change the Default Highlight Color for a Microsoft Word Document?
β€’ How Can I Run a Macro After Opening Up Word?
β€’ How Can I Center-Align a Picture in a Word Document?
β€’ How Can I Change an Existing Hyperlink in a Microsoft Word Document?
β€’ How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
β€’ How Can I Insert Text Into an Existing Microsoft Word Bookmark?
β€’ How Can I Get a Total Page Count for All the Word Documents in a Folder?
β€’ How Can I Set the Document Orientation in Microsoft Word to Landscape?
β€’ How Can I Replace Text in a Microsoft Word Document?
β€’ How Can I Search For (and Reformat) Highlighted Text in a Word Document?
β€’ How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
β€’ How Can I Get a List of Available Metadata for Microsoft Office Documents?
β€’ How Can I Add Multiple Tables to a Word Document?
β€’ How Can I Search a Word Document for All the Words in Double Brackets?
β€’ How Can I Add Centered Page Numbers to the Footer of a Word Document?
β€’ How Can I Create a Table and Fill the First Column With a Range of Dates?
β€’ How Can I Rename a Word Document Using the First Three Characters in That Document?
β€’ How Can I Search for and Highlight Words in a Microsoft Word Document?
β€’ How Can I Right-Align a Single Column in a Word Table?
β€’ How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
β€’ How Can I Change the Font Name and Size for an Entire Word Document?
β€’ How Can I Set Word’s Revision View Mode to Final?
β€’ How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
β€’ How Can I Set Word’s Default File Location to be the User’s Home Directory?
β€’ How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
β€’ How Can I Change File Locations for Microsoft Word?
β€’ How Can I Insert a Symbol into a Word Document?
β€’ How Can I Hide a Specific Toolbar in Microsoft Word?
β€’ How Can I Extract Word Paragraphs That Use a Specific Style?
β€’ How Can I Insert Files into a Word Document?
β€’ How Can I Convert 1,000 .RTF Files to Word Documents?
β€’ How Can I Add a Hyperlink to a Word Document?
β€’ How Can I Put the User Name into the Footer of a Microsoft Word Document?
β€’ How Can I Determine Which Version of Word is Installed on a Computer?
β€’ How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Add a Record to a Database Using Windows PowerShell?
β€’ How Can I Delete a Set of Records From an Access Database?
β€’ How Can I Be Notified if an Access Database File Exceeds a Specific Size?
β€’ How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
β€’ How Can I List All the Fields and Data Types in an Access Database?
β€’ How Can I Append a Value to a Specified Field For Each Record in a Database?
β€’ How Can I Add the Contents of a Group of Text Files to an Access Database?
β€’ How Can I Import a .CSV File into an Access Database?
β€’ How Can I List All the Access Database Files on a Computer?
β€’ How Can I Print a Microsoft Access Report?
β€’ How Can I Compact an Access Database?
β€’ How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
β€’ How Can I List All the Tables in an Access Database?
β€’ How Can I Determine Which Version of Access was Used to Create a Database?
β€’ How Can I Delete a Table from a Jet Database?

Microsoft Outlook
β€’ How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
β€’ How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
β€’ How Can I Set a Reminder on All My Outlook Appointments?
β€’ How Can I Schedule an All-Day Event in Microsoft Outlook?
β€’ How Can I Run a Script Any Time Outlook is Started?
β€’ How Can I Determine the Age, in Years, of an Outlook Contact?
β€’ How Can I Save the Attachments for All My New Outlook Messages?
β€’ How Can I Convert an Extension Number to an Actual Phone Number?
β€’ How Can I Create a New Folder in Microsoft Outlook?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Get a List of Appointments for a Specific Month?
β€’ How Can I Save Emails That Are More Than One Month Old?
β€’ How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
β€’ How Can I Save All My Contacts as VCards?
β€’ How Can I Delete Unread Emails That Are More Than 6 Months Old?
β€’ How Can I Randomly Select an Email From an Outlook Mail Folder?
β€’ How Can I List All the Meetings Scheduled By a Specified Person?
β€’ How Can I Filter Outlook Messages By Email Address?
β€’ How Can I List All the Members of a Microsoft Outlook Distribution List?
β€’ How Can I Determine the Follow-Up Status of Outlook Emails?
β€’ How Can I Tell If Any of My Contacts Have a Birthday This Month?
β€’ How Can I Delete All the Messages in My Sent Items Folder?
β€’ How Can I Start Outlook If It Isn’t Already Running?
β€’ How Can I Connect to the Junk Mail Folder in Outlook?
β€’ How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
β€’ How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
β€’ How Can I Delete All the Email Sent From a Specific Person?
β€’ How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
β€’ How Can I Get Total Size and Number of Items in an Outlook Folder?
β€’ How Can I Convert an Outlook Email Message into a Text File?
β€’ How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
β€’ How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Apply a New Template to a PowerPoint Presentation?
β€’ How Can I Configure PowerPoint to Print Handouts Instead of Slides?
β€’ How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
β€’ How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
β€’ How Can I Change the User Information in Microsoft Office?

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
• How Can I Import a Fixed-Width Data File into Microsoft Excel?
• How Can I Sort a Spreadsheet By Cell Color?
• How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
• How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
• How Can I Read Custom Summary Information Properties for an Excel File?
• How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Search For Values in an Excel Worksheet?
• How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
• How Can I Determine the Value of the Last Cell in an Excel Range?
• How Can I Convert an Excel Serial Number to a Date and Then Back?
• How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
• How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
• How Can I Update and Then Break All the Links in an Excel Spreadsheet?
• How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Add a Background Picture to an Excel Worksheet?
• How Can I Replace Numeric Values in an Excel Spreadsheet?
• How Can I Copy Data From One Spreadsheet to Another?
• How Can I Replace Text in an Excel Spreadsheet?
• How Can I Set the Print Area in an Excel Spreadsheet?
• How Can I Delete Specified Rows in an Excel Spreadsheet?
• How Can I Color Every Other Row in an Excel Spreadsheet?
• How Can I Copy Selected Columns From a CSV File to an Excel File?
• How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
• How Can I Export an HTA Table to Excel?
• How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
• How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?
• How Can I Sort a Row in an Excel Spreadsheet?
• How Can I Remove the Password When Opening an Excel Spreadsheet?
• How Can I Import an Excel Spreadsheet Into an Access Database?
• How Can I Save a Table in an Access Database as a Spreadsheet?
• How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
• How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
• How Can I Configure Excel to Autosave Every 5 Minutes?
• How Can I Save an Excel Chart as a Picture?
• How Can I Change the Font Color in Excel If a Specified Condition is Met?
• How Can I Convert an Excel Spreadsheet to XML?
• How Can I Set the Default File Path in Excel to a User’s Home Directory?
• How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
• How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
• How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
• How Can I Add a Total Row to an Excel Spreadsheet?
• How Can I Create a Custom Date Format in Microsoft Excel?
• How Can I Keep the Screen From Updating While Running an Excel Script?
• How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
• How Can I Determine the Background Color of a Spreadsheet Cell?
• How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
• How Can I Copy a Worksheet to a New Spreadsheet?
• How Can I Use Windows PowerShell to Automate Microsoft Excel?
• How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
• How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
• How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
• How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
• How Can I Create a New Excel Spreadsheet at Midnight Each Night?
• How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
• How Can I Convert a Number to a Date in Excel?
• How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
• How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
• How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
• How Can I Determine the Last Row in an Excel Spreadsheet?
• How Can I Remove All the Formatting from an Excel Spreadsheet?
• How Can I Change the Footer in an Excel Spreadsheet?
• How Can I Add Additional Worksheets to an Excel Workbook?
• How Can I Sort Worksheets in a Workbook?
• How Can I Import a Fixed-Width File into Microsoft Excel?
• How Can I Add a Hyperlink to an Excel Spreadsheet?
• How Can I Build an Array from a Column of Data in Excel?
• How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
• How Can I Center Text in an Excel Cell?
• How Can I Change the Background Color of a Cell Depending on the Date?
• How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
• How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
• How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
• How Can I Insert a Column into a Spreadsheet?
• How Can I Save a Single Excel Worksheet to a CSV File?
• How Can I Password-Protect an Excel Spreadsheet?
• How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
• How Can I Randomly Assign a Font to Characters in a Word Document?
• How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
• How Can I Count the Number of Sentences and Paragraphs in a Word Document?
• How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
• How Can I Tally All the Items in a Microsoft Word Document?
• How Can I Reset the Revision Number of a Word Document to 1?
• How Can I Retrieve Field Values in a Microsoft Word Document?
• How Can I Change the Paragraph Case in Microsoft Word?
• How Can I Insert a Manual Line Break into a Microsoft Word Document?
• How Can I Search For and Modify Formatted Text in a Word Document?
• How Can I Search For Red Text in a Microsoft Word Document?
• How Can I Change the Default File Save Format in Microsoft Word?
• How Can I Put the File Name in the Footer of a Microsoft Word Document?
• How Can I Add a Page X of Y Footer to a Microsoft Word Document?
• How Can I Save Each Paragraph in a Word Document as a Database Record?
• How Can I Replace a Specified Font in a Microsoft Word Document?
• How Can I Apply a Theme to a Microsoft Word Document?
• How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
• How Can I Insert a Date Field in Word?
• How Can I Add a New Item to the Microsoft Word AutoCorrect List?
• How Can I Clear All the Formatting From a Microsoft Word Document?
• How Can I Change the Default Highlight Color for a Microsoft Word Document?
• How Can I Run a Macro After Opening Up Word?
• How Can I Center-Align a Picture in a Word Document?
• How Can I Change an Existing Hyperlink in a Microsoft Word Document?
• How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
• How Can I Insert Text Into an Existing Microsoft Word Bookmark?
• How Can I Get a Total Page Count for All the Word Documents in a Folder?
• How Can I Set the Document Orientation in Microsoft Word to Landscape?
• How Can I Replace Text in a Microsoft Word Document?
• How Can I Search For (and Reformat) Highlighted Text in a Word Document?
• How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
• How Can I Get a List of Available Metadata for Microsoft Office Documents?
• How Can I Add Multiple Tables to a Word Document?
• How Can I Search a Word Document for All the Words in Double Brackets?
• How Can I Add Centered Page Numbers to the Footer of a Word Document?
• How Can I Create a Table and Fill the First Column With a Range of Dates?
• How Can I Rename a Word Document Using the First Three Characters in That Document?
• How Can I Search for and Highlight Words in a Microsoft Word Document?
• How Can I Right-Align a Single Column in a Word Table?
• How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
• How Can I Change the Font Name and Size for an Entire Word Document?
• How Can I Set Word’s Revision View Mode to Final?
• How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
• How Can I Set Word’s Default File Location to be the User’s Home Directory?
• How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
• How Can I Change File Locations for Microsoft Word?
• How Can I Insert a Symbol into a Word Document?
• How Can I Hide a Specific Toolbar in Microsoft Word?
• How Can I Extract Word Paragraphs That Use a Specific Style?
• How Can I Insert Files into a Word Document?
• How Can I Convert 1,000 .RTF Files to Word Documents?
• How Can I Add a Hyperlink to a Word Document?
• How Can I Put the User Name into the Footer of a Microsoft Word Document?
• How Can I Determine Which Version of Word is Installed on a Computer?
• How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Add a Record to a Database Using Windows PowerShell?
• How Can I Delete a Set of Records From an Access Database?
• How Can I Be Notified if an Access Database File Exceeds a Specific Size?
• How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
• How Can I List All the Fields and Data Types in an Access Database?
• How Can I Append a Value to a Specified Field For Each Record in a Database?
• How Can I Add the Contents of a Group of Text Files to an Access Database?
• How Can I Import a .CSV File into an Access Database?
• How Can I List All the Access Database Files on a Computer?
• How Can I Print a Microsoft Access Report?
• How Can I Compact an Access Database?
• How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
• How Can I List All the Tables in an Access Database?
• How Can I Determine Which Version of Access was Used to Create a Database?
• How Can I Delete a Table from a Jet Database?

Microsoft Outlook
• How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
• How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
• How Can I Set a Reminder on All My Outlook Appointments?
• How Can I Schedule an All-Day Event in Microsoft Outlook?
• How Can I Run a Script Any Time Outlook is Started?
• How Can I Determine the Age, in Years, of an Outlook Contact?
• How Can I Save the Attachments for All My New Outlook Messages?
• How Can I Convert an Extension Number to an Actual Phone Number?
• How Can I Create a New Folder in Microsoft Outlook?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Get a List of Appointments for a Specific Month?
• How Can I Save Emails That Are More Than One Month Old?
• How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
• How Can I Save All My Contacts as VCards?
• How Can I Delete Unread Emails That Are More Than 6 Months Old?
• How Can I Randomly Select an Email From an Outlook Mail Folder?
• How Can I List All the Meetings Scheduled By a Specified Person?
• How Can I Filter Outlook Messages By Email Address?
• How Can I List All the Members of a Microsoft Outlook Distribution List?
• How Can I Determine the Follow-Up Status of Outlook Emails?
• How Can I Tell If Any of My Contacts Have a Birthday This Month?
• How Can I Delete All the Messages in My Sent Items Folder?
• How Can I Start Outlook If It Isn’t Already Running?
• How Can I Connect to the Junk Mail Folder in Outlook?
• How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
• How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
• How Can I Delete All the Email Sent From a Specific Person?
• How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
• How Can I Get Total Size and Number of Items in an Outlook Folder?
• How Can I Convert an Outlook Email Message into a Text File?
• How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
• How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Apply a New Template to a PowerPoint Presentation?
• How Can I Configure PowerPoint to Print Handouts Instead of Slides?
• How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
• How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
• How Can I Change the User Information in Microsoft Office?

My search resulted in WAY to many matches, After a few hrs of readin- im no closer to an answer.

Inspired by Shg, I decided to try my hand at a few basic macros.

One of my macros is a tied to a worksheet_change. The macro runs and gives me the result that i want, however excel flips to the sheet that the sub is running for.

example:

Sheet1, Change the cell
excel flips the visible to sheet2
macro runs

Its kinda of annoying. I would like excel to stay on sheet1 where i am trying to finish data entry. I suspect it is how i have writen the macro. Any help at all on how to clean it up.. or a better way to write the same thing would be very appreciated.


	VB:
	
 TermPlacement() 
     
     ' Term_Placement Macro
     ' Uses the Value of Term to insert a formula repeatedly in a colum.
     
    Dim sNFormula As String 
    Dim rTerm As Range 
    Dim rStart_Cell As Range 
    Dim rNpayment As Range 
     
     'The reference cell located directly above the first cell in column
    Set rStart_Cell = Worksheets("Floor Plan").Range("$B$13") 
     'Additional set values
    Set rNpayment = Range("Npayment") 
    Set rTerm = Range("Term") 
     
    sNFormula = "=IF(OFFSET($B$13,($D$6-1),6)

I almost don't know how to phrase the question, which I know will present a problem...

I'm working with a DDE feed of stock quotes (much like this thread: http://www.mrexcel.com/forum/showthread.php?t=322427)

Ideally, I'd like a row of information to be cut & pasted as the values into another worksheet IF an objective condition is hit.

A very simple example: Let's assume I've got the following data:

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA2=
ABCDEF1TimeStockBidΒ VolBidΒ PriceAskΒ PriceAskΒ Volume211:10:23MSFT3035.5535.5810311:10:23YHOO50029.8529.920Sheet3Β 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I'd like to be able to copy the entire line to a new worksheet IF the bid volume is above 400, every time it updates with new data.

Unfortunately, my VBA knowledge ends very shortly after recording a macro & making small tweaks, so I have no idea how I'd set something like this up. Does anybody have any suggestions for me?

Thanks a ton!

Hello all! My first post!

So I have designed an excel file for my job that does complex calculations based on the data that users fill into a number of cells.

What i would like to do is somehow set this up to happen automatically for a long list, rather than be manually input.

So, just as a super dumbed-down example:

Cell A2 = B2 + C2
User enters the data in B2 and C2. Lets say B2 = "Apples" and C2 = "Oranges"

Now, say I have another Excel sheet where Column A is a list of 100 "Apples" and Column B is a list of 100 "Oranges"

Is there a way I can get Excel to automatically, individually plug each Apples into Cell B2 and each Oranges into Cell C2, and then in Column C give me each value of Cell A2 after the calculation is performed?

Keep in mind, my example is SUPER dumbed down from the real thing. I would not be possible to just copy/paste the formula into each row.

I uploaded an example to better illustrate, as my description might be bad. So the worksheet "DATA" contains the list, and I want the data in each row to be individually plugged into the appropriate cells on the worksheet "CALCULATIONS" and then give me each result in Column C.

THANKS IN ADVANCE FOR ANY HELP!!!!!!!!!

Hello All!
I have been banging away at a variety of calendar methods that fit the landscape of my work-environment. Originally (as some of my previous posts make clear) I stabbed my information into an outlook calendar. I quickly maxed out the calendar's capacity.
I have now switched to a linear calendar in a spreadsheet
example:
SAT|SUN|MON|TUE|WED etc.
1|2|3|4|5 etc

My proof of concept (hard coded for Jan 1, 2011) works great! However, once the first month ends
I need to redo everything for the following month - i.e. have the macro do a second pass of all the info I just finished inputting for January.

So I grab my startday = datevalue(1/1/2011) and statically increment it to datevalue(2/1/2011) and redo everything.
I'm at a loss at figuring out how to do the loop properly. Statically setting the dates and doing a pass of all the same info for the additional 11 months makes the macro long and clunky.
Note that, for the purposes of building the macro, I am only dealing with 1 leave date.
There will be additional dates and colour codings added much later for different categories (training, night duty, travel etc etc etc) As well, the class CString was imported here for the use of Regular expressions.

I've pasted my code below (except for the feb pass - as it is pretty much the same as jan) any help or assistance or suggestions or perhaps a teleporter to another dimension where would immensily appreciated. (Heck, I don't even know if my title is properly worded!)

Thanks to all

-----


	VB:
	
 
 
Option Explicit 
Sub CalBeta1() 
    Dim MyInput As Variant 
    Dim diff As Long 
    Dim DayofWeek As Date 
    Dim finalday As Date 
    Dim startday As Date 
    Dim fooday As Date 
    Dim curyear As Date 
    Dim curmonth As Date 
    Dim mydays As Integer 
    Dim myspread As Integer 
    Dim my2spread As Integer 
    Dim r As Integer 
    Dim i As Long 
    Dim j As Long 
    Dim myleave1 As Integer 
    Dim f As Object 
    Dim update1 As Date 
    Dim update11 As Date 
    Dim leave1 As Integer 
    Dim ColNumb As Object, RowNumb As Long, MyCol As New CString, MyNewCol As New CString, MyNewXYCol As New CString 
    Dim XCol As Object 
    Dim ZCol As Object 
    Dim XYCol As New CString 
     'Dim AbDate As Date, LNum As Long
    Dim wsSheet1 As Worksheet 
    Dim wsSheet2 As Worksheet 
     
    Set wsSheet1 = Worksheets("LEAVE") 
    Set wsSheet2 = Worksheets("BETA_CAL") 
     
     ' Select Worksheet
    Worksheets("BETA_CAL").Select 
     ' Unprotect sheet if had previous calendar to prevent error.
    Worksheets("BETA_CAL").Protect DrawingObjects:=False, Contents:=False, _ 
    Scenarios:=False 
     ' Prevent screen flashing while drawing calendar.
    Application.ScreenUpdating = False 
     
     ' Set up error trapping.
     'On Error GoTo MyErrorTrap
     
     ' Clear area a1:af35 including any previous calendar.
    Worksheets("BETA_CAL").Range("a1:az35").Clear 
     
     ' Use InputBox to get desired month and year and set variable
     ' MyInput.
     'MyInput = InputBox("Type in Month and year for Calendar. [Format: January 2011] ")
     
     ' Allow user to end macro with Cancel in InputBox.
     'If MyInput = "" Then Exit Sub
     
     ' Get the date value of the beginning of inputted month.
     'startday = DateValue(MyInput)
    startday = DateValue("1/1/2011") ' Format: mm/dd/yy
     
     ' Check if valid date but not the first of the month
     ' -- if so, reset StartDay to first day of month.
     'If Day(startday)  1 Then
     '    startday = DateValue(Month(startday) & "/1/" & _
     '    Year(startday))
     'End If
     
     '''''''''''''''''''''''''''''''''''''''''
     ' BEGIN FORMATTING OF HEADER INFORMATION'
     '''''''''''''''''''''''''''''''''''''''''
     ' Prepare cell for Month and Year as fully spelled out.
    Worksheets("BETA_CAL").Range("a1").NumberFormat = "mmmm yyyy" 
     ' Center the title with appropriate formatting
    Worksheets("BETA_CAL").Range("A1:AF1").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Merge 
    Worksheets("BETA_CAL").Range("A1:AF1").Select 
    With Selection 
        .Value = "COMBINED SCHEDULES CALENDAR " & Year(startday) 
        .Font.FontStyle = "Arial" 
        .Font.Size = 10 
        .Font.Bold = True 
        .Font.Italic = True 
    End With 
     
     ' Prep next row to display month and year
    Worksheets("BETA_CAL").Range("A2:AF2").Select 
    With Selection 
        .HorizontalAlignment = xlCenter 
        .VerticalAlignment = xlBottom 
        .WrapText = False 
        .Orientation = 0 
        .AddIndent = False 
        .IndentLevel = 0 
        .ShrinkToFit = False 
        .ReadingOrder = xlContext 
        .MergeCells = False 
    End With 
    Selection.Merge 
    Worksheets("BETA_CAL").Range("A2:AF2").Select 
    With Selection 
        .NumberFormat = "@" 
         ' Put inputted month and year fully spelling out into "a2".
        Range("A2").Value = MonthName(Month(startday)) & Chr(32) & Year(startday) 
        .Font.FontStyle = "Arial" 
        .Font.Size = 10 
        .Font.Bold = True 
        .Font.Italic = True 
    End With 
     
     '''''''''''''''''''''''''''''''''
     ' BEGIN CALCULATION OF FIRST DAY'
     '''''''''''''''''''''''''''''''''
     ' Set variable and get which day of the week the month starts.
    DayofWeek = Weekday(startday) 
     ' Set variables to identify the year and month as separate
     ' variables.
    curyear = Year(startday) 
    curmonth = Month(startday) 
     
     ' Set variable and calculate the first day of the next month.
    finalday = DateSerial(curyear, curmonth + 1, 1) 
     
     ' Calculate how many days in the given month
    mydays = Day(DateSerial(Year(Date), curmonth + 1, 1) - 1) 
     
     ' Used to input data in the proper format.  I.E. if I select column C, I have to -1, if I select column D I have to -2
    myspread = mydays - 1 
    my2spread = mydays - 2 
     
     ' Place a "1" in cell position of the first day of the chosen
     ' month based on DayofWeek.
    Select Case DayofWeek 
    Case 1 
        Range("c3").Value = "Sunday" 
        Range("c4").Value = 1 
    Case 2 
        Range("c3").Value = "Monday" 
        Range("c4").Value = 1 
    Case 3 
        Range("c3").Value = "Tuesday" 
        Range("c4").Value = 1 
    Case 4 
        Range("c3").Value = "Wednesday" 
        Range("c4").Value = 1 
    Case 5 
        Range("c3").Value = "Thursday" 
        Range("c4").Value = 1 
    Case 6 
        Range("c3").Value = "Friday" 
        Range("c4").Value = 1 
    Case 7 
        Range("c3").Value = "Saturday" 
        Range("c4").Value = 1 
    End Select 
     ' Begin AutoFill days
    Worksheets("BETA_CAL").Range("c3").Select 
    Selection.AutoFill Destination:=Selection.Resize(1, mydays), Type:=xlFillDefault 
     
    With Range("d4").Select 
        Selection.Insert 
        Selection.Formula = "=(c4+1)" 
        Selection.AutoFill Destination:=Selection.Resize(1, myspread), Type:=xlFillDefault 
    End With 
     
     ''''''''''''''''''''''''''''''
     ' BEGIN POPULATING WITH DATA '
     ''''''''''''''''''''''''''''''
     ' Count Rows in Sheet Leave
    r = Sheets("LEAVE").Range("A" & Rows.Count).End(xlUp).Row 
     ' Select the proper worksheet in case of excel brainfart
    Worksheets("LEAVE").Select 
     ' Start the for loop in order to stab data in Calendar for leave 1
     ' We start at 2 because 1 is just header information
    For i = 2 To r 
        update1 = Worksheets("LEAVE").Cells(i, "H").Value 
        update11 = Worksheets("LEAVE").Cells(i, "I").Value 
        If update1 = "12:00:00 AM" Then 
             ' Copy and Paste Service Number and Last Name For those without a leave time
            Worksheets("BETA_CAL").Select 
            Worksheets("NominalRoll").Range("A" & i).Copy 
            ActiveSheet.Paste Destination:=Worksheets("BETA_CAL").Range("A" & i + 3) 
             
            Worksheets("NominalRoll").Range("B" & i).Copy 
            ActiveSheet.Paste Destination:=Worksheets("BETA_CAL").Range("B" & i + 3) 
            On Error Resume Next 
             
        Else 
             '''''''''''''''''''''''''''''''''''''''''''''''''''
             ' FIGURE OUT DAYS BETWEEN START AND END OF LEAVE 1'
             '''''''''''''''''''''''''''''''''''''''''''''''''''
             
             ' Using function testdates to pass data through datediff
            leave1 = TestDates(update1, update11) 
             
             ' Figure out the first day of leave as per calendar
            myleave1 = Day(update1) 
             
            With wsSheet2 
                Set ColNumb = .Rows(4).Find(What:=myleave1, LookIn:=xlValues, LookAt:=xlPart, _ 
                SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Columns 
                MyCol = ColNumb.Address 
                MyNewCol = MyCol.Remove("[^a-z A-Z]", RegularExpression).Reverse.UpCase.Inspect 
            End With 
             
             ''''''''''''''''''''''''''''''''
             ' BEGIN ADDING EMPLOYEE INFORMATION '
             ''''''''''''''''''''''''''''''''
             ' Copy and Paste Employee Number and Last Name
            Worksheets("BETA_CAL").Select 
            Worksheets("NominalRoll").Range("A" & i).Copy 
            ActiveSheet.Paste Destination:=Worksheets("BETA_CAL").Range("A" & i + 3) 
             
            Worksheets("NominalRoll").Range("B" & i).Copy 
            ActiveSheet.Paste Destination:=Worksheets("BETA_CAL").Range("B" & i + 3) 
             
            With Worksheets("BETA_CAL").Range(MyNewCol & i + 3).Select 
                Selection.Resize(1, leave1 + 1).Merge 
                Selection.Interior.ColorIndex = 6 
                Selection.Interior.Pattern = xlSolid 
                Selection.Interior.PatternColorIndex = xlAutomatic 
            End With 
        End If 
    Next i 
     
     ''''''''''''''''''''
     ' BEGIN NEXT MONTH '
     ''''''''''''''''''''
     
     
     ''''''''''''''''''''''''''''''''''''
     ' BEGIN END OF MACRO CLEANUP STUFF '
     ''''''''''''''''''''''''''''''''''''
     ' Turn on gridlines.
    ActiveWindow.DisplayGridlines = True 
     ' Protect sheet to prevent overwriting the dates.
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _ 
    Scenarios:=True 
     ' Resize window to show all of calendar (may have to be adjusted
     ' for video configuration).
    ActiveWindow.WindowState = xlMaximized 
    ActiveWindow.ScrollRow = 1 
     ' Allow screen to redraw with calendar showing.
    Application.ScreenUpdating = True 
     
     ' Prevent going to error trap unless error found by exiting Sub
     ' here.
     ' Exit Sub
     
     'MyErrorTrap:
     '       MsgBox "You may not have entered your Month and Year correctly." _
     '           & Chr(13) & "Spell the Month correctly" _
     '           & " (or use 3 letter abbreviation)" _
     '           & Chr(13) & "and 4 digits for the Year"
     '       MyInput = InputBox("Type in Month and year for Calendar. [Format: January 2011] ")
     '       If MyInput = "" Then Exit Sub
     '       Resume
End Sub 
Function TestDates(pDate1 As Date, pDate2 As Date) As Long 
    TestDates = DateDiff("d", pDate1, pDate2) 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


First off, I would like to say thank you to all of the people who actively use this board. As an excel and VB newbie, this site has been so valuable to me and I really appreciate it.

Onto my issue: I have a spreadsheet that gathers data from 5 other spreadsheets, compiles it all into one worksheet, and runs a couple formulas to check the current "status" of each row. Finally, the last module checks to see if a row is "complete" and if it is, it copies the data to another worksheet and then deletes the row.

All of the code I have in place works very well and very fast except for the last one, which looks for "completed" and if found deletes the entire row. For some reason, this code can take literally hours to run for something that's only 1,800 lines or so. I have tried to use xlCalculationManual and a couple other methods to try and improve the speed, but nothing has seemed to work.

Does anyone have any ideas on how I might be able to speed this process up? Here is the code in question:

Sub DelRows()
Dim delFactor As String
Dim rng As Range
delFactor = "COMPLETE"
ThisBook = "Name_of_workbook.xls"
Workbooks(ThisBook).Worksheets("Name_of_sheet").Select
Set rng = Range("M1:M" & Cells(Rows.Count, "M").End(xlUp).Row)
ActiveSheet.AutoFilterMode = False
With rng
.AutoFilter Field:=1, Criteria1:="=" & delFactor
.Offset(1, 0).EntireRow.Delete
.AutoFilter
End With
End Sub

I'd be very grateful if someone can help with this - tearing my hair out!

I have a charting application "pushing" data record by record (row by row) directly into an Excel worksheet. There are no DDE statements involved in Excel.

I need to get a snapshot of calculations at specific points in time - when a new record comes in. Unfortunately, Excel keeps on re-calculating everything from scratch each time a new record/row comes in, overwriting previous results.

When a row is filled (say columns A-H) then calculations will be done (in column I) based on calculation on this row and other data held in other worksheets. However, this calculation in I (or a copy of it in another column) must remain permanent after it has done this first calculation as data in the other worksheets will change thereafter.

I've now spent ages trying to get this up and running without success. I have tried using RECALL but am finding it really difficult to get the right coding - IF it can be used with DDE updates rather than manual updates.

Would be really grateful of a reply from someone.

Thanks again.

Richard

Recall summary from http://www.rhdatasolutions.com/morefunc/#RECALL

RECALL

Returns the former contents of the caller cell (its contents before the last calculation).

SYNTAX :
=RECALL(Static)

- Static (boolean, optional) : if TRUE, the function is static (non-volatile). Default : FALSE.

If the formula of the caller uses or refers to a cell which uses a volatile function (RAND, INDIRECT, OFFSET...), this argument should not be set to TRUE.

EXAMPLES :

=RECALL()+1 : counts the number of recalculations in Excel.

=IF(A1RECALL(),A1,RECALL()) : returns the higher number which the cell A1 has ever contained.

=IF(MAX(A1:B10)RECALL(),MAX(A1:B10),RECALL()) : returns the higher number which the range A1:B10 has contained.

=RECALL(TRUE)+A1 : returns a cumulative sum of all values which have appeared in the cell A1.

=IF(COUNTA(A1)+1,RECALL(TRUE)+1) : counts how many times the contents of A1 have changed.

=IF(COUNTA(A1:B10),RECALL(TRUE)+1,0) : counts how many times the contents of any cell in A1:B10 has changed, or returns 0 if this range has been cleared.

=RECALL(TRUE)+(A2500) : counts how many times the value of A2 has been greater than 500.

I'd be very grateful if someone can help with this - tearing my hair
out!

I have a charting application "pushing" data record by record (row by
row) directly into an Excel worksheet. There are no DDE statements
involved in Excel.

I need to get a snapshot of calculations at specific points in time -
when a new record comes in. Unfortunately, Excel keeps on
re-calculating everything from scratch each time a new record/row comes
in, overwriting previous results.

When a row is filled (say columns A-H) then calculations will be done
(in column I) based on calculation on this row and other data held in
other worksheets. However, this calculation in I (or a copy of it in
another column) must remain permanent after it has done this first
calculation as data in the other worksheets will change thereafter.

I've now spent ages trying to get this up and running without success.
I have tried using RECALL but am finding it really difficult to get the
right coding - IF it can be used with DDE updates rather than manual
updates.

Would be really grateful of a reply from someone.

Thanks again.

Richard

Recall summary from http://www.rhdatasolutions.com/morefunc/#RECALL

RECALL

Returns the former contents of the caller cell (its contents before the
last calculation).

SYNTAX :
=RECALL(Static)

- Static (boolean, optional) : if TRUE, the function is static
(non-volatile). Default : FALSE.

If the formula of the caller uses or refers to a cell which uses a
volatile function (RAND, INDIRECT, OFFSET...), this argument should not
be set to TRUE.

EXAMPLES :

=RECALL()+1 : counts the number of recalculations in Excel.

=IF(A1RECALL(),A1,RECALL()) : returns the higher number which the cell
A1 has ever contained.

=IF(MAX(A1:B10)RECALL(),MAX(A1:B10),RECALL()) : returns the higher
number which the range A1:B10 has contained.

=RECALL(TRUE)+A1 : returns a cumulative sum of all values which have
appeared in the cell A1.

=IF(COUNTA(A1)+1,RECALL(TRUE)+1) : counts how many times the contents
of A1 have changed.

=IF(COUNTA(A1:B10),RECALL(TRUE)+1,0) : counts how many times the
contents of any cell in A1:B10 has changed, or returns 0 if this range
has been cleared.

=RECALL(TRUE)+(A2500) : counts how many times the value of A2 has been
greater than 500.

--
rhay999

Hello,
I am new to VBA and have been trying in the past few days to pull data from specific cell from 52 xls files that are located in a specific folder. And for some reason I am coming up with "Files not Found".

Background Info. I belong to another forum http://www.theswamp.org that is for Autocad programing and whatnot. A couple people have been helping me (more like them) write this code. They can make in work on the machines (3 total) with my files. I can not on mine. There is something stupid that is stopping the macro but we can not figure it out. One of them suggest I come to you people to see if you can figure it out.

I know there have been alot of post here as to how to pull the data but none that I can tell me how to trouble shoot it.

one version we tried but this does nothing
'
' Test Macro
' Macro recorded 12/12/2006 by
'

'
    Dim fs As FileSearch
    Dim i As Integer
    Dim j As Integer
    
    
    Set fs = Application.FileSearch
    
    With fs
        .LookIn = "C:Documents and SettingsTed KrushMy DocumentsTIME SHEETS"
        .Filename = "*.xls"
        .Execute
        j = 2
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
    
            Workbooks.Open Filename:=.FoundFiles(i)
            Range("L39").Select
            Selection.Copy
            ActiveWindow.Close
            Windows("Gather.xls").Activate
                Range("A" & j).Select
                ActiveSheet.Paste
            
            j = j + 1
        
        Next i
    End With

Second version which returns "no files found"
Option Explicit

Public Const AppName = "Excel Data Compiler"
Public Const AppVer = "[Ver. 1.0]"

Public tmpPath As String
Sub ListFilesMessageBox()
    Dim fs As FileSearch
    Dim i As Integer
    
    Set fs = Application.FileSearch
    
    With fs
        .LookIn = "C:junk"
        .Filename = "*.xls"
        .Execute
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    End With
End Sub
Public Sub Setup()
    On Error GoTo ErrHandler
    
    tmpPath = "C:junk"
    
    Sheets.Add Type:="Worksheet"
    With ActiveSheet
        .Move After:=Worksheets(Worksheets.Count)
        .Name = "Totals"
    End With
    ActiveWindow.DisplayGridlines = False
    
    ListFiles
    
    Exit Sub
ErrHandler:
    MsgBox "An error has occurred!" & vbCrLf & "Location: modSetup.Setup", vbCritical + vbOKOnly,
AppName & "  " & AppVer
End Sub
    
Sub ListFiles()
    Dim fs As FileSearch
    Dim i As Integer
    Dim bk As Workbook
    Dim strFileName As String
    Dim bkSource As Workbook
    Dim sourceRange As Range
    Dim destrange As Range
    
    Set fs = Application.FileSearch
    Set bkSource = ThisWorkbook
    
    With fs
        .LookIn = tmpPath
        .Filename = "*.xls"
        
        If .Execute > 0 Then
        
            For i = 1 To .FoundFiles.Count
                Debug.Print .FoundFiles(i)
                
                Set bk = Workbooks.Open(.FoundFiles(i))
                bk.Activate
                
                Set sourceRange = bk.Worksheets("Sheet1").Range("A1")
                Set destrange = bkSource.Worksheets("Totals").Range("A" & i)
                sourceRange.Copy destrange
                
                Set sourceRange = bk.Worksheets("Sheet1").Range("A2")
                Set destrange = bkSource.Worksheets("Totals").Range("B" & i)
                sourceRange.Copy destrange
                
                bk.Close False
            Next i
            Range("A" & i + 1).Select
            ActiveCell.Formula = "=SUM(A1:A" & i & ")"
            
            Range("B" & i + 1).Select
            ActiveCell.Formula = "=SUM(B1:B" & i & ")"
            
        Else
            MsgBox "There were no files found."
        End If
    End With
End Sub


Hello everyone:

I am trying to create a spreadsheet to calculate thermodynamic
properties of a steam turbine and am having a number of problems with
Excel. I have been making changes to it over the last couple of months
and gotten myself into a bind. Just when I think I have everything
nailed down and working.... something else craps out. I will try to
explain, as best I can, what has happened (or not happened) in the last
week.

I have a spreadsheet, you can download it from here
http://www.csupomona.edu/~cthompson1...chores-BAD.xls
that keeps aborting with an invalid page fault.

I have been saving backup copies for each of my major changes and
think I have located the problem here between these two backup
versions:
http://www.csupomona.edu/~cthompson1...ies-bu-008.xls
and
http://www.csupomona.edu/~cthompson1...ies-bu-009.xls
.. They both have calculations under the toolbar->options set to
automatic and the iterations checked. The "009" spreadsheet immediately
starts executing the spreadsheet formulas as soon as it is opened, the
"008" version does not. Apparently I didn't notice this until around
version "010" when I started making changes to the debug routines in my
code.

The first thing I did was to add code like the following to identify
the calling cell that caused my visual basic functions to fail:

Public Function TempDPW(Density, Pressure, Optional Guess, Optional
Precision, Optional iterations)

Dim myName As String
Dim myCell As Range
Dim mySheet As Worksheet
Dim myBook As Workbook
Dim aName As Name
Dim CellName As String

If IsError(Density) Or IsError(Pressure) Or IsEmpty(Density) Or
IsEmpty(Pressure) Or Density <= 0 Or Pressure <= 0 Then Exit Function

On Error Resume Next

myName = "TempDPW"

If TypeName(Application.Caller) = "Range" Then
Set myCell = Application.Caller
Set mySheet = myCell.Worksheet
Set myBook = mySheet.Parent
Err.Number = 0
For Each Name In myCell
Set aName = myCell.Name
If Err.Number = 0 Then
CellName = aName.Name
Else
CellName = "#N/A"
End If
Err.Number = 0
Next Name
End If

On Error GoTo Error_routine

....

TempDPW = T

Exit Function

Error_routine:
Debug.Print myName, "Density=", Density, "Pressure=", Pressure
If (TypeName(myCell) = "Range") Then Debug.Print myName, "Sheet=",
mySheet.Name, "Name=", CellName, "Row=", myCell.Row, "Col=",
myCell.Column, "Address=", myCell.Address
Debug.Print myName, "Error Source=", Err.Source, "Num=", Err.Number,
"Line=", lnum, "Desc=", Err.Description
Stop
Resume Next

End Function

After these changes I discovered that I could stop the spreadsheet
("009") from calculating on startup if I set Application.Calculation =
xlCalculationManual before I save it to disc. So I set up a button on
my standard tool bar to switch between automatic and manual, and added
code to turn off the automatic calculation "Before_Save" in my
workbook. The code follows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next

Application.CommandBars("Standard").Controls("Calculation Mode").Delete

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

On Error Resume Next

Me.Application.CalculateBeforeSave = False
Me.Application.Calculation = xlCalculationManual
With Me.Application.CommandBars("Standard").Controls("Calculation
Mode")
.State = msoButtonDown
.TooltipText = "Calculation mode is manual"
End With

End Sub

Private Sub Workbook_Open()

Dim cmd As CommandBarControl

On Error Resume Next

'Debug.Print "Workbook_open is here!"

initialize

With Application.CommandBars("Standard")

Err.Clear

Set cmd = .Controls("Calculation Mode")
If Err.Number <> 0 Then Set cmd = .Controls.Add

With cmd
If Application.Calculation = xlCalculationAutomatic Then
.State = msoButtonUp
.TooltipText = "Calculation mode is Automatic"
Else
.State = msoButtonDown
.TooltipText = "Calculation mode is manual"
End If
.BeginGroup = True
.Caption = "Calculation Mode"
.OnAction = "CalcMode"
.FaceId = 2
End With
End With

'Debug.Print "Workbook_Open Is not here!"

End Sub

The way the spreadsheet works in the "008" version it this.
Immediately after the workbook is opened, I run the macro "initialize"
to initialize the water module constants and arrays. Then I can make
changes to the spreadsheet, and run the macro "AllGoalSeek" to update
the cells and do the spreadsheet calculations. It seems like the "F9"
button does nothing here, but <ctl-alt-F9> recalculates all of the
cells.

In the "009" version I was stumped when I opened the workbook and
started running into my debug code "Stop" statements. I finally
realized that none of the constants or arrays had been initialized, and
I started working the problem of turning off the calculation mode at
startup. The first thing I did here was to install "Workbook_Open" code
to execute the "Initialize" subroutine and set the module constants and
arrays. This code was unfortunately never executed and i have no idea
why. I put a "stop" statement on the first line of "Workbook_Open" and
discovered to my horror that it never popped up. So then I added an
"auto_open" macro to my water module with the initialization code
inside, only to discover to my further horror that this was never
executed until AFTER ALL of the cells had calculated their functions in
ERROR with the module UN-initialized. I think "F9" works ok here, but
<Ctl-alt-F9> does nothing. I don't get it!

Where I am at now is this: The "BAD" spreadsheet kinda works and
kinda doesn't work. Before this one I added some new routines
"EnthalpySPW, TempSPW, TempHPW" which do the same thing as GoalSeek in
a function. These seemed to be working ok for the most part until I
found out that Excel sent parameters in error. I added checks to exit
the functions when this occured and thought that all of my problems
were solved and got rid of my "AllGoalSeek" macro. Then I added code
"TempDPW" and screwed everything up. Somehow when I did a search and
replace on temperature and density, and I inadvertantly changed
everything in the module instead of just the highlighted code in the
TempDPW function. So I deleted the module and brought in a fresh "OLD"
copy from the previous spreadsheet. The problem still seems to exist
where a page fault can occur anywhere at anytime... Usually before I
have a chance to save my changes.

What I have noticed today is that my "Calculation Mode" button
doesn't appear to be working. I was pressing the button on the
spreadsheet and noticed that it was NOT changing state. It always
stayed down in "Manual" mode even if the Toolbar->Options->Calculation
said that the spreadsheet was in "Automatic" mode. When I put in a
break into my CalcMode macro to see what was going on, I found that it
got to the line Application.Calculation = xlCalculationAutomatic and as
soon as the line executed, the spreadsheet started calculating cells.
This time though the constants and arrays are initialized and so
everything should run ok. Except that somewhere in the spreadsheet an
EnthalpyW function is called with the temperature in an "ERROR 1021"
state or something. The function sees the error and exits the function
with the "Exit Function" statement and that's it. Nothing else happens
and the code never returns to the CalcMode macro to finish with the
button setup.

If anybody has any ideas how to do a better job with this thing
please post to this group. I am at my wits end on this one.

P.S. Is there any way to tell Excel what order I want it to executed
cells in? Sometimes it looks like it is calculating everything, other
times only a couple of cells. With Iterations turned on, I see a kind
of ripple effect through the cells. The wierd part is when it stops and
in cells where "if statements" check conditions, the results of the "if
statements" don't match the conditions reported in the spreadsheet. Is
there some way I can tell Excel to do everything from this cell to
another cell just one time?

Oh yeah. The other thing that is bugging me is when I open the
spreadsheet and it ask's me if I want to "Enable" or "Disable" macros
and I say "Disable," the next thing I get is a dialog about "Excel type
4.0 macros." If I say "no" the workbook doesn't open, and if I say
"yes" it does. As far as I know I only have Visual Basic type macros.
Are these the "Type 4.0" macros, or do I have a virus or something that
I am unaware of?

Regards from,
Chris Thompson

User Interface
New User Interface
The new results-oriented user interface makes it easy for you to work in Microsoft Office Excel. Commands and features that were often buried in complex menus and toolbars are now easier to find on task-oriented tabs that contain logical groups of commands and features. Many dialog boxes are replaced with dropdown galleries that display the available options, and descriptive tooltips or sample previews are provided to help you choose the right option.
No matter what activity you are performing in the new user interface, whether its formatting or analyzing data, Excel presents the tools that are most useful to successfully complete that task.
Introducing the new interface
There is a new look for Office Excel 2007, a new user interface (UI) that replaces menus, toolbars, and most of the task panes from previous versions of Excel with a single mechanism that is simple and apparent. The new user interface is designed to help you be more productive in Excel, more easily find the right features for various tasks, discover new functionality, and be more efficient.
The New Interface commands hierarchy:
1. Menu Tabs.
2. Ribbons.
3. Groups within each tab break a task into subtasks.
4. Command buttons (icons) in each group carry out a command or display a menu of commands.
Ribbon user interface: The primary replacement for menus and toolbars in Office Excel 2007 is the Ribbon. Designed for easy browsing, the Ribbon consists of tabs that are organized around specific scenarios or objects. The controls on each tab are further organized into several groups. The Ribbon can host richer content than menus and toolbars can, including buttons, galleries, and dialog box content.
Tabs that appear only when you need them: In addition to the standard set of tabs that you see on the Ribbon whenever you start Office Excel 2007, there are two other kinds of tabs, which appear in the interface and are useful for the type of task you are currently performing.
Contextual tools: Contextual tools enable you to work with an object that you select on the page, such as a table, a picture, or a drawing. When you click the object, the pertinent set of contextual tabs appears in an accent color next to the standard tabs.
Program tabs: Program tabs replace the standard set of tabs when you switch to certain authoring modes or views, including Print Preview.
File Button : This button is located in the upper-left corner of the Excel window and opens the menu shown here:

Quick Access Toolbar: The Quick Access Toolbar is located by default at the top of the Excel window and provides quick access to tools that you use frequently. You can customize the Quick Access Toolbar by adding commands to it.
Adding Commands to Quick Access Toolbar: In the Customize Quick Access Toolbar box, select either For all documents (as a default) or a specific document.
Click the command that you want to add, and then click Add.

Dialog Box Launchers: Dialog Box Launchers are small icons that appear in some groups. Clicking a Dialog Box Launcher opens a related dialog box or a task pane, providing more options related to that group.

Use the Keyboard to Access any Commands in the Ribbon
To use keyboard shortcut: To open a menu tab, press the Alt tab, now press a letter(s) or a number or a combination of a letter & a number , see below:
Step 1: press the Alt key or F10.
Step 2:
Press H, and then a letter(s) or a number or a combination of both (a letter & a number).
Or
Use the Tab key to move between command buttons in the Ribbon.
Memory management, Workbook, Worksheet & Cells
Memory Management
Memory management has been increased from 1 GB of memory in Microsoft Office Excel 2003 to 2 GB in Office Excel 2007.
You will also experience faster calculations in large, formula-intensive worksheets because Office Excel 2007 supports dual-processors and multithreaded chipsets.
Numbers of Rows, Columns & Cells in a Worksheet
Excel 2007 sheet contains 1,048,576 rows by 16,384 columns, total of 17,180,033,024 cells compare to previous Excel versions which hold 65,536 rows by 256 columns, total of 16,777,216 cells.
New file formats
XML-based file format: In 2007 Microsoft Office system, Microsoft is introducing new files formats for Word, Excel, and PowerPoint, known as the Microsoft Office Open XML formats. These new file formats facilitate integration with external data sources, and also offer reduced file sizes and improved data recovery. In Excel 2007, the default format for an Excel workbook is the Office Excel 2007 XML-based file format (.xlsx). Other available XML-based formats are the Excel 2007 XML-based and macro-enabled file format (.xlsm), the Excel 2007 file format for an Excel template (.xltx), and the Excel 2007 macro-enabled file format for an Excel template (.xltm).
Themes, Colors & Formatting
Office themes
In Office Excel 2007, you can quickly format the data in your worksheet by applying a theme and by using a specific style. Themes can be shared across other 2007 Office release applications, such as Microsoft Office Word and Microsoft Office PowerPoint, while styles are designed to change the format of Excel-specific items, such as Excel tables, charts, PivotTables, shapes, or diagrams.
Number of Colors
Excel 2007 supports up to 16 million colors.
Rich conditional formatting
You can implement and manage multiple Conditional Formatting rules that apply rich visual formatting in the form of gradient colors, data bars, and icon sets to data that meets those rules. Conditional formats are also easy to apply in just a few clicks, you can see relationships in your data that you can use for your analysis purposes.
Formulas & Functions
Easy formula writing
Resizable formula bar: The formula bar automatically resizes to accommodate long, complex formulas, which prevents the formulas from covering other data in your worksheet. You can also write longer formulas with more levels of nesting than you could in earlier versions of Excel.
Function AutoComplete: With Function AutoComplete, you can quickly write the proper formula syntax. From easily detecting the functions that you want to use, to getting help completing the formula arguments, you will be able to get formulas right the first time and every time.
Easy access to Named ranges: By using Name manager, you can organize, update, and manage multiple Named ranges in a central location, which helps all users who need to work on your worksheet interpret its formulas and data.
New Functions
Very important and useful functions are added to Excel 2007. The functions are IFERROR, AVERAGEIF, AVERAGEIFS, SUMIFS and COUNTIFS. Read more and see example in Chapter 9, page 155.
New OLAP formulas and cube functions
When you work with multidimensional databases (such as SQL Server Analysis Services) in Excel 2007, you can use OLAP formulas to build complex, free form, OLAP data bound reports. New cube functions are used to extract OLAP data (sets and values) from Analysis Services and display it in a cell. OLAP formulas can be generated when you convert PivotTable formulas to cell formulas or when you use AutoComplete for cube function arguments when you type formulas.
Charts
A New look of charts
You can use new charting tools to easily create professional-looking charts that communicate information effectively. Based on the theme that is applied to your workbook, the new, up-to-date look for charts includes special effects, such as 3-D, transparency, and soft shadows.
The new user interface makes it easy to explore the available chart types so that you can create the right chart for your data. Numerous predefined chart styles and layouts are provided so that you can quickly apply a good-looking format and include the details that you want in your chart.
Visual chart element pickers: Beside the quick layouts and quick formats, you can now use the new user interface to quickly change any element of the chart to best present your data. In a few clicks, you can add or remove titles, legends, data labels, trendlines, and other chart elements.
A modern look with OfficeArt: Since charts in Excel 2007 are drawn with OfficeArt, almost everything you can do to an OfficeArt shape can also be done to a chart and its elements. For example, you can add a soft shadow or a bevel effect to make an element to stand out or use transparency to make elements visible that are partially hidden in a chart layout. You can also use realistic 3-D effects.
Clear lines and fonts: Lines in charts appear less jagged, and ClearType fonts are used for text to improve readability.
More colors than ever: You can easily choose from the predefined theme colors and vary their color intensity. For more control, you can also add your own colors by choosing from 16 million colors in the Colors dialog box.
Chart templates: Saving your favorite charts as a chart template is much easier in the new user interface.
Shared charting
Using Excel charts in other applications: In Excel 2007, charting is shared between Excel, Word, and PowerPoint. Rather than using the charting features that are provided by Microsoft Graph, Word and PowerPoint now incorporate the powerful charting features of Excel. Since an Excel worksheet is used as the chart data sheet for Word and PowerPoint charts, shared charting provides the rich functionality of Excel, including the use of formulas, filtering, sorting, and the ability to link a chart to external data sources, such as Microsoft SQL Server and Analysis Services (OLAP), for up-to-date information in your chart. The Excel worksheet that contains the data of your chart can be stored in your Word document or PowerPoint presentation, or in a separate file to reduce the size of your documents.
Copying charts to other applications: Charts can be easily copied and pasted between documents or from one application to another. When you copy a chart from Excel to Word or PowerPoint, it automatically changes to match the Word document or PowerPoint presentation, but you can also retain the Excel chart format. The Excel worksheet data can be embedded in the Word document or PowerPoint presentation, but you can also leave it in the Excel source file.
Animating charts in PowerPoint: In PowerPoint, you can more easily use animation to emphasize data in an Excel-based chart. You can animate the entire chart or the legend entry and axis labels. In a column chart, you can even animate individual columns to better illustrate a specific point. Animation features are easier to find and give you much more control. For example, you can make changes to individual animation steps, and use more animation effects.
Sorting, Filtering & Tables
Improved sorting and filtering
You can now sort data by color and by more than 3 (and up to 64) levels. You can also filter data by color or by dates, display more than 1000 items in the AutoFilter dropdown list, select multiple items to filter, and filter data in PivotTables.
Excel table enhancements
You can use the new user interface to quickly create, format, and expand an Excel table (known as an Excel list in Excel 2003) to organize the data on your worksheet so that its much easier to work with.
PivotTables
Easy-to-use PivotTables
By using the new PivotTable user interface, the information that you want to view about your data is just a few clicks away. You no longer have to drag data to drop zones that arent always an easy target. Instead, you can simply select the fields that you want to see in a new PivotTable field list.
After you create a PivotTable, you can take advantage of many other new or improved features to summarize, analyze, and format your PivotTable data.
Sharing & Connections
New ways to share your work
Using Excel Services to share your work: If you have access to Excel Services, you can use it to share your Office Excel 2007 worksheet data with other users, such as executives and other stakeholders in your organization. In Excel 2007, you can save a workbook to Excel Services and specify the worksheet data that you want other people to see. In a browser (browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Microsoft Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), they can then use Microsoft Office Excel Web Access to view, analyze, print, and extract this worksheet data. They can also create a static snapshot of the data at regular intervals or on demand. Excel Web Access makes it easy to perform activities, such as scrolling, filtering, sorting, viewing charts, and using drill-down in PivotTables. You can also connect the Excel Web Access Web Part to other Web Parts to display data in alternative ways. And with the right permissions, Excel Web Access users can open a workbook in Excel 2007 so that they can use the full power of Excel to analyze and work with the data on their own computers if they have Excel installed.
Using this method to share your work ensures that other users have access to one version of the data in one location, which you can keep current with the latest details. If you need other users, such as team members, to supply you with comments and updated information, you may want to share a workbook the same way.
Quick connections to external data
You no longer need to know the server or database names of corporate data sources. Instead, you can use Quick Launch to select from a list of data sources that your administrator or workgroup expert has made available for you. A connection manager in Excel allows you to view all connections in a workbook and make it easier to reuse a connection or to substitute a connection with another user.
Printing
Better printing experience
Page Layout View: In addition to the Normal view and Page Break Preview view, Excel 2007 provides a Page Layout View. You can use this view to create a worksheet while keeping an eye on how it will look in printed format. In this view, you can work with page headers, footers, and margin settings right in the worksheet, and place objects, such as charts or shapes, exactly where you want them to be. You also have easy access to all page setup options on the Page Layout tab in the new user interface so that you can quickly specify options, such as page orientation. Its easy to see what will be printed on every page, which will help you avoid multiple printing attempts and truncated data in printouts.
Saving to PDF and XPS format: Like other 2007 Office release applications, Excel 2007 supports saving a workbook to a high-fidelity fixed file format, such as Portable Document Format (PDF) or XML Paper Specification (XPS) format, that encapsulates how it will look when it is printed. This allows you to share the content of your workbook in a format that is easy for other people to view online or print, without including the underlying formulas, external data queries, or comments.
Actually, this is what you did in earlier versions of Excel to collect the information you need before you save it to Excel Services.
Using Document Management Server: Excel Services can be integrated with Document Management Server to create a validation process around new Excel reports and workbook calculation workflow actions, such as a cell-based notification or a workflow process based on a complex Excel calculation. You can also use Document Management Server to schedule nightly recalculation of a complex workbook model.

Hey eveyone,

I have a problem, I dont know where to begin and was looking for some
direction.
Here is what my company has asked me to do.

I need to create a method for printing off Pallet Control Sheets (PCS) which
go on our pallets of product, that is tamper-proof and easy to understand.

Here is an example of what a PCS looks like:

stock
code: xxxx-xxx-xx
Brand Name of Product
Organic SALTED Smooth
Peanut Butter Size
Date Code(s) _______ # of Cases: _________
_______ _________
Skid Number: 15
D.O.M:

Now what I would like is for the guys in the plant, who are very computer
illiterate, to be able to open up some sort of program/form/sheet thing which
would then ask them these questions:

Enter the stock code: ( which is 9 digits ) eg. XXXX-XXX-XX
How many skids are there? eg.15
Starting number? eg 3

When they enter the data I want it to generate a form that looks like the
Pallet Control Sheet.

Starting with the first question. When they type in the 9 digit stock code,
I would like it to bring up the 3 lines on the top of the Pallet Control
Sheet which is specific to each product we make. If possible can the 3 lines
printed off be different Fonts/font sizes?

Then I want it to print off the correct number Pallet control sheets due to
number of skids they enter and which number they are starting at. I want 2
copies for every skid number
eg. if its 15 skids of product with starting number 3 then it would print
off 30 PCS with sequencial numbering from 3 to 18. The first two sheets
would say skid 3, then the next two would say skid 4, etc....

Also, if possible, I would also like in the top right hand corner of the
Pallet Control Sheet to print off the stock code in small font.

This is the problem that has been given to me. I like how I always get
chosen for things that are just out of my ability levels, and hence my need
for help.

Does anyone have any idea how I would go about solving/creating this. What
do I need to use, Excel? Word? Access? a combination? I have basic knowledge
on all 3 programs.

If someone could just give me a general outline as to how they would
accomplish this it would be greatly appreciated.

Thank you very much for your time,

Andrew

"JLatham" wrote:

> Excel will work fine for this, my other choice would be Access.
>
> Your starting point is going to be getting all of those 9-digit stock codes
> entered into a worksheet. Next to those stock codes, on the same row you
> want to enter the 3 items of information associated with each one; one per
> cell. The stock codes need to be in the left most column of the group of 4.
> The obvious solution is to put stock codes in column A, then the other 3
> related entries in B, C and D. This will allow you to use a VLOOKUP()
> formula on the sheet you will design to be your PCS.
>
> Yes, Excel allows you to set different font styles and sizes in different
> cells on the same sheet.
>
> You then design another sheet to be the PCS that will be printed out. Make
> it look the way you want it to, and set the PrintArea property for it to keep
> from printing anything other than it that may be on the sheet.
>
> Then what you're (probably) goin to have is a 'button' on that form that
> gives a clue like "Start New PCS - Click Here". You can even set its
> properties so that even though it's on that sheet, it doesn't show up on the
> printouts.
>
> Clicking the button could bring up a userform (created inside of the VB
> Editor) that would have input areas for the necessary information (stock #,
> Skid #, # of skids) and validate them before continuing. Code would then
> simply transfer the stock number to the PCS sheet which would cause the
> VLOOKUP() formulas to get the 3 related items and put them where they need to
> be on each one, and then it would use the starting skid number and # of
> skids/palettes to go through a double-loop to print out the copies.
>
> Similar to that, but without the userform: have a separate sheet that has
> input instructions for the information they need to enter and a button that
> says "click here to transfer what you typed over to the PCS and print them"
> (well, you can probably say it quicker/shorter). They'd enter the
> information into appropriate cells on that sheet, and click the button and
> pretty much the same process would take place, although the stock code cell
> on the PCS sheet could be linked to the cell they type it into.
>
> Come to think about it - you'd only need one sheet for the data entry/PCS
> form part of this: some few rows at the top to give instructions and accept
> input into the cells, then down below that would be the actual PCS form, and
> it would be filling up with info as they type it in in that area. By using
> worksheet protection, locked cells and such, you could do it without risk of
> them screwing up the form. By setting the PrintArea for the sheet, when the
> 'print it' button was clicked, only the PCS would be printed, not the extra
> stuff on the sheet where they do the data entry.

Meader wrote:

Ok Im stuck again....

I have a question regarding how to get a button on my excel sheet to print
off a certain number of sheets in sequence.

I have the two following lines:

Enter the starting skid #: eg.5
Enter the number of skids : eg 14

And what I would like is for when someone clicks on the button, it will
print off 14 sheets starting from skid number 5, then 6 then 7 etc.

But I would also need 2 copies of each sheet. (2 of 5, 2 of 6...)

My questions are as follows:

What do i need to type in Visual Basic editor to get this to work?

How do I get it to change the number it would print off, like if it says
skid # 5 on the first sheet, how would I get it to change to skid # 6 for the
next?

Hi all,

I'm new to these forums, though not new to excel nor vb scripting as I mainly use access. However, I am having an issue that I'm sure someone else has encountered and found a solution to.

Just a little background so it makes sense. this is not using forms, but is used as a Q&A sheet. I use fields A4 through I4 down to row 23 for a standard 20 question set, A=question #, B=question, c=response, d=follow-up and so on and so forth through I. From time to time people can add a few more questions down the line and thats fine. I currently have a macro that says as long as A & B have data in it copy ALL of the data from A4 through I# of last row and paste that data into another worksheet. This part works out fine, I havent had any issues whatsoever. My problem is on clearing out any data on the 1st sheet that is below the standard questions.

For instance, row 24 has data filled in or A,B,C,H,I, but none of the letters in between. So if i use the
 it stops at C and doesnt move on to the end.  I do not want to use the entirerow function as there are 2 cells that have
dropdown formulas in them that just need to be cleared out and not deleted.

Below was the code I tried to use and obviously doesnt work or I wouldnt be posting!

now go back to Report worksheet and purge any data for
questions 1-20 and everything after 20
    Sheets("Report").Select
    Range("C4:I4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A24").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
the C4:I4 and xldown work usually, but even then due to blanks i get random stops. I need to know how to bypass the blank spaces to do something similar to what is above.

This might not make a ton of sense, i do not explain excel very well!

Please ask questions!

Thanks

**See end of post for solution**

Hello all,

I have a macro that opens excel files and then loops through to copy/paste data to a worksheet called "formdata" (it copies column to column)

Now I have created a series of formulas that allow me to build a table for a set of data (a column of data) belonging to one worksheet. However since I will be opening an arbitrary number of worksheets (could be 1,2,x) I want to build as many tables as the files I open and I want them spaced equally between them.

Here is what I have put together so far:
Private Sub CreateStatsTables()

'Create the statistics tables macro. Created by Kostas02 04 2008
'
Application.ScreenUpdating = False

'Creating the table for Formdata Column A

ThisWorkbook.Worksheets("STATISTICS").Activate

' Clear the area where I will put the table
Range("C13:E32").Select
Selection.ClearContents


    Range("C13").Select
    Range(Selection, "C32").Select

' Here I select the data I have copy/pasted already through another formula and apply a frequency formula on them as well as
some accumulation and percentages on seperate columns. The source data column is on a worksheet called Formdata. The
destination worksheet for all tables is called Statistics.

    Selection.FormulaArray = _
        "=FREQUENCY(Formdata!C[-2],STATISTICS!RC[-1]:R[19]C[-1])"
    Range("D13").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Range("D14").Select
    ActiveCell.FormulaR1C1 = "=R[-1]C+RC[-1]"
    Range("D14").Select
    Selection.AutoFill Destination:=Range("D14:D32"), Type:=xlFillDefault
    Range("D14:D32").Select
    Range("E13").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(RC[-2]:R[19]C[-2])"
    Range("E13").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/SUM(R13C3:R32C3)"
    Range("E13").Select
    Selection.AutoFill Destination:=Range("E13:E32"), Type:=xlFillDefault
    Range("E13:E32").Select
    Range("A1").Select

' Graphs: Here I put some borders/formatting around the table, this is not necessary but it would be useful.

Range("A12:E32").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A12:E12").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
Summing up:
What I am looking forward to achieve are 2 things.
a) have this procedure done automatically for all the number of columns available in the worksheet called "formdata"
b) space all tables equally between them (they are all of a certain size, 21 rows and 5 columns)
c) The formula only fills in 3 columns while the rest 2 are just text. How can I automate the text input (which is standard again) in the formula?

So far I have hardcoded the process for 10 tables but I need to limit it to however many I open each time. I think that I should do the loop some short of (I will try to explain the structure):
For each Column in Formdata
range set_number_of_rows_under_last_row_with_data
run formula
end with
Is this the correct idea?

All ideas welcome!

Cheers,

kostas

---Solution---

many thanks to Rylo for coming up with the following code

Sub CreateTables()
'create the base output tables
  Sheets("Statistics").Activate
  Range("B12:E12").Value = Array("Periods", "Number of Incidents", "Accumulative",
"Percentage")
  Range("B12:E12").Font.Bold = True
  Range("A13:A32").Value = "Delay up to"
  Range("A34").Value = "Total number of incidents:"
  Range("B34").Formula = "=SUM(C13:C32)"
  Range("A34:B34").Font.Bold = True
  Range("B13").Value = "12:01:00 AM"
  Range("B14").Formula = "=b13+timevalue(""00:01:00"")"
  Range("B14").AutoFill Destination:=Range("B14:B32")
  Range("B13:B32").Value = Range("B13:B32").Value
  Range("C13:C32").FormulaArray = "=FREQUENCY(Formdata!$A:$A,STATISTICS!B13:B32)"
  
  Range("D13").Formula = "=C13"
  Range("D14").Formula = "=D13+C14"
  Range("D14").AutoFill Destination:=Range("D14:D32")
  Range("E13").Formula = "=D13/SUM(C$13:C$32)"
  Range("E13").AutoFill Destination:=Range("E13:E32")
  Range("E13:E32").NumberFormat = "0.00%"
  CreateBorders

  Range("A12:E34").Copy Destination:=Range("G12")
  Range("I13:I32").Replace what:="formdata", replacement:="Formdata2"
'copy the base tables to cover the number of output column instances
  With Sheets("Formdata") 'determine the last column of data in formdata
    lastcol = .Cells.Find(what:="*", after:=.Range("A1"), searchdirection:=xlPrevious,
searchorder:=xlByColumns).Column
  End With
  
  For i = 1 To lastcol - 1 'the first column of data is covered by the initial table input
    Range("A12:K34").Copy Destination:=Cells(12 + i * 26, "A")
    newcol = WorksheetFunction.Substitute(Cells(1, i + 1).Address, "$1", "")
    Cells(12 + i * 26, "A").Resize(21, 15).Replace what:="$A:$A", replacement:=newcol & ":"
& newcol
    
  Next i
  
  Range("A1").Select
End Sub



Sub CreateBorders()
    Range("A12:E32").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A12:E12").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

End Sub
You will find the necessary workbook attached in one of the following posts.

Regards,

kostas


No luck finding an answer? You could always try Google.