I found a macro code in this forum (Macro: Insert Sheet & Name As Cell Text first empty cell it should stop, but I do not know how to do that. (for example: If cells text is: A1: DOG, B1: CAT, C1: MOUSE then macro should create worksheets named DOG, CAT and MOUSE). I would save code to macro.xls file, in that file it would be also worksheet named LIST with cells names A1: DOG, B1: CAT, C1: MOUSE... in first row. But I would like to run macro on other files, so this new worksheets would be created in that new file and not in macro.xls where where macro and LIST are saved.
Dim strName As String
strName = Sheets(1).Range("A1")
ActiveSheet.Name = strName
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.
Attached is what I use as a Calendar Macro and it works great for single active cell use.
I have a column for 'Notes' and I can only use 1 cell per item so every time I have an update about a specific item I go the to notes section and put a date and leave few spaces then type in whatever the notes are. Currently I'm doing it manually and I tried to use my macro on the Notes column and what it does is to remove everything in the cell and replace it with whatever date I choose in the calendar.
find a way to use my current Calendar Macro to add a date in the cell instead replacing everything in the cell with a date.
I need to figure out a way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.
way to automatically insert rows and copy data on multiple selected sheets. for example, if i insert a row anywhere on sheet 3, i need that same row inserted in the same location with the cells populated with the same data on sheets 4, 6 and 9.
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
I use the following macro (found it in a forum) below to insert photos in Column A of my worksheet and it works great! But I need a MACRO that will load hundreds of photos all down Column A. what code needs to be added in order for that to work? Im just a novice and have been searching for a Macro I can copy that will let me load hundreds of photos automatically.
I have two sheets that i am working with sheet1 and sheet2.
I need a macro that will insert multiple rows in row 8 of sheet 1.
I also need it to copy the formula from row 8 sheet1 to every new row inserted.
the number of rows inserted will depend on colum A from sheet2.
*column A contains numbers, and the amount of numbers will vary. The data starts in A11 and continues down.
So for example. if sheet 2 column A11 and A12 and A13 are the only cells that contain numbers, then the macro will add 3 lines starting in row 8 of sheet1 and it will also copy the formulas from that row to every new row inserted.
I'm trying to figure out how to create a macro for a project at work. Basically, think of a spreadsheet with 5 tabs, but the information in Tab 1-Column D is the same in Tab-4 Column D and Tab-5 Column D. When I insert a row, though, I have to go to each tab, insert the row, and copy down the formulas from the row above to ensure the flow-through stays true. This can get very tedious.
Does anyone have a template or tips on a macro that would, in essence, work like this:
a) Highlight the row above which a row should be inserted b) Trigger the macro c) A row is inserted above the highlighted row in Tabs #1, #4 and #5 d) The information from the row above the inserted row is copied down to the new row in each of the three tabs.
I am trying to write a formula to insert in a macro for multiple lines of data.
Column B contains the date that I am comparing the date in Column C against. I want to be able to highlight the content on that row if the date in column C is greater than or equal to column B. how to write this?
I'm trying to track information pertaining to employees across different worksheets in one excel file. I have one sheet that is the master list of employees. The first column of every worksheet is the same (employees names based on their location) but track different information.
I want to be able to create a macro button that will update all the worksheets if i insert or delete an employee from the master list. When a new row is inserted, the other worksheets should be updated as well with the new name and a blank row to be filled in. And when a name is deleted, the entire row should be deleted as well.
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.
Print 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
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?
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.
I 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 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 ...
I am trying to automatically insert multiple (100) worksheets using VBA. The names I need each worksheet to be named are in a list in a separate worksheet in the same workbook. I found this site: http://www.mindspring.com/%7Etflynn/excelvba3.html and have been trying to adapt the following
Sub AddSheetWithNameCheckIfExists() Dim ws As Worksheet Dim newSheetName As String newSheetName = Sheets(1).Range("A2") ' Substitute your range here For Each ws In Worksheets If ws.Name = newSheetName Or newSheetName = "" Or IsNumeric(newSheetName) Then MsgBox "Sheet already exists or name is invalid", vbInformation Exit Sub End If Next Sheets.Add Type:="Worksheet" With ActiveSheet .Move after:=Worksheets(Worksheets.Count) .Name = newSheetName End With End Sub
I am having difficulty iterating the code from cell A2 to A102.
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"
What can I do to prevent people from stepping into the macro, or prompt a password to actually use the macro itself?