I have a big spreadsheet with a number of sheets, some with dynamic lists which take up a lot of processing power, but which I only use rarely. Consequently the whole spreadsheet is running very slowly. Is there a way to set certain sheets to not automatically update, but retain automatic updating on others? I know you can set Calculation to Manual in the Options, but I need to retain automatic calculation on some sheets.
View 9 Replies
I have 2 excelfiles. The macro should copy the Sheet "SDH" from file "New file.xlsm" and paste it to the file "FileforUpdate.xlsm".
"FileforUpdate.xlsm" has already a Sheet called "SDH". This have to be deleted before past the sheet "SDH" from "New file.xlsm" .
After Copy, delete and past it should save the "FileforUpdate.xlsm" in it's folder and close the WB.
I am working on a addin UserForm to quickly format cell text, especially only select characters in a cell. I have this functional in a modal setting working with the active cell when I activate the UserForm. What I would like to be able to do is to work in excel without having to close the Userform and have the textbox update with each new cell. I have changed the UserForm properties to ShowModal = false which gets the first half. However the textbox will only show the cell value I started with.
VB:
Private Sub UserForm_Activate()
TextBox1.Text = ActiveCell
End Sub
[Code]...
I have a workbook which has links to another excel workbook (source). This workbook has many sheets -all exact copy- with different names. I changed startup prompt (edit->links) to "dont display the alert and dont update automatic links". Then I recorded a macro for updating links (from source) and assigned this macro to a button. What I want to do is when I click this update button I want links to be updated only on the active sheet that Im on. My problem is, when I press this button, it updates all links including other sheets (basically whole workbook).
My current macro is (I dont know coding but I tried changing ActiveWorkbook to ActiveSheet but it didnt work):
Sub update()
ActiveWorkbook.UpdateLink Name:= _
"C:Documents and SettingsAlperDesktopExcelsource.xls", Type _
:=xlExcelLinks
End Sub
I am using excel 2010.
I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.
However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...
How can I update each sheet individually, without compromising the other sheets?
Excel is not responding to Shift - F9.
I have a rather big worksheet with lots of sheets in it. I need to see in the frontpage how many sheets there are in total (-2, the frontpage & end sheet don't count) and how many sheets are currently shown (ie. not hidden, as there are vba-filters which sheets to display).
I don't really mind how it is shown, it could be one cell with 14/53 meaning 14 sheets shown of a total 53 or in seperate cells.
I am trying to select a variable amount of worksheets in the workbook and print just those.
I was trying to do something like...
Code:
Sub print_sheets()
shtcount = Sheets("Master List").Range("A278")
For i = 2 To shtcount
Sheets(Array("i")).Select
Next i
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
...but this wasn't really working...
I have a macro that runs when the workbook is saved, it starts to enter in certain formulas onto the worksheet, how can I have this maro run only when 2 specific sheets are active
So basically I want the macro to run if either worksheet "Sales Jan-Jun" is active or worksheet "Sales Jul-Dec" is active, if they are not the active sheets I want to exit sub
This is what I tried without success
If ActiveSheet ("Sales Jan-Jun") Or ActiveSheet ("Sales Jul - Dec") Then
Exit Sub
Else
Is there a code I can use that will only have autocalc functioning on the active sheets? If not, how about for only the current Files that are active?
View 1 Replies View RelatedI have a workbook that has a sheet for every day of the month (1 to 31).
When it opens it automatically activates the sheet for the day corresponding today.
What I'm missing is how to enable users to only update that sheet.
Example today is the 1st, sheet 1 should be unprotected but all others protected and off course tomorrow it should be sheet 2.
Is there any way to only print active sheets
i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets
i would like to print sheet 1 if cell D19 has data in it
i would like to print sheet 2 if cell D61 has data in it
i would like to print sheet 3 if cell D103 has data in it
i would like to print sheet 4 if cell D145 has data in it
I want to create a macro to check through all sheets in an active work book, if a sheet is empty, then delete it?
View 2 Replies View RelatedI have a large workbook where the tab colors differ. I don't want the first few sheets or the last dozen or so to sort but I want to be able to activate/group numerous sheets and then run a macro that will take those sheets and sort them by color.
View 4 Replies View Relatedis it possible to keep the same active cell when switching sheets in the same workd book. I am in sheet1 and cell C15 is my active cell. When i move to Sheet2 it either defaults to A1 or the last place i was in this sheet. I guess it would be the Worksheet_activate() and deactivate function but not sure
View 3 Replies View RelatedCould you have a look at my attached sheets?
2 sheets here, one is "TEMP" the other is " SUM".I want to have a command button in the "SUM", in order to do the below actions
1.compare the right now A columns between "SUM" and "TEMP";
2.If it does not exist in "SUM",i.e. it is new,then move the row to the last row of "TEMP";
3.If it exists already in "SUM",then update the row.
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
That particular question has been solved, but now i need it to work with multiple values from combobox.
So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7
The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"
WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.
Run-time error '-2147417848 (80010108)': Automation error
The object invoked has disconnected from its clients.
When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate
I know I can update the content of a cell on any sheet from other sheet using for example: =SHEET!$G$22. This works ok for text and numbers, but how to get the same result for a function?
Example: in sheet1, cell B3 I have =SUM(A5:A9). When I modify in sheet1, cell B3: =SUM(A1:A9) I need his formula to change on other sheets as well. How to do that?
code to pull up all the sheet from closed workbook to active opend workbook.
Closed Workbook name : Create Position
Active Workbook name : EIB builder
I don't know why i'm having such trouble with this. I keep getting "Invalid Call or argument". All i need is to loop through sheets 2 to 9 and set the year of 8 pivots per sheet to 2005.
View 2 Replies View RelatedI have a few excel sheets that we use to keep up with clients and the hours spent working on projects for them. We add and delete clients almost every week, and every time we make these changes we have to update all of the linked sheets. I am wondering what is an easy way to have sheets update themselves as we add and delete clients.
View 9 Replies View RelatedI am trying to make a line graph of a company's profitability. Each day I add a new page and use data I collect to determine the day's profit. I want the graph to update each time I add a new sheet. The cell that contains the day's final profit is the same cell on every sheet.
View 2 Replies View RelatedI have a excel workbook with One Master Sheet.
The data entered in the master sheet is automatically updated in the sub worksheets through direct links created . Each sub worksheet is a branch of organization. almost 100 branches are there.
The data is entered for all the branches in the Master Sheet at different periods continuously which is to be kept as such.
For example In first day 10th, 15th branch data may be entered in master sheet first and second row, which is getting automatically updated in the respective branch worksheets. But the problem is when the data is entered for any branch( for example 6th branch) in 5th row of Master sheet, in the respective 6th branch sheet also, it is updated only in 5th row only, leaving the first four rows blank. But i want the updation in the branch sheet continuously without any blank rows.
For updation i have used the simple direct link between the cells of Master worksheet and Branch sheets.
How to get the updation in branch worksheets while entering the data in Master sheet without any blank rows.
I've a workbook and it has two sheets. Suppose, I want to link A1(sheet1) and a1(sheet2). If I update any of the cells in any of the sheets the other one will be updated automatically. How can this be possible? Or are there any other alternatives?
View 14 Replies View RelatedI have already got an anwer for this long back from this site. The code was writted by Mr. Krishnakumar
the thread is here :[url]
i need some changes to be made in this code. The existing code creates and updates the details in the sheets automatically from the master data. I just need the sum of Column I in all the sheets after the last row of Column I.
selecting all the sheets and typing the formula in I column is not possible because, the last row in Column I is different in all the sheets.
In sheet 1, the last row of Column I is Row 15, in sheet 2 Row150 is the last row.
I guess something could be done in macros.
follwing is the existing
Sub TestIt()
Dim sWS As Worksheet
Dim Sellers As Range, Seller As Range
Dim lRow As Long, fRow As Integer
Dim CopyRng As Range, ws As Worksheet
I have 3 sheets: 1: master entry sheet that fills data in 5 different excel docs. Then there is another doc that aggregates data from the 5 and presents totals on the data. I am trying to get the end result without having to have 7 different excel files open, but when I enter into the master entry sheet, the data doesn't seem to be "pushed" up to the 5 different docs until each one is opened up. Obviously, the master aggregator is not updated since the 5 don't have the new data. When the files are all opened at the same time, the data flows perfectly, I'm just trying to find a way to avoid having to open the 5 docs in the middle of the process.
View 9 Replies View RelatedEach monday i get a new price list from our supplier. My job is to compare this list with ours and update if neccesary. The reason, why I'm writing here is simple -I need to automate the process. Since their and our pricelist is somewhat different, it's only possible to use selection comparison. So, I need something that can do this:
1. First I open those two files and make a selection on both of them (like all the apples on the supplier list and all the apples on our list)
2. Push a button that executes a code
3. The code compares a value in the first column
4. If it finds a match, compares the data in second column
5. If data is same, color the cell (or the text) lets say yellow
6. If data is different, update field in our pricelist and color the cell (lets say red), so I can find and recheck it later
7. If the supplier has a _new_ product, the code will insert it somewhere in our list. Doesn't matter where, it may as well be a new sheet
8. Compare the next cells in selected area
I have multiple sheets all of which are identical except for the number of rows containing data. I have been trying to create a macro to update these sheets into one 'Master' sheet but I'm having great difficulties due to me needing to leave Column A and Row 1 blank.
I have uploaded example data of what I am after, sheets 2 - 6 need to be automatically updated to the 'Master' sheet when the macro is run.
I am trying to modify the macro that I have put together so that it executes only specific worksheets within a single workbook. In this example, I would like the macro to update worksheets A, B, and C but I have more sheets in my actual file. I am only able to get the macro to work on the active sheet. Attached is my workbook.
VB:
Sub HideColumns()
Application.ScreenUpdating = False
Dim myRange As Range
For Each myRange In Range("B5:M5")
[Code] .....
I have a workbook that I would like the same operation on all sheets except the first sheet in the book every time I update it (which is once a week).
I get data (currency format) for all of my employees on a weekly basis. Unfortunately this data is shown as a negative number and I need to invert these values. (So I use the formula below)... but I have to do this manually for about 35 sheets and I figure I could automate this somehow with a macro. Here's what I do usually and what I would like the macro to do automatically:
I want to insert a column after column G.
I would like to enter the following formula into H2:
=IF(G4="u", "u", IF(G4
