Here's a code I've been working on. Cant seem to get it too work across all the worksheets.
Code:
Sub remerge()
'Remergeonly Macro
Dim WS As Worksheet
Dim R As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each WS In Worksheets
With WS.UsedRange
[code]....
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
i have 7 worksheets in the same workbook
i have 3 macros, each macro is the same for 2 worksheets, i.e identical sheets, but for two seperate clients
I have recorded a macro that just basically ''presses the 6 buttons in turn"
but this seems really clumsy, it there a way i can have a ''Super duper master button'' Called Main on sheet 1
and which executes
Macro 1 on sheets 2 and 5,
macro 2 on sheets 3 and 6
and macro 3 on sheets 4 and 7
and then returns me to sheet 1.
I am using excel 2003
I have made 7 macros for inserting a blank row above certain text, which are located in single column but in different row. 3 macros are for one sheet and 4 are for other. I am able to run them at once but the changes are getting done in only one sheet (which is active when I run macro)
Below is the code of macro:
Sub Space1()
Dim c As Range
For Each c In Range("D1:D60")
[Code]......
I have a Macro that copys all the cells with a 9 digit number in a range and lists them into col CC
However the way it is written Each macro is stored into its own worksheet. the problem is that i need to do this for all 43 tabs in the workbook which means a lot of copy and past when I build a new workbook.
So is there a way to load 43 diffrent macros into the 43 sheets in vba?
Or is there a way i can make the macros Global? so that they can all be stored in the same place but still work?
The below macro is saved under the workseet "PEBBLES" in vba
Code:
Code:
Sub Pebbles()
Dim objReg As Object, objMatch As Object, objColl As Object
Dim rngWhole As Excel.Range
Dim rngCell As Excel.Range
Dim lngRow As Long: lngRow = 1
Set objReg = CreateObject("vbscript.regexp")
[Code]....
ive got a macro which works with data on hidden sheets within the workbook - when i hide the sheets the macro gives errors and will not run. when i unhide the sheets everything runs fine!
View 9 Replies View Relatedhow to save my macros to a spread sheet? It seems every time I close my sheet(s) (after saving???) and come back to them later, my macros are gone??
I have a spreadsheet with some macros in it. When I run the macros, I would like to unprotect the worksheet and then protect the spreadsheets when the macros are done. The excel tabs I would like to protect/unprotect are called Graph and Data.
View 3 Replies View RelatedI have been asked to create a spreadsheet that has a front sheet where you can free type search criteria and then it will populate rows below with all search results from the remaining sheets within the workbook, for example;
In cell N15 I type the search criteria and hit a button
In cells F31-O31 it returns the following information;
CategorySub-CategoryCourseDrop Down Option 3SolutionFurther information or support
I have little to no experience in writing macros
1) Force user to enable macros.
2) keep three worksheets very hidden all the time.
3) passwrd protect vba
I have been using this code to force user to enable macros but this unhides all sheets other than "macros not enabled" sheet. I need three worksheets to remain very hidden all the time. How do I do this.
Option Explicit
Dim ws As Worksheet
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Worksheets("Macros Not Enabled").Visible = True
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = xlVeryHidden
Next
End Sub
Private Sub Workbook_Open()
For Each ws In Worksheets
If ws.Name "Macros Not Enabled" Then ws.Visible = True
Next
Worksheets("Macros Not Enabled").Visible = xlVeryHidden
End Sub
Can someone please help? I am very new to vba but I have managed to write code ( multiple subs strung together with Call statements) for about 25 sheets in this one workbook. Each sheet has a command button that initiates the data maipulation that I want to accomplish on that particular sheet. It all works very well when I go to each sheet and click on the command button.
Now I want to make all of these processes run from one command button on the first sheet. All of the subs on every sheet now have unique names because I anticipated wanting to string them all together and run them from one command button. I guess you call this a module for the entire workbook, but I am still struggling with the terminalogy of all this programming, so don't know for sure.
I added the code from the second sheet/command button to the end of the code from the first sheet/command button and joined them with a Call statement. Figured I would go about this sistematically, sheet by sheet. Instantly, I got an error message " Activate method of range class failed" when I ran it. At the start of the added code, I had added something like Worksheets("Sheet2").Activate figuring that I needed to activate that worksheet in order to make the rest of the code work. That is where I got the error.
Sorry for the long winded explanation. Can someone tell me what I need to add/do in order for the code to run without error as I string together all the pieces from each sheet?
I have the following code as workbook module but it is not working, I have tried fiddling with it but I cannot work out what is wrong, can anyone help please.
Private Sub Workbook_Open()
Dim ws As Worksheet ....
I am trying to create a Macro for taking information from a master sheet "sheet1" and dumping it into another worksheet based on a single variable in "sheet1". I have attached the excel workbook for reference. In detail, I would like to pull all of the rows where cell "F" in "sheet1" equals "1" and dump them into the new sheet titled "Dept1" with the same headings. The same for all rows where cell "F" in "sheet1" equals "2" to dump into the new sheet titled "Dept2".
View 6 Replies View RelatedI now have the spreadsheet I want to distribute saved as an add in stored in a network repository. RoyUK has provided me with some links with code I lifted to add custom menu items.
The last problem I have is that I'm not trying to call a macro, but am instead trying to load the sheets (there are 4 sheets) and forms (there are 7 forms).
With what I have now, I can launch a macro with no problem, but cannot get the sheets / forms displayed to the user. I've tried launching a form by loading / showing it, but I get error message (runtime error 9).
For each of the sheets below, I need to extend the bottom of the page down ten rows and i need a page break after 60 rows.
Does anybody have any ideas? Here are the tabs that I need this macro on:
(MySheets = Array("FY09 Installation Support", "FY09 Install", "FY09 Purchase", "FY09 CF Discretionary Grants", "FY09 CF LOI", "FY08 Purchase", "FY08 Installation Support", "FY08 CF Discretionary Grants", "FY07 Sup Install Support", "FY07 CF Install Non-LOI", "FY07 Sup Purchase", "FY05 CF Carryover Install", "FY04 Recovery Funds", "FY05 Recovery Funds", "FY08 Safety Carryover", "FY09 Safety", "FY09 Transport Canada")
I have a sheet with macros that allows users to fill in data from columns F10 to O10 from row 10 to 54.
Column P will then make some computations based on the inputs from col F to O.
Row 55 will also make some computation based on inputs from row 10 to 54.
How to I modify the code below to allow users to either add/delete columns and rows such that it does not fowl up the computations?
The computation does not have to be at column P or at row 55, they can be dynamic and change depending on how many rows and columns the users delete/add.
A little background on what the code does. From Column F to O, the code UNLOCKS the columns only if user inputs date in Cell 3 of that particular column.
Example. Column F is locked from F10 to infinity at the start. Only when user inputs date into F3 does the cells F10:F54 is unlocked for user to input data.
This goes on until column O. I want users to be able to add more columns, or delete ones they don't need.
I am new to using Macros and have not had great progress building a macros to make my life easier.
What I am trying to do
-In my main database tab 'FW Telecom' Use column A ( a set of numbers) to create a new tab based on that number, with a 'FW' infront IE ( column A shows 11, i want the tab to read FW 11)
-I want to create one for every number in the column
-Then I would like to copy my 'Template' tab to each new tab.
-From there I would like to fill in the information in the new tabs from the main database tab 'FW telecom'
-Since they will all be copies of the original template, i figure i can just get it to pull from the first column A and then fill in the info.
Not too much in theory going on here, just take my database and move the pertinent information to a user friendly look which I set up in 'template'
I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0
Splash.Visible = True
I created several macros objects to do simple hide columns, filter on, filter off, etc. type functions, using the Tools / Record Macro feature. Then discovered they won't work in a protected worksheet. Is there a way around this situation, other than turning protection off? Here's an example of the code for one of them, although I get the error or every attempt to perform a macro once the sheet is protected:
View 4 Replies View RelatedI have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies View RelatedI have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
I would like to use VBA to search a folder and copy data from tabs within the excel files there. The data will be pasted to a tab of same name in the the main file. All the files are in the same format.
So far I have only managed to list the files in the folder using code I found on your site!
I have a workbook that updates from external source and creates sheets depending on a cell range.
I have put tab 1 and tab 0 on either end of where the new sheets will be inputted, will never know how many sheets
What i need to happen is if someone fills in "complete" in A7 in my "summary" sheet then the values in row 6 in all the other sheets get hardcoded. This needs to happen from A7 down to A26, so A8 = complete then copy row 7 etc
This is what i have so far
I get compile error here ........Sheets(ArrSh(1)).Activate
Also need it to work for all the other rows.
Sub hardcode()
'
'Sheets("Summary"). Select
If Range("a7") = "complete" Then
'
Sheets(Array("1", "0")).Select
Sheets(ArrSh(1)).Activate
[Code] ......
I have been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
The sheet is actually being added to the workbook, seemingly before the error. I resume the code, and a new sheet is placed in the workbook and it errors again. The Debugger stops and highlights on the code above.The sheet count number was 10895 at the error, just as an indicator of how many times the simulation has performed successfully. I am hoping this is something I can fix without having to start over...
[URL] ....
I want to sort the Inventory Checklist sheet based on Column D but it gives me nothing but references errors.
I have 25 sheets in the workbook and a combobox on the main page, The combobox references a range of 1-25 that represents the 25 hidden pages. right now i can get the sheets to unhide one at a time based on the selection e.g. combobox option 1 will unhide sheet 1 but the sheet are representing sites in a design so i need to have the option to select multiple sites in the combobox option so for example if i select 5 then sheets 1-5 should unhide. I hope I've explained that clearly.
The other question or option would be to just duplicate sheet 1 based on the combobox selection e.g. selection 5 duplicates sheet 1 5 times.