I am using Excel 2007 with an extension from Adapx, Inc. of Seattle for electronic data capture and input.
I am trying to automate a process of inspecting and recording inspection data that has been collected and entered manually to this point. The data currently comes in as a variable quantity of 8.5 X 11 sheets. Some reports are 1 sheet; some are 7 or more sheets. In any case, the first sheet has all the ID information with some details and details of other samples are on subsequent sheets. When done manually, they are stapled together.
When done electronically, the report comes in as a series of worksheets. I describe the code I need as an electronic staple of a variable quantity of worksheets in Excel.
That is to say, I need to identify a group of worksheets as a single inspection report.
In order to make the plan work, I have the following idea. This is just my starting point. I am open to any ideas you care to share.
I need to have the current worksheet look at a specific cell in the previous work sheet.
If the value matches the value of the co-responding cell in the current work sheet, then copy the values of several specific cells into the current worksheet. This will repeat until the trigger value changes. It will increment by one. When it changes, I need to look at the next work sheet. If the trigger value matches the new trigger it will again copy the values from a specific set of cells of the current 'previous' worksheet into the then current work sheet. This sequence will repeat until there are no additional worksheets to process.
In an Excel Workbook with multiple worksheets set up exactly the same way, is there a way to select all sheets and perform "group and Outline" functions. I find that when I select all sheets, the "group and outline functons are not functional.
I Need the outline/group feature to work on a protected workbook with over 200 sheets, meaning a macro to cover the entire workbook not just a single sheet. This macro should travel with the workbook as it will be on many different peoples PC's
Private Sub Workbook_Open() Dim cSheet As Integer Dim tSheet As Worksheet On Error Resume Next cSheet = 1 Set tSheet = Worksheets(cSheet) While Err.Number = 0 Call wbProtect(tSheet) cSheet = cSheet + 1 Set tSheet = Worksheets(cSheet) Wend Err.Number = 0 End Sub..........................
I would like to do is to sort only selected sheets. Can someone give me this additional code that can be incorporated in the code below. (If I just select the sheets I want sorted and run the code below, it sorts all worksheets irrespective of whether it is active or not).
Sub SortWorksheets() Dim N As Integer Dim M As Integer Dim FirstWSToSort As Integer Dim LastWSToSort As Integer Dim SortDescending As Boolean SortDescending = False If ActiveWindow.SelectedSheets.Count = 1 Then FirstWSToSort = 1 LastWSToSort = Worksheets.Count Else With ActiveWindow.SelectedSheets For N = 2 To .Count ................
I have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report.
The columns are as follows:
Table Joined Tables Column Name Column Alias Column Description Column Data Type Column Length
From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.
This is ok but When close the workbook and reopen it, my sheet becomes fully protected and i cant use the group icons. Can anyone either suggest better code or a way to initilise the workbook to act in this way when it loads up. For Reference Below is my full
Sub ProtectAll() Dim ws As Worksheet sSheet = Control.Name For Each ws In ThisWorkbook.Worksheets Select Case ws.Name Case sSheet1 Case Else ws.Protect Password:="PASSWORD", userinterfaceonly:=True ws.EnableOutlining = True End Select Next ws End Sub
I have a huge worksheet containing Blackberries expenses of my company divided by multiple sections. Each section follows the following pattern: Example:
Client 222-5555 MY COMPANY NAME LCC International Calls - Call to Bahamas on June - $50,00 Local Calls - Call to NY on June - $ 30,00 Total Price - 80,00
I would like to select each row between client and total price and paste then on multiple sheets in order to create individual invoices. The selection must be based on the client number (for instance 222-5555) that is part of the cell value.
i have a simple PO tracking system, i have a workbook with multiple sheets at any one time approximately 20, projects come and go and i need to add a sheet periodically, rename the tab to the project ID - 4x numeric.
All this i have managed to do, and sort the sheets numerically, i now need to copy the last (added) row on the Project specific sheet to the last clear row on the summary sheet.
I have also achieved this, but in order to do it properly i need to switch between the specific Project sheet i am adding the current PO to, (any one of 20) and the summary data sheet, the sheet i am copying to.
To achieve this i thought i could assign the TabName (of the Project) to a variable and then refer to it
Sheets(TabName).Select. using msgbox throughout appears to provide the correct results, however, this produces a {subscript out of range error no9} on this particular line. Any suggestions welcome. Same issue i believe i could address my problem if i could whilst in the Project specific sheet i could determine the codename of the sheet which i believe i could then use to switch between sheets programatically.
I am usually able to find the answers to any questions I have by searching the forum or internet. Unfortunately I ran into one that I am not even sure is possible at this time. I have 7 macros like the following. One for each day up to 7. The macros create four pivot tables on the page. I wanted to find out if I could combine it into one macro using variables. Unfortunately every attempt I have made results in an error.
Sub Day1Table1() Sheets.Add. Name = "Day 1 Tables" LastRow = Sheets("All Data").Range("R65536").End(xlUp).Row ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "'Day 1'!R1C1:R" & LastRow & "C18").CreatePivotTable TableDestination:= _ "'Day 1 Tables'!R1C1", TableName:="Day 1 Service Alarms" _ , DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTables("Day 1 Service Alarms").AddFields RowFields:="Service" With ActiveSheet.PivotTables("Day 1 Service Alarms").PivotFields("Service") .Orientation = xlDataField .Caption = "Number of Alarms" End With..............................
I've been trying to bend my mind on how to accomplish this and haven't found a clear way to do this. I have to combine reports from a varying amount of one worksheet workbooks that are all formatted the same and have the same data format in the same cells. I was originally thinking about creating some quick code like
'for when I need to sum activecell.formula = Workbook1.range(b2) + workbook2.range(b2) + workbook3.range(b2) 'and for when i need to create averages activecell.formula = "=average(Workbook1.range(b2), workbook2.range(b2), workbook3.range(b2)
However, the amount of workbooks will change depending on which regional account I'm working on, so I have no 'set amounts' of sheets. it can be anywhere from 3 to 300 (figuratively speaking). Is there a way for me to create something that will be flexible enough to expand the amount of workbooks I need to consolidate into 1 final report that totals them together? or am I going about this totally wrong?
I am trying to write VBA code that groups cells between blank rows and inserts a message if a string of text (a name) is missing from the entire group. I want the code to search cells in column A and group the cells between blank cells. Use the name I input in an input box as the search criteria and insert a message in the Column C next to the last blank cell in the group and then move on to the next group and highlight the cell in red with bold text. I am including a spreadsheet with an example of what the sheet should look like before and after the code is run.
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
I want to make a range variable ("testrange") that I can use to define a range of non-continuous cells and then use that range variable to select that range of cells in different worksheets within the same workbook.
I tried the below code (simply coloring cells red), but get an error when I try to select "testrange" after moving to the next worksheet.
[Code] .....
The next worksheet is also supposed to have the same range of cells colored red, but I can only get it to work on the first active sheet.
I would like to avoid having to repeatedly rebuild the range I want selected each time I move to a different worksheet.
I am trying to return the SUM of a variable cell across multiple worksheets... This 3D formula works
=SUM('March 2012:December 2013'!E27)
I wanted the formula on the next row to ref F27 then G27 etc... (which I can't just drag down) so thought I'd try to "assemble" the formula I wanted using the INDIRECT function...
I tried this formula which doesn't seem to work...
=SUM(INDIRECT("'"&E6&":"&E27&"'!E27"))
NOTE: the script in the INDIRECT brackets returns the text string... 'March 2012:December 2013'!E27
I have several groups of data in the same sheet. Each group has two blank rows above the first row of data. Each group has column "B" in common (e.g., "Phone" in the example attached). I'd like to add a title/label of "Phone" to the first column in the row immediately preceding the first row of data for that group.
I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).
I inserted this code into a rountine I do daily which works fine.
The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.
At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?
I've posted the code i got given below and have highlighted the bit where I have to name the sheets.
how to use the group box to group a number of option buttons together.
Question is, lets say, i have a total of 4 group box. if i want it to work in such a way where by once 2 option buttons are being selected (1 option selected from each group box), the rest of the option buttons in the 4 group box will be greyed out. is that possible?
I have a custom data entry form which is working fine. The form completes customer data for reviewing at a later date. I have now been asked to change it so it will seperate the data in to customer's who require some documents sent out and some that dont.
Is it possible to have a combobox on the form and if the options on the combobox are Yes and No (original), when either option is selected the data will complete on to a seperate sheet for cases with documents required or documents not required?
Basically, i have a common workbook template that is used by multiple users across the business to request a cost for numerous new products.
Within the template, there is a common section at the top, where specific project information is entered. There is also a table beneath where 1 or many products can be entered, with specific information relating to that product in the same row.
All the submitted requests are uploaded via an email attachment, to a particular sharepoint directory.
What i would like to do in the master workbook is the following:-
1. Open in turn every uploaded workbook within the sharepoint directory and copy the following cells into the master workbook, each in it's own row (or next available), with the data in adjacent cells.... 1st cell to enter data is $B6.
Cells to copy from each sheet:
Common info contained within cells: $DG$2,$N$11,$N$12,$N$19,$N$13,$AO$7,$AO$8,$AO$9,$AO$10,$AO$11,$AO$12,$AO$12,$AO$13,$AO$14,$BO$8,$BO$11,$BO$14
Product specific info: $U37, $AD37, $AH37, $DH37, $C37, $O37
Depending on the number of products requested, we need to repeat (loop?) until it finds the next blank row in the table. I have hidden a blank row in the table, so there will always be one!
All of the common information needs to be included for each product specific entry.
For each file, once the upload has been completed, i would like the file to be moved to another "archive" directory.
I have attached the template for information. The master workbook is still in development so can't share currently.
In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.
I have this massive spreadsheet, with the maximum rows excel will fit on one sheet. In the interest of simplicity, this is what the raw data looks like:
Last First Amount
Jones Jim $1000 Jung Joe $700 White Jon $100 Jones Jim $200 Jones Jan $300 Jung Joe $800 White Jon $200
What I want to do is automatically get excel to group all the same names together. Then I want it to sum all the values for each name, then order by largest total value for each person, then order that by name alphabetically. In other words, the above would look like this after the sort:
Last First Amount Jung Joe $800 Jung Joe $700 Jones Jim $1000 Jones Jim $200 Jones Jan $300 White Jon $200 White Jon $100
Or, if necessary, there could be sum total rows under each name...although I don't have spare rows, so IDK. I could delete some rows if I had to, but would rather not.
Conditional formatting. I want to change the color of a group of cells based on data in another group of cells. Example:
If cells G8 and G9 (which are merged) are between 80% and 94%, then I9,I10,I11 (which are merged) will turn Yellow. Also, under the same scenario, IF G8 and G9 is greater than 94%, then cells I9, I10, I11 will turn Red.
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test() Dim StartRng As Range Dim Buffer As Range
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro. But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile. Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile. The next target file in the folder is opened and the actions are repeated in this second target file. For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook. The error message i get is: "Run-time error 91:Object variable or with block variable not set." When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V 13019090W Part A1 68705500 Part B1 64202900 Part C-11 59634600 Part D1 26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.
But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"
How to set the file which got opened from the folder to wbk variable.
Code: Sub dataintoonesheet()
Dim i As Integer Dim jk As Integer Dim j As Integer
