Macro To Formats Five Worksheets In The Same File
Jul 27, 2006
I did this macro which formats five worksheets in the same file. I saved this macro as a PERSONAL.XLS file. Last night when i open new files to re-run this macro it was working fine excep one of the worksheets wasnt formatting properly....the macro on this sheet wasnt working for some reason.
Then this morning when i opened new files and tried the macros again it wouldnt work at all. And there is no reacord of them I did five of them and they are all gone! I saved them so why did they disappear! I thought that when you saved a macro as a PERSONAL file that you could open new files and re-run that macro again and again????
View 9 Replies
ADVERTISEMENT
Dec 24, 2008
I have a sheet used to process sales orders - about 500 orders per month x ~1.5Mb each. I want to save them as smaller files.
The sheets to save are full of vlookups linked to external sheets and tabs, other formulas, and macros - most of which do not need saving when the sales order is filed. There are lots of merged cells too.
I must save:
1. Cell values
2. Cell formats incl merged cells, borders, colour, font etc.
3. The row and column sizes
4. Print set ups - print area, margins, page setup, header footer etc - (Everything needed to reprint to same as original)
I think I need a "File save as" style Macro which opens a dialogue box for the user to nominate the destination folder (& allows the user to browse for it), and a new file name.
The original file name is "Job Sheet - Omlaw.xls"
The tab/sheet to save is "Front Sheet"
(There are two other tabs - neither of which need saving.)
All cells to be changed to "locked".
The sheet must be password protected in Excel. User to enter it - or not.
The saved file should be "write protected" if possible?
View 7 Replies
View Related
May 3, 2014
I have a file here that already has macros in it. The file is basically a excel document generator. When you click create sku, the document will generate multiple documents based on the user inputs.
1) In the generated documents the original "generator" file creates, I need generated files to have column B and D formatted to TEXT, currently all the generated files are formatted to general. (This code is in module 1, line 84 col 34.)
2) The other problem is I need to append the value in Cell N1 in the "sku data entry" sheet to be appended to the generated file names. (this part of the code is is in module 1, line 150 col 28.)
I was unable to attach my file to this thread because it was to big. however I copied the module where I think the code is causing problems:
Sub procData()
' Starting point for read/extract process
Cells(4, 6).Select ' Range = F4 (row 4, col 6)
For x = 6 To 52 Step 2 ' col F to col AZ
[Code]...
View 1 Replies
View Related
Nov 25, 2011
I have a workbook with worksheets that are named in various ways that I need a VBA macro to sort.
Some sheets have names that begin with numbers.
I am able to get the macro to sort them all alphabetically but those with numbers are always put at the start of the work book instead of within the alphabetical format I want to use
I may have sheet names like:
CAT 3CAT 1CAT 2CAT MONKEY DOG 4DOG 3DOG
This is how I want these sheets sorted:
CAT 1CAT 2CAT 3CAT DOG 3DOG 4DOG MONKEY
The sheet names are examples and not actual sheet names.
Below is my code so far
Code:
For lCount = 1 To lShtLast
For lCount2 = lCount To lShtLast And Not IsNumeric(Mid(UCase(Sheets(lCount).Name), 1, 1))
If UCase(Sheets(lCount2).Name) < UCase(Sheets(lCount).Name) Then
[Code].....
View 2 Replies
View Related
May 3, 2007
- I have a worksheet (W1) which i have filters
- I have another worksheet (W2) that is formatted for a institutional purpose where i must put the things selected in the W1, but the problem is that: in the W1 i have a cell (A1) with some data but in W2 i have a space composed with one line but with several columns and excel says that he cannot paste the information copied in W1 to put in W2.
there is any way to avoid this problem because at the moment i am making copy paste one by one, and it takes to many time and i would like to copy and paste the information selected by the filter in W1 to paste in W2.
View 9 Replies
View Related
Aug 2, 2006
I am attempting to copy some numbers from one spreadsheet to another including the formats. The format I am using is a user-defined one which doesn't normally appear in the list of personalised formats.
My code seems to work fine within the same spreadsheet but fails when I do it using 2 spreadsheets.
View 9 Replies
View Related
Oct 23, 2013
I have a workbook that contains worksheets. They are listed as follows:
Sheet 1ABCDEFG
In cells A1 - A49 I have text. What I would like to do is to have a macro that I can run that will basically copy and save new workbooks with sheets A - G copied over and have the new workbook saved with the file name that I have denoted in cells A1 - A49 on Sheet 1. Also, the macro would ask me where I want to save the new Workbooks.
For example, if this were Sheet 1, Column A then the cells below would be the saved name of the new workbooks and the new workbooks would have Sheets A - G in themRed
Blue
Purple
Black
White
Yellow
Orange
Green
Gray
Brown
One more piece of information, the file that is being copied and saved is large (~80MB). If there is a macro that would allow me to simply "save as' the workbook and the Saved Workbooks would be named using the data in Sheet 1, that would work as well in case copying, pasting, then saving may take more time
View 1 Replies
View Related
Oct 12, 2008
I use Excel 2003 and am trying to put together an automatic macro to apply five conditional formats to a range of cells. The range is H3:H21 and is unnamed. The values in the cells are derived from formulas. These are the conditional formats:
If there is a 1, the background color should be gold/44 and the font should be bolded.
If there is a 2, the background color should be gray 25%/15 and the font should be bolded.
If there is a 3, the background color should be dark yellow/12 and the font should be bolded.
If there is an e, the background color should be red/3 and the font should be bolded.
If there is a 0 (zero), the font should be white/2.
From what I understand, I need to put the code in by using the "View Code" option in the tab sub-menu.
View 10 Replies
View Related
Jun 4, 2014
I receive sales data from my wholesalers every month and I continually have to format them to fit the structure of our in-house database. I wanted to design a macro that would automate this process. However, in some months, the files are recieved in a format that is a bit different from the wholesaler's usual format.
Is there such thing as an initial "litmus" test where I could try running the macro and if it doesn't fit the usual structure, there's an error code and I could do it by hand?
View 3 Replies
View Related
Nov 20, 2006
An in-house server spits out a whole lot of dates which I put into Excel and then use the dates for analysis (VBA code). The problem is that Excel is getting confused about the date formats. Sometimes the dates are formatted as dd/mm/yyyy and then the next line can randomly be mm/dd/yyyy. This totally messes up all changes of having data integrity.
View 5 Replies
View Related
Sep 25, 2006
I need to find a way to insert a row (preferably at the bottom of an existing list) that mirrors the formatting of the row above.
I have had a stab at this (see attached example file) but my solution requires the user to select the correct activecell before it works correctly
View 9 Replies
View Related
Apr 18, 2006
I can't open a file anymore. On opening an exclamation mark message appears telling me the file contains "Too many different cell formats". After clicking "Ok" Excel doesn't continue the opening proces. However I need the info in the file. how to retreive the info in the file?
View 2 Replies
View Related
Sep 15, 2014
I have two columns containing dates (Date1 and Date2). Date1 is like a long date and Date 2 is a short date. I need a macro to compare these two dates and delete rows where Date1 <> Date2. Please find attached the before and after file which also contains the date formats for these two dates.
View 8 Replies
View Related
Dec 17, 2011
I have a pivot chart, filtering data from a pivot table, the problem I've got stuck is that every time I filter the chart the colors and weights of the chart change to default values, not what I want, I've managed to every time I click on the pvt cht tab it updates with the colors and weights I want, but when filtering I don't know how to do.
View 1 Replies
View Related
Nov 23, 2005
I need to insert an Excel worksheet in the PACS (Picture Archiving and Communications System) in our medical imaging department. Our PACS only accept JPEG or TIFF or DICOM format. Currently, we convert the Excel sheet to PDF and then JPEG and then insert it in the PACS. Is there a way (macro? VBA?) to program a cell (lets call it "Save as a Picture") so that when I click on that cell, the worksheet get saved as jpeg or tiff or dicom format?
View 3 Replies
View Related
Dec 2, 2008
The macro firstly formats the data that is pasted into it from an access database, then it does some calculations to determine when 10 working days from a specific date is, and when 20 working days from the date is (these go in new columns at the end of the data). The macro will also add new columns which say "Not resolved" if there is no date in the Resolution column, "Yes" in the "10 working days met?" column if the condition is met and the same for 20 workings days in a different column. As the colouring etc takes a long time I really wanted to add as conditional formatting to the macro!
how to attach a spreadsheet here then I can show you what the outcome we want is. A point to note is that there is not a set number of rows each time we do this, and I don't just want 1 cell to be coloured - I want to specify which cells in the row are coloured depending on the results in another cell on that row.
View 2 Replies
View Related
Jan 30, 2012
from an active workbook, I would like to open a new file "testfile" and copy two work sheets ("result1", "result2") into the new file and then close it and continue working in the active workbook. seems simple but i keep getting errors.
View 2 Replies
View Related
Mar 5, 2014
I have a workbook that has a lot of formulas in it that reference material stored on my local computer.
I need a macro that will copy all 42 tabs in to a new workbook book without all formulas, but saving all the formats have made. This would save me from every other day coping and pasting manually each of the pages.
View 2 Replies
View Related
Dec 18, 2012
I have a template file for ordering trafolyte and steel plates. I have added macros to this template file. The existing macros do the following (shortly described):
Macro 1: clears order
Macro 2: update order date + send a read only file to the supplier of plates + save a read only copy of the file into one of three folders acc to info in one of the cells.
It's the Macro 2 I want to edit.
I want to add a "function" which copy a selection of data.column A to N from row 12 to 548 but only the rows where there is a value in column A.
Row 1 to 11 includes standard order info and Macro buttons.
Row 11 includes the heading for order data.
For everytime someone click on the Macro 2 button in the template file, I want the selection to be paste into the first "available" row in a "Total list" file.
The "Total list" file may have to be open (or a function to open, paste selection and then close the "Total list" file may be added)
File and Folder info:
To simplify suggestions, the following file and path info can be used (I can change to the correct later):
Template file name: template_order.xlsm
Template file location: \servershared emplate
Total list file name: total_list.xlsx
Total list file location: \servershared otal
Selection info:
The template file exists of a "general order info area" A1:N10
The column heading for order data is located at A11:N11
The selection to be copied is A12:N550 - But only rows where column A includes data (not empty).
(If the spesific order consists of 14 plates than there will be item no 1-14 in column A and I then I want to copy A12:N25 (row 25 will be item 14).
When I try to use record macro it looks like it only records what's happening in the template file - It doesn't record the pasting in the total list.
View 1 Replies
View Related
May 20, 2014
I want to copy all 25 worksheets to a new file and don't know how to do this easily.
View 2 Replies
View Related
Mar 25, 2009
my formula is as follows: =(VLOOKUP(A7,'[Monthly AR Analysis - Feb09.xls]AR - Current'!$A$1:$I$153,8,FALSE)). I will be using this for formula on about 15 different worksheets ... my issue is the file name changes every month and currently I have to go in and do a find and replace, I would like to have one main page where I could change the file name month and it will change all the rest.
ie: Monthly AR Analysis - Feb09.xls next month changes to Monthly AR Analysis - Mar09.xls .... So basically each month I want it to pick up the new file that has been saved with the new name. So what I would want is to be able to have a cell that I could change the Feb09 to Mar09 and then it would do the same to all the worksheets. I attempted to use CONCATENATE to change file name and it worked but will not work within the Vlookup.
View 4 Replies
View Related
Apr 6, 2011
I have a user that needs to compare MSExcel files with a mass number of worksheets within the file/files. Is there anything to compare Excel files and to print out the compared results?
View 3 Replies
View Related
Aug 6, 2007
How do you get a quick report of all of the worksheets in a given file?
Is there functionality in Excel that would provide this? Or a simple macro?
I inherited a file w/ approx. 50 tabs and I'd like to print out the list of all the worksheets so I can create a checklist of tasks I need to do for each worksheet.
View 7 Replies
View Related
Oct 30, 2011
I have a file that opens a number of files from a source directory (which is identified by the User at run-time) & merges the source data into various worksheets (which all works fine)
Now what I'd like to be able to do is to be able to (silently) save multiple worksheets back to separate files in the original directory based on each worksheet name - e.g:
"Sheet1" and "Control" Sheet" are saved to SourceDirectorySheet1.xlsm
"Sheet2" and "Control" Sheet" are saved to SourceDirectorySheet2.xlsm
"Sheet3" and "Control" Sheet" are saved to SourceDirectorySheet3.xlsm
...
"Sheetn" and "Control" Sheet" are saved to SourceDirectorySheetn.xlsm
(Note that "Control Sheet" also contains Command Buttons & VBA which I'd like to preserve)
View 5 Replies
View Related
Jan 26, 2014
I have an excel file with few worksheets. From the 2nd to the 22nd the structure of the worksheets is the same. For each of them I have to add the same column of data. How do i do this?
View 5 Replies
View Related
May 14, 2014
I need to look at the values in Column B Sheet1 and compare to Values in Column K in Sheet2. If there is a match, I want to put "Cashed" in the cell adjacent to the cell in Sheet1.
View 4 Replies
View Related
Mar 20, 2007
How do I create code that will copy and paste 30 rows of data
Copy from filename "Record1", worksheet name "CA3M", in column A and B, and Paste data in filename "Record2", worksheet name "CA3M"
- Column A 30 rows of data paste into A20:A49 of the new file and worksheet
- Column B 30 rows of data paste into E20:E49 of the new file and worksheet, prior to pasting these results remove all dashes "-" eg.0-0-1 = 001
The location of the 30 rows of data to be copied changes, so the code should always look for the following information in the row before and after the 30 rows to copy in column A and B:
- Find the 30 rows of data in Column A and B where the first row immediately follows the row with the cell result "Account Date" and the last row is immediately before the row with the cell result "Previous Page Page".
Also, I will probably have to update the code to repeat this function for several other worksheets within these 2 files after my database is completely built.
View 12 Replies
View Related
Feb 20, 2008
I am trying to write this code where I need to save an excel workbook with a number of tabs to 1 text file with a name of users choice.
I am able to do this if there is a separate file for every worksheet within the workbook but not if we have the same text file to append every time.
View 9 Replies
View Related
Feb 23, 2014
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
View 1 Replies
View Related
Feb 26, 2014
adapt the code write by TURBO at [URL]....
I'm trying to add more sheets to consolidate the data from different worksheets
What I want it to do is to consolidate each worksheet in every excel file into one workbook that will have the same worksheet structures as the child files
If every excel file has {Sheet1,Sheet2,Sheet3} structure The Master Consolidated workbook should have the same structure but with all the date copied from the child excel files.
Attache it's also an example
Test xls files2.zip
View 5 Replies
View Related
