I am trying to run the following code on multile sheets in a workbook. so far this code is working fine for one worksheet. Can someone help me modify this so it runs on multiple sheets. There can be more then 1 worksheet in a workbook depending upon data ...
View 9 Repliesi have a macro code but i don't know how to apply it to all sheets in the same workbook
my code is
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("D5:D100"), Target) Is Nothing Then
Target.Offset(0, 1).Value = Now() [code]....
How I can unlock a range of sheets that are password protected in my workbook.
I have 20 identical sheets one for each department which are protected by the same password.
It would be nice if I could rapidly lock and unlock these sheets.
What I need a set rng command in conjunction with a For Next loop?
Am trying to find the VBA code to "ask a user for the password to unhide multiple sheets/tabs"
I have got the script for unhiding all sheets which is:
Sub Sheets_Unhide()
Dim sh As Worksheet
For Each sh In Worksheets
sh.Visible = True
Next
End Sub
What i would like though is for it to prompt with a password first before the code is executed for unlocking 50 odd sheets.. I know its not 100% fool proof, but would do for what i need!
I have a workbook with 10 sheets.
Each sheets has data starting row 14 and column B with row 13 being column Headers.
I want to copy data from each sheet, having column headers suppose A, F & G in to a single sheet. The position of column headers I want to copy are different in each sheets
Means macro will go to sheet1 first, he will copy data from column headers A, F & G and paste in new sheet suppose main.
then macro will go to sheet2, he will copy all the data from column headerA, F & G and paste it in main sheet after the last used row and so on.
I have a folder on a public drive. (\CFDIVIX1PublicPurchasing2009)
This folder contains multiple sub-folders (the sub folders are vendors which I purchase merchandise from) each sub-folder has 1 .xls file in it. Each file has 1 sheet containing all the information I need to retrieve.
Each one of these files has the same header information in cells A1 through I2 so I manually place that when I create this new workbook prior to inserting the macro. Each work sheet has a different # of rows depending on the number of items I buy from that particular vendor but I feel safe saying it never exceeds 250 rows per worksheet.
What I need
For the macro to pull up the first work sheet and pull out the data from cells A3 through I250 and place that data into my new work sheet in the same format. Then I want the code to move on to the next worksheet and pull out the A3 through I250 and place that data in the new worksheet directly below the last row of data that was pulled in. (Also it would be great if the code ignored blank values. Like if the first work sheet it pulled data from only had data in the first 10 rows I don’t want 240 rows of blank rows before the next set of data)
I hope that running this macro will accomplish transferring all of my data from these multiple sheets into a single work sheet containing all of the pricing for all of my vendors. Once I have this I can place a second sheet on the workbook and use a formula to pull out pricing information that I need on a per basis without having to search through 50 vendors to find who it comes from. I don’t control the vendor work sheets that I want to extract data from so I need to use the macro to run it weekly incase my purchasing dept makes changes.
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
I found the code below and it works perfectly if I want to copy all the other sheets to a master sheet. But, I need to specify specific sheets. Basically I have a workbook consisting of multiple sheets and multiple "master" sheets so I need to specify in the code which sheets it should be copying.
[Code] ........
I havae the following macro which i recorded in Excel. I want this Macro to run after another macro that groups data and creats tabs. The following macro will then add a column and run an array formula. I think this can be done in a loop but i'm not sure how to do it. This is working but takes a long time and times out by the time it reaches the last tab.
Application.Run "TotalHrs"
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("I4").Select
ActiveCell.FormulaR1C1 = "Invoiced Amount"
Range("I5").Select
Columns("I:I").EntireColumn.AutoFit
Selection.FormulaArray = _
"=INDEX(VLookup!R2C2:R242C4,MATCH(RC[-3]&RC[-2],R5C6:R2500C6&R5C7:R2500C7,0),3)*R[3]C[-1]"
Selection.Copy
Range("I6:I1000").Select...................
How do I change the code so that the macro looks up the next sheet (instead of by sheet number, which is how the code was recorded) and transfers data to the summary page until there are no more new sheets. See attached zip.
View 6 Replies View RelatedI have got the following code to fill series and someone please help me that how to use this macro I can enter thses code in sheet1 on enter event and the result iwill be displayed in sheet2 and if any duplication in series then it is copied in sheet3....
View 9 Replies View RelatedHi 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.
I 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 multiple sheets and a summary sheet in the beginning. and i need to populate the min value / max value and the avg value for every sheet into the summary sheet.
Example i have a column of numbers in column G and i need the min , max and round(avg) for all sheets in the first summary sheet.
I want to consolidate the various employees' salaries of all months in a sheet. I enter salaries in different sheets month-wise and in each sheet, department-wise. Some employees get commission in various departments. Now, I need to see the details of an employee by giving his name. I should get month-wise his salary, commission and department in which he get commission, across all the sheets.
View 1 Replies View RelatedPrint sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.
(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)
I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:
I have an excell spreadhseet that has more than 100 sheets and I would like to combine all these sheets into one master sheet (Sheet1 = MasterSheet) within this workbook. Each sheet has different number of rows used. I just want used ranges to be copied over to a master file appending the previous copied range.
Sub MergeSheets()
Dim strSheet As Object
Dim LR As Long, LC As Long
Sheets("Sheet1").Name = "MasterSheet"
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
For Each strSheet In Sheets
If strSheet.Index 1 Then
[code]....
I am writing a macro that organizes data, but also needs to be able to conditionally copy data from other sheets in a workbook.
I have a table with values...column A has the identifiers (strings of letters) and column N has comments related to the data in each row. So, for example, row 3 column A has the value "AAPL," and row 3 column N has the related value "sells electronic goods."
Now, I want to be able to copy column N based on the value of column A into other sheets using my macros. So, say I have another sheet in the workbook and row 6 column A has the value "AAPL," I want row 6 column N to have the related value from the previous sheet. Is it possible to do this?
I have a macro listed below that I would like to use on multiple sheets within the same workbook.
Sub PrintMacro1()
'
' PrintMacro1 Macro
' To change print format from landscape to protrait
[Code].....
What would be the macro if i need to hide multiple select sheets? I'm working on a test with 12 sheets. Even number sheets contains the fields that they need to answer and the Odd number sheets contains the formula for score computing including the answers so it needs to be like this:
Sheet 2 - The test
Sheet 3 - Must be hidden
Sheet 4 - The test
Sheet 5- Must be hidden
Sheet 6- The test
Sheet 7 - Must be hidden
I already have the code to unhide all sheets, just need the macro to hide specific sheets like the ones above.
I am creating a vacation calendar for all of my associates. I have 763 employees so, i want to run a macro that will react a worksheet for each of them. I will have an employee list that will create the sheets and I will have a VLOOK Up to update and pull information when we have new hires come on board.
View 9 Replies View RelatedI have to run the macro in over 75 sheets every month and they are divided in few workbooks. The workbooks have 2 summary sheets and then the sheets I need to run the macro. Can you help me to run the macro in all of these workbooks running it just once?
Sub TelcoTicketsCleaning()
'
' TelcoTicketsCleaning Macro
' Macro recorded 6/13/2007 by EQUANT
'
Dim lastrow As Long
lastrow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For r = lastrow To 46 Step -1 'Remove rows with DSL, ISDN and PSTN services..................
I would like to run the code below on selected sheets in my workbook:
Range("B9:AW38, AZ9:BE38, b3").ClearContents
I tried using this but it doesn't work:
Sub clear()
Sheets( Array("sheet A", "sheet B", "sheet C")).Select
Range("B9:AW38, AZ9:BE38, b3").ClearContents
End Sub
I have a spreadsheet with over a hundred tabs, each of which need to be password protected (same password).
I found code on CFO.com that got me half way there. It protects all the spreadsheets, but it does not password protect the macro itself, meaning that anybody can step into the macro and unlock everything at the click of a button.
The code looks like this.
VB:
Sub ProtectAllSheets()
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="secret123"
Next ws
MsgBox "All Worksheets Protected"
[Code] ....
What can I do to prevent people from stepping into the macro, or prompt a password to actually use the macro itself?
Sorting.xlsx
I am trying to figure out how to make a macro that can sort some numbers from Largest to Smallest within a specific range on multiple sheets. The range is only within column D starting with cell D11: (until the data ends) on all the sheets in my workbook (the number of sheets may change with time) except for Sheets: "A", "B" and "C".
In other words I want the sort to work on all sheets except the first 3 sheets which are named Sheet A, Sheet B, and Sheet C.
I have attached a spreadsheet for an example of what I am saying.
I have the following macro, however I am struggling to make it work on my file:
[Code] .....
I have attached my sample. essentially i have the dec, jan etc month tabs to consolidate into the summary sheet. i would like the data to be dumped from both sheets into the summary sheets. i will be adding a feb, march, april etc tabs as the year progresses.
I would also like column A in the summary sheet to repeat the name of the sheet the data is being retrieved from.
Attached File : sample macro_issue01.xlsm
I got a quite huge excel file with multiple sheets. For convenience sake I want to group and hide all the sheets not necessary for the viewer.
View 14 Replies View RelatedThis Macro works fine to copy data from multiple sheets into one master sheet, but it is also copying the formula. How can I change this Macro so that it is copying and pasting VALUE only?
Sub Combine()
Dim J As Integer
On Error Resume Next
' work through sheets
For J = 4 To Sheets.Count ' from sheet 3 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A10").Select
Selection.CurrentRegion.Select ' select all cells in this sheets
' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets("Combined").Range("A65536").End(xlUp)(2)
Next
End Sub
I'm trying to create a workbook with multiple worksheets. My first sheet to be named "Main" and each additional sheet to be named in sequence after the values in the cells between B5:B98 on !Main.
Example:
B5 = "01"
B6 = "01.1"
B7 = "01.2"
B8 = "02"
B9 = "03"
etc...
I'd like to have a macro that would take a worksheet in this workbook named "Template" and make a copy for each cell between B5:B98 and name that new worksheet "c" and then the value in each of those (B5:B98) cells.
Example:
First worksheet named "Main"
Second worksheet named "c01"
Third worksheet named "c01.1"
and so on.....
I have a large workbook with 10+ sheets i need to protect all at once. I have created a macro in VBA below. It works, but I would like another user to be able to change formatting on the sheets while they are protected. below is my macro.
Sub ProtectAll()
Dim wSheet As Worksheet
Dim Pwd As String
[Code].....