Saving To Seperate Workbooks Without Including Formulas

Nov 1, 2007

I have a code (which I have posted below) which I slightly modified and was kindly provided for me by 'antoka05'

It saves all my worksheets to seperate files which works great. :D

What I wanted to know is can I modify this code so that when it saves the worksheets to seperate workbooks that it just saves the values and not the formulas that are in the cells.

Also I need to remove the password from the sheets before they are saved, could this also be incorporated into the code?

View 11 Replies


VBA To Copy / Paste (including Formulas) To Next Available Row

Mar 20, 2014

I need some VBA to copy row 2 then paste it (including formulas) into the next available row in the same sheet but then also clear the data in row 2 but retain the formulas.

View 2 Replies View Related

Copy Similar Named Worksheets To Seperate Workbooks

Dec 23, 2008

I have an application where I create up to 400 worksheets based on imported data.

These worksheets are named programatically with a main name and a sheet index number (to ensure no duplication of sheet names).

Typical Sheet names are "VARIOUS (1)"; "VARIOUS (2)"; "PART 1000 (3)"; "PART 1000 (4)"; "PART 1001 (5)"; "PART 1001 (6)" etc.

I want to be able to loop through all the sheets and copy similar named worksheets to seperate workbooks. So all the VARIOUS go to one workbook, all the PART 1000 goto another, and all the PART 1001 goto another. I can only determine the worksheet names by looping through all sheets - as I am not sure what they will be called because it is done programmatically. ALL sheets will contain a "(n)" where n is the sheet number, so I believe can extract similar names from the string preceding the left bracket.

At the moment I just lump ALL the worksheets irrespective of name to a single workbook creating an array and using the SPLIT function. This is very efficient and what it does, and I still want to use the SPLIT function and arrays.

this is the code I currently use, any help much appreciated!!! Please tell me how to modify this existing code to clump together similar names and copy - I guess I will need to loop multiple times to achieve this.

For Each mySheet In ActiveWorkbook.Sheets

Range("Arc").Value = "'" & Range("Arc") & "," & mySheet.Name
If Left(Range("Arc").Value, 1) = "," Then
Range("Arc").Value = "'" & Right(Range("Arc").Value, Len(Range("Arc").Value) - 1)
End If

Sheets(Split(Range("Arc").Value, ",")).Select
Sheets(Split(Range("Arc").Value, ",")).Copy

View 5 Replies View Related

Import Blocks From Text File Into Seperate Workbooks

Jun 13, 2008

I have wrote a few macros before which involved searching, copy pasting and comparing.

I now need a macro that can first open a text file, in this text file will be 6 section separated by 6 different titles so it will look something like this



View 9 Replies View Related

Compare Two Worksheets (including Formulas) And Highlight Different Cells

Oct 17, 2012

I want to design a macro that will compare the student's completed worksheet to the model answer worksheet. This will allow the class to work at different paces depending on ability.

For each exercise, the two sheets should look identical; and so, I would like the macro to highlight any different cells in the student sheet in a colour. I would also like cells with identical values, but different formulas to be highlighted - this is why I could not use the solution posted in : "Compare two worksheets and highlight the one sheet's difference from the other one".

View 1 Replies View Related

Saving Values Not Formulas

Feb 13, 2009

I have a estimating workbook with a number of worksheets to provide a estimating process. this includes the final worksheet with the estimate on.
This is fine if i am printing it to send to the customer or sending as a PDf.

However i need to save as a stand alone excel workbook. But all pre-formated and only with the values in it and not the formulas. It would be easiest for the users if it could be done with a button on the quote sheet (ie done with as macro as some users are not very IT literate and do mess up).

View 4 Replies View Related

Close Workbooks (with Variable Names) Without Saving

Mar 7, 2014

I have completed a long macro, and at the end of the macro I want to close a few workbooks automatically without saving. These workbooks have been assigned variable names.

Is there a command line I can use, that will not prompt the user to save?


View 3 Replies View Related

Text To Columns: Split Data In Seperate Seperate Coloumn

Jul 23, 2009

Attached file where i m not able to split data in seperate seperate coloumn

View 3 Replies View Related

Formulas Across Two Workbooks

Sep 16, 2009

In Workbook 1 O1, I need a formula that will, if Workbook 1 M1 = "Yes", find the value from Workbook 1 N1 anywhere in Workbook 2 Column E, and when it is found, look at the value 2 columns to the right in Workbook 2. If there is a value in this cell, return "Yes" in Workbook 1 O1. If there is no value in this cell, return "No" in Workbook 1 O1. If Workbook 1 M2 = "No", the formula should return "No".

View 12 Replies View Related

Formulas Move When Cutting And Pasting Workbooks

Jul 14, 2014

I've set up a spreadsheet structure at work which is three workbooks linked together.

I created this initially within My documents saved locally to my machine. Now though, I need to move the folder containing these workbooks onto the server to be accessed by others. My problem is that when cutting and pasting the folder, the formulas within the three spreadsheets still refer to their original location within My Documents rather than following the folder to their new location.

View 4 Replies View Related

Copying Formulas Across Workbooks Without File / Book Referencing?

Aug 13, 2012

How to copy a formula (16 columns) from a workbook to another, without referencing the source workbook.

Some people are using tricks such as replacing the equal sign "=" for another character such as "^" then using replace all to put the equal sign back again... but this is too much trouble.

View 2 Replies View Related

Saving Multiple Workbooks At One Time/file As A Text File

Mar 16, 2007

found the following code which works for 1 workbook at a time. I am trying to save 7 workbooks at 1 time. Is it possible?

Sub SaveAsCell()
Dim strName As String

On Error Goto InvalidName
strName = Sheet1. Range("V77")
ActiveWorkbook.SaveAs strName

It will work on the first sheet but none after that. I need to have each workbook saved with the value in cell V77. Also if that is possible, is it possible to change where the file is saved as well?

Right now I have a master workbook that will open the 7 other workbooks, paste data onto several pages in each workbook. I would like the macro to save the workbooks. The workbooks are named: 02 Tuesday, 03 Wednesday, 04 Thursday, 05 Friday, 06 Saturday, 07 Sunday, 08 Monday.

View 9 Replies View Related

Saving A File Without Saving Macro

Jan 30, 2003

I have a spreadsheet that imports data, manipulates it then deletes 2 of the sheets then saves the file under a different name to the network. Is there any way to save this new worksheet without it storing the macros - so when the user open it, only the data is there and they get no prompt to enable macros?

View 6 Replies View Related

Saving User Inputted Data From A Worksheet To Another Worksheet And Saving It

Jan 7, 2009

I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.

I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"

On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.

What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.

I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.

Attached is my excel file.

You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.

View 9 Replies View Related

Using Cell References In File Paths For Formulas To Create Dynamic Formulas

Dec 3, 2013

I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:

=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292

What I want to do is extract the file path from the above formula and make it a composite of several cell references.

So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:

_MONTH =11 November
_YEAR =2013

I tried several versions, I am hoping for something like this:

=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292

View 4 Replies View Related

Converting Formulas To Relative/absolute References With Formulas Referencing Other Sheets

Dec 15, 2008

I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.

View 9 Replies View Related

Getting Averages From Seperate Worksheets

Dec 9, 2009

The first one, I do i remove #DIV/0! from cell O7 in the worksheet "My overview"?

The second one, I need to get averages of the QA score for the 3 months from January to March in the worksheet "My Overview" Cell E14 for the corresponding person on each sheet. I understand that there need to be values in place to get an average but is there a way to overcome this?

View 2 Replies View Related

Sum If, But The Sum Range Is Seperate Cells ?

Oct 8, 2008

I want to do a simple Sum if. My range is simple and so is my criteria, but the actual range of cells to sum is not in one continus row or column. Can I do the sumrange as seperate cells?

View 3 Replies View Related

Consolidating 4 Seperate Worksheets

Oct 21, 2006

I have 4 worksheets where the structure is exactly the same except the figures differ

The worksheet have text and values in columns A to S. I need to write VBA code that will combine the data and show the descritions in a consolidated woorksheet.

View 9 Replies View Related

Seperate Names From Numbers

Apr 27, 2007

I have the following list of names, phone and radio numbers that somebody put together all in one cell. Formated as General with Word

View 9 Replies View Related

VBA: Seperate Vba Code From File

Aug 14, 2008

Is there a way to put vba code to a standard alone exe file? This way, I don't have to keep update the code on different excel files.

View 9 Replies View Related

Run A Sub Or Function In A Seperate Workbook Using Vba

Jan 28, 2009

I've seen that you can set up a reference and that may be the only way, but my guess would be that it is not the only way.

I'd like to open and run a sub from a different workbook (in the same folder) using vba. For example, test1.xls would have a sub that would open test2.xls and run a sub from test2.xls before closing and returning to test1.xls.

View 9 Replies View Related

Seperate Text In A Column

Oct 4, 2009

Is there a simple formula that can be used to seperate text in a column.

ABC - 123456
I want to return the text before the "-".

View 9 Replies View Related

Formula Seperate The Column

Oct 29, 2009

I have columns with titles at the top, and with numbers in the below cells.

What I would like is a formula for a column seperate to these that firstly, looks if there is a quantity in the relevant cell on that row, secondly takes the title (top cell) of that column, and thirdly puts the number in the cell on that row after the title:

A_____B_____ C_____ D_____ E______ F
1_____UK____ Ger___ M/E___ Rus_____ Nor
2_____80_____250___ 300___ 350_____200
3____ 60_____200____350____350____ 150

So in cell A1, I would like a formula that reads the subsequent cells and their titles, and gives a one-cell summary of the data seperated by commas. So in A1 it would say:

UK 80, Ger 250, M/E 300, Rus 350, Now 200

And in A2:
UK 60, Ger 200, M/E 350, Rus 350, Now 150

View 9 Replies View Related

Seperate Decimal & Whole Number

Sep 21, 2007

How do I seperate 1.2 and 1.02 miutes from the same column into Minutes and Seconds Correctly.

How do I get the first to seperate as (1) Min (20) Secs and the next to
seperate to (1) Min (2) Secs

I have 200,000 line of data in one column with times from .o1 mins to 1498.40 mins that I am doing this to. I need to convert it all back to seconds.

View 4 Replies View Related

Formulas To Hide Partial Concatenate Data And Determining Two Other Formulas

Dec 11, 2013

I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.

I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?

View 11 Replies View Related

IF Statement Including BETWEEN

Mar 31, 2006

How can I write an IF statement that evaluates whether a cell's value is
BETWEEN two numbers?


A1 = 89.99

I need a statement that evaluates whether A1's contents are between 80.01
and 90.00.

View 14 Replies View Related

Including 0 In Formula (#DIV/0!)

Jan 31, 2010

I am trying to create a marksbook that will ignore blank cells and calculate a percentage based on the amount of tasks completed.

So far I have managed to get it all working EXCEPT if I enter 0 as a value.

For example, suppose there are two tasks, both of equal value. If a person was to get 100% for the first task, and 0 for the second task, you would expect the total to be 50%. I cannot get my formula to recognise that a 0 has been entered.

(Cells F5:T5 are what the task is out of. Cells F6:T6 is the weighting. I want to be able to enter a 0 in cells F8:T8 to include it in the final percentage.) ...

View 9 Replies View Related

Finding Duplicates In Two Seperate Columns

Sep 16, 2008

This should be an easy one, it has slipped my mind.

I have columns A and B, I want to find any names that appear in both column A and column B. I want them to show in a certain color.

View 11 Replies View Related

Copy Time From Seperate Spreadsheets

Jan 28, 2009

I get several workbooks from co-workers in other cities. They include data like the total run time for production. When I copy the time 24:03 fom one cell in their spreadsheet to my spreadsheet it shows up as 0:03. I've already double checked that I'm grabing the right cell and that the format for the cell is correct.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved