In the above example, in the blank spaces directly below each change in company, I'd like to summ actual and budget expenses. The spreadsheet has many sheets like this with companies that have different number of lines per company (meaning the summ formulas will be different by sheet and by company).
View 9 RepliesCombine to sheets in one sheets using macro ...
View 9 Replies View RelatedI am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I would like to run a specific macro accross the 20 sheets to the right of my pivot table, the names of these sheets will change each week, so I am looking for a next sheet option to run 20 times or maybe count the sheets and take off 5 but it must run the macro to the right of the existing sheet.
View 4 Replies View RelatedI'm trying to create a macro that will copy and then paste a range of cells to another worksheet but in one row. The cells that I would like to copy are in 3 different rows. They are B7:F7, C12:D12, C17:E17. Now I've managed to find a macro through this forum that will paste the particular cells but they I can't seem to find a way to record a macro that will paste them in one row. In addition to that I also tried copying and pasting each of the ranges of cells individually it kind of worked but it came up with an error box.
The problem is, even if I can find a macro to paste those cells it has to be able to paste them underneath the previous row. So e.g it should paste the cells in row 17 and then when i press the command button again it should paste them to row 18.
I want to run this macro so that it runs through only some of the sheets in my workbook, but it isn't working:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("EXTERNAL").Select
After searching through other threads I was able to gather the following code to copy worksheet titled Week 1 and paste it to the remaining 51 worksheets in the workbook:
Sub CopyCols()
Dim ws As Worksheet
For Each ws In Worksheets
If ws. Name <> "Week 1" Then Sheets("Week 1").Range("A1:N" & Rows.Count).Copy ws.Range("A1:N" & Rows.Count)
Next ws
End Sub
The problem I am having is that I have 3 summary worksheets before the Week 1 worksheet and I need these sheets to not be affected by the above macro.
I am not sure why the following code is changing values not only on the active sheet but also on the "Daily" sheet. I only want it change the values on the "Daily" sheet. What am I doing wrong?
Dim wb As Workbook
Set wb = ThisWorkbook
'Copy Date
Cells(3, wb.Worksheets("Daily").Range("Daily_Column_Date").Column) = wb.Worksheets("Admin").Range("Current_Time").Value
Is there a bit of code I provide after "Cells" to point to only "Daily" sheet?
I have a workbook that I am continually adding and removing pages from. The pages are used together in groups of 3 and as a result are named 1.1,1.2,1.3; 2.1,2.2,2.3 and so on. I have a macro that works on sheet 1.3 and now need it to repeat on sheets 2.3, 3.3 etc depending on how many sheets I have open and active in the workbook. Is there anyway that I can create a macro that will work on all sheets that have titles ending in 3 and then end if there are no more? Thus it would work if my workbook goes to either 2.3 or to 7.3 and so on.
View 14 Replies View RelatedMacro command to run a macro in all sheets of a workbook
View 6 Replies View Relatedwhen I run my mcaro in a worksheet that has 4 different sheets , like sheet1 which I called 1, sheet2 which I called 20A and so on, ....
when I run my macro in sheet 1 I find out that sheet2 which I assigned total in it , " total is not working" , it does everything exactly how I want it except for the total, and I have to run the macro from the sheet 20a itself to have the total show for my, why is that , since all the information all shows right.
I want to know how can I get my macro to run it from any sheet in the worksheet ans still the total for each sheet ( and I do not have to go to each sheet and run it again to get the total for that sheet). my macro is as the following.
I have found and adapted a macro (below) that searches all worksheets for a specific value (entered by the user in a specfic cell) then displays a list of hyperlinks to the matching cell locations.
I now have a couple of sheets I don't want to be included in this macro ("Master List" and "Pivot Table") but I'm really struggling to find a way of amending the macro to exclude these. I've searched and tried a couple of ways but I get errors and I think I'm not knowledgable enough to know where to put each bit of code.
Hi i have this macro below which works perfectly to clear the contents of column A. I will have multiple worksheets all named from 1,2,3,4,5 upto 50.
What i would like to do is modify this script below so it will run on the worksheets labelled above.
If I have a list (in sheet 1) of names listed in column "a" and numbers listed in column "b" and the same thing (on sheet 2) and I want to run a macro that takes the data from (sheets 1 and 2) and lists the matching names in column "a" (in sheet 3) and the number associated with the name (in sheet 1) in column b and the number from (sheet 2) in column c
View 2 Replies View RelatedI 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
I need a simple macro to delete all sheets in a workbook except one that is called "START"
View 9 Replies View RelatedI have a macro and I want to run that macro on multiple sheets. I don't know how to select multiple sheets. I have selected for example three worksheets.
I have used the following code but the code runs only on sheet 10. what do I need to do to make it work on all the sheets ?
Code:
Sub multiplemacro()
Dim wsh As Worksheet
Sheets(Array("Sheet10", "Sheet11", "Sheet12")).Select
[Code].....
I have a macro (see below) that simply formats a sheet for printing. I am currently clicking individually on each sheet in the workbook, and running the macro. Is there a way to have it automatically loop through all the sheets in the workbook?
My current macro is:
Sub Set_Print()
'
' Set_Print Macro
'
Application.ScreenUpdating = False
[Code] .......
I have about 10sheets / workbook, and 80 workbooks to do, all saved in the same folder.
I used this code to sort sheets,
Code:
Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
[code].....
now if my sheets name is like this 001_Tommy, 002_Alex, 003_John, 004_Daniel, 005_Alan,......., and I want to sort sheets name after the underscore "_", arranged like this, 005_Alan, 002_Alex, 004_Daniel, 003_John, 001_Tommy,......, how to do this?
I am trying to get information from two sheets. I have added the lines myarray1 and mystring1. I also added myarray1 in the For Statement. I cannot get it to run correctly. If you take these two lines out, and the myarray1 out of the FOR statement it works for one sheet.
Sub test()
On Error GoTo notfound
myarray1 = Array("A", "D", "G", "H", "I", "End")
myarray = Array("A", "Q", "R", "S", "T", "U", "V", "X", "Y", "Z", "AB", "AC", "END")
For i = 0 To WorksheetFunction.Match("END", myarray1, myarray, 0) - 2
x = WorksheetFunction.Match(ActiveCell.Value, Sheets("phones", "computers").Range("a:a"), 0)
mystring1 = mystring & Sheets("phones").Cells(1, myarray1(i)).Value & ": " & Sheets("phones").Cells(x, myarray1(i)).Value & Chr(10)
mystring = mystring & Sheets("computers").Cells(1, myarray(i)).Value & ": " & Sheets("computers").Cells(x, myarray(i)).Value & Chr(10)
Next i..........................
I have a workbook with some sheets password protected and other sheets not protected.
I have a macro that unprotects the sheets, but it requires the password to be typed multiple times.
I want to put the password into the macro so it doesn't require manual entry of the password lots of times.
Each sheet has the same password, so I would expect to enter it for the first sheet only.
What I have came from a simple macro recorder selecting each sheet in turn:
Sheets("wages").Select
ActiveSheet.Unprotect
Sheets("Comparative").Select
ActiveSheet.Unprotect
I have about 80 sheets in a workbook, and I would like to put each sheet into it's own workbook, and have that new workbook called by the value in Column A.
View 9 Replies View Relatedi use a macro to protect several sheets in a workbook.
When i protect the sheets, i only want to be able to cells and nothing else. How should i mdify the code below to do this? I tried record the macro and un checking the "select locked cells" box which is one of the only 2 boxes checked. It works, but then when i save the wb and re-open it it allows me to select locked cells.
Sheets("FF - WC").Select
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("FF - UR").Select
ActiveSheet.PROTECT DrawingObjects:=True, Contents:=True, Scenarios:=True
I have a macro on: Sheet101 (DAY 1) that adds additional sheets.
Example: When I click active button it will add Sheet102 (DAY 2) & can add up to Sheet130 (DAY 30)
I want a macro that will delete any of the sheets added from Sheet102 (DAY 2) up to Sheet130 (DAY 30) But WON'T Delete Sheet101 (DAY 1)
Example:
If I have added 3 sheets I want it to delete those 3 Sheets:
Sheet101 (DAY 1)
how to code a macro that totals columns d through z on several worksheets that have various numbers of rows. Each sheet has the same number of columns but different numbers of rows.
View 9 Replies View RelatedPrivate Sub Worksheet_Activate()
ActiveWindow.WindowState = xlMinimized
If InputBox("Pleas enter the password: ") "Password" Then Sheets("OkToSeeThisOne").Activate
ActiveWindow.WindowState = xlMaximized
End Sub
This is the code i enter into 3 sheets that i don't want ppl to see unless they know the password but if they press cancel the End on the debug error they can view the sheet lolz. How can i stop them actually viewing it at all?
I have attached 2 workbooks to illustrate the problem I am having.
One workbook contains a receipt. The macro is asking the user to input information into the receipt form, however one piece of information needed for the receipt, the balance due, needs to be pulled in from another workbook which contains the accounts receivable information for each family. Each sheet in this workbook is named the name of a child.
This is where I’m stuck. When the user inputs the child’s name I would like the vba code to search through the sheets in the second workbook and return the balance due for the name entered which is in cell “J42” on the sheet named the same as the child’s name the user entered.
Once it has found that sheet and entered the balance due on the receipt in cell “C10”, I would like the code to copy the amount being paid from the receipt cell “J6” to the next blank row in “Column H” of the Accounts Receivable sheet for that child.
The third workbook I have but wasn't able to include is a database I tried to create listing all the children because I was told that would be the best way to do this, but I couldn’t figure out how to get what I need from that document either. (I think trying to change how I was doing things in mid stream just made it more difficult and I don’t have adequate information in the database document to get what I need.)
The one other issue I have is making sure the user enters the name exactly as it is in the other workbooks, and I thought of having a dropdown box that pulls in the names from the database and having the user select the name from the dropdown box so it will be an exact match, but I have no idea how to have the macro allow the user to choose from a dropdown box.
I'm in the process of placing a button on sheet#1 that will print any of the 5 sheets in the workbook that have been used... a sheets use is determined by cell I53 being > 0...
I've initially used this code to test I53, but it shows all sheets, regardless of I53 value...
Sub PrntUsedShts()
Dim ws As Worksheet
For Each ws In Worksheets
If Range("I53").Value > 0 Then
ws.PrintPreview
End If
Next ws
End Sub
Ideally, the finished code will send all selected sheets collated as a single print job...
Should I be looking at putting test positive sheets into a sheet array?, then printing the sheet array?
Once again, your valued input is greatly appreciated...
I need to have a vba macro code to delete certain worksheets with names such as sheet1, sheet2, ... sheetn., so same name with different suffix. I want to delete these sheets only if they exist in the workbook. That is, the logic is something like this: if sheetname="Sheet1" or "Sheet2" or, ... "Sheetn" then delete.
View 2 Replies View RelatedI have been using a macro for the last while and it has done the job untill recently. It is too slow and I need to speed it up. What is does is looks for a product code in column 3 of the "shelf" sheet and then goes to the "sheet 1" and if it finds it copys accross the information in each cell choosen. There are about 4000 products but the most a particular product will appear is twice. Also the information in columns "e - j" gets copied everytime to "sheet 1" the code is below and i have included an excel file.
Sub One_prices()
'this section does the actual updating of the PLOF/SLOF for 1 prices
Sheets("Sheet1").Select
Dim cf As Range, s1 As Worksheet, s2 As Worksheet, p As Long
Dim firstfound As String
Set s1 = Worksheets("Sheet1")
Set s2 = Worksheets("Shelf_stock_Labels")
'Application. ScreenUpdating = False
p = 2
q = 1
Do While Not IsEmpty(s2.Cells(p, 4))
Set cf = s1.Columns.Find(s2.Cells(p, 4), SearchOrder:=xlByRows)
If Not cf Is Nothing Then
firstfound = cf.Address
Do