What code to write to format all worksheets in a given workbook. However, I have what I will call my "main" worksheet that I do NOT want to follow this formatting. So, let's say I have 5 worksheets total in my workbook, I want to format all of them the same EXCEPT the sheet named "main".
View 2 RepliesWhat I’m doing is sorting one page and copying the information to another page in the same workbook. But I keep getting this stupid error.
Run-time error '1004'
This operation requires the merged cells to be identically sized.
I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.
My current Coding Snippets that I want to use look like the following:
Code:
Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String
[code]....
Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I have 2 worksheets I need to compare. One of the worksheets is sent to me from a vendor and its not in the same format (regarding the order of column labels). What would be the most efficient method to compare these two spreadsheets in a way that I can call out anything that is not in BOTH spreadsheets?
For example, if John Doe shows in spreadsheet A but not in spreadsheet B, can I get that to call out? Likewise if its in B but not A? Im basically trying to locate all discrepancies between the two.
Not sure if I have to get both worksheets in the same column order or not
I want the font color I have chosen for a cell in a worksheet to change the font color of a cell in a different worksheet inside the same workbook. Can someone help this newbie out?
View 14 Replies View RelatedI have created a workbook with 31 worksheets (they represent each day of the month). I'd like to know how I can auto fill the date in cell A1 across the 31 worksheets without having to type in each day myself. I need the date format to look like this: Wednesday 7th May 2014
In other words, cell A1 in worksheet 1 needs to say Thursday 1st May 2014, cell A1 in worksheet 2 needs to say Friday 2nd May 2014, cell A1 in worksheet 3 needs to say Saturday 4th May 2014 and so on for the remainder of the worksheets for that month.
I have created a workbook with 31 worksheets (they represent each day of the month). I'd like to know how I can auto fill the date in cell A1 across the 31 worksheets without having to type in each day myself. I need the date format to look like this: Wednesday 7th May 2014
In other words, cell A1 in worksheet 1 needs to say Thursday 1st May 2014, cell A1 in worksheet 2 needs to say Friday 2nd May 2014, cell A1 in worksheet 3 needs to say Saturday 4th May 2014 and so on for the remainder of the worksheets for that month.
I have a workbook which I'm using to collect monthly data. Each sheet (with the exception of a sheet called 'welcome') is named by month and year. Is there a way to sort these in chronological order?
View 3 Replies View RelatedI have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?
View 1 Replies View RelatedI need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
I'm making a Excel list for trading cards (MTG) and I've divided it into different worksheets in order to sort it by the color of the cards (not really important I guess).
The issue I have is that I want it to copy certain columns (in this case C3:C1000, F3:F1000, G3:1000 ) from almost all of the worksheets into a new worksheet where it should paste them into column A, B and C.
In two of the worksheets it's also different (C3:C1000 , G3:G:1000, H3:H1000).
I've seen many where you copy one range from multiple worksheets, and that would've been okay I guess if not those two worksheets were different. I'd prefer not to change them, and I'd also prefer if I could've gotten just the information I needed also. Is this possible to do?
I'm trying to email multiple worksheets from a list to multiple recipients in a list, but in one file per person. In a sheet called "Email list", for example, I have a list of 50 worksheet names (e.g. one for each cost centre) in column A (with a heading in cell A1, if that's OK, so the list starts in A2) and in columns B-F up to 5 recipients for each worksheet (e.g. 3 recipients might be 2 supervisors and their manager).
Also, each manager and supervisor might also receive several sheets.
Although columns B-F are presently free-format (they can be anyone's email address, regardless of whether they're a supervisor or manager), it might be easier to split these columns so B,C,D are supervisors emails and E,F are managers, if that makes it any easier creating the collated file of worksheets..?
Unfortunately the recipients might change occasionally so they can not be hard keyed into the macro itself (which would be much easier), but need to be in a table so it's easier for the user to update and maintain - in the past, I've managed to hard key the requirement into a macro which I've maintained (by recording and editing the macro), but I don't know the VB to figure this new request.
Ideally, I am trying to create a macro that can collate the various worksheets each person will receive and send the selected sheets in one file to that recipient, rather than multiple files within one email and/or multiple emails.
I wrote a chart formatting macro, which works when applied to a specific sheet, and I'm trying use it in a for each loop. The macro is supposed to loop through a list of sheets and only format chart 1. I'm getting a "run time error 438: object doesn't support this property or method" at this line:
Code:
.ActiveChart.Shapes("TextBox 1").TextFrame.Characters.Text = "Bodily Injury (BI) Liability Claim Trends" & vbLf & " 2005-" & Range("K5").Value & " Percentage Change"
Here is my for each loop:
Code:
Sub UpdateTextAllStateCharts()
'This macro loops through worksheets in a list and performs the assigned task
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("C:C")
If sheet_name.Value = "" Then
[Code] .....
This line is also causing the same error:
Code:
With .Characters(42, 68).Font
I have an excel workbook with 8 worksheets. Each worksheet has vertical columns (approx 250 columns per sheet) of numeric data. Is there a function or macro that will combine all of this data into one vertical column without having to individually cut and paste each one into the new column?
View 3 Replies View RelatedI have 2 large XLS sheets that need to be split into seperate sheets.
The first is only 5 columns wide but the amount of rows changes day to day.
The second is a maximum of 7 columns wide and again the amount of rows will change.
I'm hoping for a VBA code to be able to do both on seperate books.
The A Column has the name of the company, and this can include (/,&) that will need to be removed, the names can also exceed 31 characters.
I would like to seperate the sheets by the company name and have the name appear as the sheet name.
Also the formatting from the master sheet to be copied to the resulting sheets, with a header row.
I have included the data for the larger of the two workbooks. data.jpg I have to pull the smaller report each day and the larger report each week.
My query is;
I have 2 different worksheets having similar format (header rows/columns) but different data (values). I need to pull out these data based on their values, means if cell is blank leave that cell and move on, but if cell has some value then pull that value with its corresponding row & column headers to place into a simple table to further use it for PivotTable. The number of rows and columns are equal in both worksheets.
So basically, the proposed function will run a check on both worksheets within a specific range of cells for their values and if not blank, it will fetch that cell value along with its relevant row header and column header and place all these attributes into the destination worksheet.
Hope I am quite clear with my query. with the required function to ease my life?
I would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.
I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:
On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)
Code to merge worksheets from different workbooks stored in different location.
I have a sheet called "Master" in all the workbook I want to combine.
I have a unique password for all the workbook as well.
All the workbooks are stored in different folder location.
I would like to do a paste special values when the consolidation takes place.
I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.
Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.
I am trying to consolidate information from multiple (atleast two or more) worksheets (in the same workbook) into a single sheet.
The worksheets have identical column headings. There are 4 columns in each worksheet. The first three columns are text strings (and some cell in these columns may be blank). The fourth column is an integer. The number of rows in each worksheet is most likely to vary. Within a worksheet the rows are unique. However, some rows may be duplicated across worksheets.
To reiterate, here's what I am trying to do.
Step 1. Create a summary sheet.
Step 2. When there is a unique row (identified by the first three cells in that row) across worksheets then I have that row as is in my summary sheet.
Step 3. When there are duplicate rows (identified by the first three cells in that row) across worksheets, I want to add up the cell in the fourth column associated with those row in my summary sheet
I have tried union (works only within a sheet), consolidate (works only with a single column). I don't need the sophistication of a pivot table but simply an aggregation of data
I am trying to do this using macros (within an add-in)
I have enclosed a sample workbook with the expected output. I created this workbook by hand in an attempt to clarify my problem statement.
I need a summary page in a workbook that will pull data from multiple workseets with mulriple criteria.
I have treid sumproduct, but that doesn't seem to be working when I select a range from multiple workseets.
I have a macro that I presently use with just one worksheet. I now need this same macro to do the exact same thing on additional worksheets (in the same workbook) that I plan to add. These new worksheets will have the exact same format, structure etc., only the data that my users enter will be different.
Question: Is there some simple way to modify my existing macro so that each time it runs it performs its functions over multiple worksheets?
(I'm guessing that it has something to do with the "worksheets.active" code but I can't figure out how to get the right syntax to get that to work.)
I have 4 sheets that use the same template.
Now user has option to add new Rows to it.
There are few predefined row layouts (using diffrent lists as column values).
This is code that i tryed to use:
Only adding new row and insertin its name (i didnt try formating cells yet since i cant get this simple stuff to work).
Dim ActiveSheetName As String
ActiveSheetName = ActiveSheet.Name
Sheets( Array("Week One", "Week Two", "Week Three", "Week Four")).Select
Range("A14").Select
Selection.End(xlDown).offset(1, 0).Select
Selection.EntireRow.Insert
Selection.Value = TextBox1.Value
But this code only enters new row in first sheet (Week One).
Other sheets are unchnged.
TextBox value is enterd in 4 sheets.
I have the following code for adding and naming worksheets from the default setting of three worksheets for a new workbook. However the first array element, "D1" is skipped and a blank sheet is inserted at the end.
Sub AddWorksheets()
Dim Tabs As Variant
Dim I As Byte
Tabs = Array("d1", "O1", "O2", "H1", "H2", "L1", "L2", "C1", "C2", "V1", "V2", "AC1", "AC2", "M1", "M2", "T1", "T2", "P1", "P2", "SR1", "D1", "D2")
Sheets.Add , Count:=19
For I = 1 To 22
Sheets(I).Name = Tabs(I)
Next I
End Sub
I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.
For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11
Is there a faster way to do this?
I am having a problem getting my formula to return a result. The basics are that I would like a count of a specific set of data in a section of a workbook. So in column 'A' I am searching for 'TextA' but this also has to have 'TextB' in column C. The problem is I have 12 consecutive monthly worksheets I would like to pull this data from and I only really want to write one formula. I can (and started to) use the countifs function plus countifs function etc referencing each worksheet individually but this seems long winded.
I have read on this forum that I can use sumproduct combined with countifs but I can't seem to get it right. I have listed my formula for a single sheet countifs function below, but I don't know how or if to include the sumproduct function to reference the rest of the monthly worksheets. The other thing I should mention is that there are other worksheets too, so it would need to be specific to this range, but they are consecutive within the workbook.
Writing a VB macro to automatically export multiple worksheets to one large or many individual comma delimited CSV files. There could be as many as 100 worksheet tabs that are virtually identical each with historical stock price data....a different stock ticker for each tab.
Here are a couple of links that seem relevant: [URL]