i created a sheet and called it "Original" (which somehow is a template), in another sheet i created a big Button named "Create account" ,then i created a macro which copies that "Original" sheet and by default it names it "Original 2 " now that i need a new account each time i click that button to create a new sheet and rename it by the client name i need, in order to keep the "Original" intact as a template. It happens sometimes by mistake that i rename the "Original" one ( The template one) which therefore returns an error when i click the button!
Is there anyway to add to that macro a code which rename that "Original 2" something else lets say "Account" to differentiate it from the Template one?
Or to Protect only the "Original" sheet from being renamed out of the whole sheets i have , in order not to rename it accidentally?
If you look at the file I attached you will see the tabs at the bottom are months of the year. I know how copy a sheet but every time I make a new month tab I have to retype the month in the top left corner of the table, in the tab, and other places. This gets really repetitive when I have to do this for literally around 20 other spreadsheets like this.
What I'd like is a way to create new month tabs that automatically rename the month and year in chronological order on the sheet and tab if possible. 2 months it's because I'm constantly having to tweak spreadsheets for my boss. It'd be pointless to go ahead and make a template because then I'd have to go back and change the code for every month. So I just create tabs and edit code month by month.
The below code opens up the printpreview window. This does not allow to select the correct (or change) printer it will automatic use the default printer.
Is there a way for the code to open the print window so I can change the printer and select number of copies?
Code:
Sub Print_Button() Dim ws As Worksheet, cell As Range Set ws = Sheets("main") Set cell = Range("g2000").End(xlUp) Do Until cell.Value "" Set cell = cell.Offset(-1, 0) Loop ws.PageSetup.PrintArea = ("A2:" & cell.Address) ws.PrintPreview End Sub
I have a list of references in a range (A5:A98) in a worksheet called "Architectural". What I am trying to do is create a seperate worksheet based on each cell value in the range. However, rather than blank worksheets, I would like each new worksheet to be a copy of a worksheet called "Template". If possible, I would like to have the name of each worksheet also put into cell B2 of each copy.
I have a table that lists names, eligibility and # of people. That is just one worksheet. I have a total of 30 worksheets in that workbook. The column with the names is completely filled out. I entered a number in the eligibility and # of people columns and Excel copied all three cells (name, eligibility and # of people) to the same cells on all of the worksheets. I have no formula in any of the cells, nor do I have anything linked.
I'm looking to automate the process of repeating an excel worksheet (which is set up with various tables and formulas) for various countries.
I have a referenced list of countries set up in worksheet 1. So for example: in worksheet 2 I have a sheet set up for Albania - and I want to repeat this for another 100 countries. . Can I use the reference list of countries to auto-repeat worksheet 2 for worksheets 3 - 102 and auto-title the worksheet tabs? (E.g. Brazil, Canada, Chile..............)
I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.
Is there a way to either change this so that it lets me to select the whole area or a way to make a macro to do what this does to one cell?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("M13:IR458")) Is Nothing Then Select Case Target.Value Case "1" Target.Font.ColorIndex = 20 Target.Interior.ColorIndex = 10 Case "Good" Target.Font.ColorIndex = 2 Target.Interior.ColorIndex = 35 Case "Stable" Target.Font.ColorIndex = 2 Target.Interior.ColorIndex = 27......................
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I have 5 opened files. I have copied into those files a set of formulas from my master sheet (with the macro). In A3 of ALL the open files, there is a date (ex. Date: 1/4/2014) Based on that date I want to copy the result of what I copied into the files (below is the code that I used to copy into the open files, maybe I could add something to create a named range of the resulting paste) back into the master sheet into a tab called 2014 and into a cell two cells below the cell in row 12 with the MATCHING date.
I need an excel macro that will copy the last row of data from one worksheet within a workbook to another worksheet in the same work book. The last row number of data will alwasy vary
I am looking for a Macro, which would first ask me to input the member notation in a new tab (Say M1 in the example). After I give the input as M1 or M2 or M2 or so on, it would display only rows for Section 1 (min and max values) and section 4 (min and Max values)(highlighted in Red). I know it is petty simple, but I am still an amateur with regards to Macros.
Trying to create a spreadsheet to track transactions. Sheet "A" is the working file with a limit of 25 rows. When a transaction is complete, I would like a macro button that copies one of these rows (1-25) to an archive file (Sheet "B"). I would like it to copy and paste to the next available row on Sheet "B".
I am trying to make a macro, but because of my inexperience, it's not working out. I have got a big list (list 1) of numbers in column E. each number has some information in the cells of columns C, D and L, all in the same row. on the same sheet, I have got another, smaller set of numbers (list 2), also in column E but at the bottom of the sheet. the numbers in list 2 are all present in list 1 (but not all numbers in list 1 are present in list 2). the columns C, D and L on the same row of the numbers in list 2 are empty...............
I have a sheet called Summary. On that sheet, Cell O6 has a drop down with two options, when you change these options, a number of other cells on the same sheet automatically change (just using formulas). Including a cell that I've given the named Range of 'testCell'. Based on the drop down, test cell will either = 8 or 9.
What I also want to change is the format of a range of cells whenever O6 is changed - but only when O6 is changed.
However, the following code does not work. It works fine if i remove the 'If Target.Address = "O6" Then ...' but doesn't work with it included.
Code: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "O6" Then If Range("testCell").Value = 8 Then Range("P10:AD27").Select Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)" Range("O6").Select
And I also have two cells which are designated as the low and high range. (ie C1 = 85% and C2 = 100%). I need help with the macro for the button that will copy only the rows to a new sheet that fit within the range. So in the example above, pressing the button will create this in a new sheet:
I want to create a macro that will allow me to create a set number of copies of a specific sheet within the workbook. I have one sheet within my workbook entitled "Tab". I want to create a macro that will ask me how many copies I want to create and then proceed to create all the copies. i.e.: select to create 4 copies, with the result being: Tab[original], Tab(2), Tab(3), Tab(4), Tab(5)
I have a worksheet change macro that from the insertion of date (dd/mm/hhhh) in a cell, fills some other cells with the day number, the week number, the month,...
Everything works fine when i fill one date after the other. But when I try to fill many cells with the same day by dragging the black cross at the right corner of the cell the macro is not activated. how to activate a worksheet change macro by dragging the black cross as a mean to fill many consecutive cells in a column?
Say i have a Worksheet named "gateway" or sometimes it will be "gateway (2)" (3) and so on. Is there a macro that i can call that in some ways calls the active worksheet and renames it to just "gateway" everytime?
I'm looking for information on building a macro that will automate making copies of a spreadsheet into another workbook that contains very particular information. My master sheet has lists of my customers and the rep's that service them. What I need to do is have macro buttons for each rep. that will pull all the shops that they service and all information for these shops into another spreadsheet.
I am trying to have a macro that separates a list with unique values in column a, copy the results, open a file name in column AG, and then paste the copied stuff into the open workbook.
Currently, it runs, but when I copy to the new sheet, the thing I want goes where I want it to, but I also get an extra sheet1. how can i change this so the sheet1 doesn't get pasted in?
Sub MC() Dim r As Long, rng As Range, ws As Worksheet Application.DisplayAlerts = False Application.ScreenUpdating = False ActiveSheet.Name = "Sheet1" With Sheets("Sheet1") Sheets.Add().Name = "temp"
the following code filters data and copies to worksheets specified in code, can this code be shortened to make the macro run faster. Also in my second criteria how can I put "Contains "PT2" in the criteria as nothing happens when i run the macro, is *PT2?
With shtRec .AutoFilterMode = False .Range("A7").AutoFilter Field:=8, Criteria1:="LEHMAN", Operator:=xlAnd With .AutoFilter.Range On Error Resume Next Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12) On Error GoTo 0 End With If rng Is Nothing Then MsgBox "No data to copy" Else rng.Copy shtLehman.Range("A6") End If .AutoFilterMode = False End With
The intent of the code below is to run the marco HideCol when a change is made in cell C10. If I actually change the value in C10 it works. However, I have C10 linked to another worksheet (=anotherpage!A13). When the value changes, my macro is not executing.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim changed As Range Set changed = Range("C10") If Not Intersect(Target, changed) Is Nothing Then HideCol End If End Sub
In Col D of my spreadsheet, I have a list of security codes, in this list there is a security code "all", i need a macro that will add 1 to the code, so it reads "all1", now i need the macro to run as soon as new data is pasted to sheet "Lending", the ranges in Col D do change on a daily basis therefore cell reference for security codes is not fixed.
When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?
Original Sub sessions() Dim a As Integer Dim x As Long x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
Sheets(1).Unprotect Password:="password"
For a = 2 To x
If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then Cells(a, "f").Resize(1, 6).Select Selection.Locked = True ElseIf Cells(a, "m") Cells(a, "d") Then Cells(a, "f").Resize(1, 6).Select Selection.Locked = False
