I currently have an Excel Document (Property Front Sheet Template) which has the following 4 sheets:
Front Sheet - This pulls data from the Pivot Table and Logo Sheet (this is like an Estate Agents cover sheet for a Property with a Picture and basic info pulled from the Pivot table)
Pivot Table - Info is currently pulled from Report Tab (Macro below is used to then populate the correct Logo based on business name and corresponding Logo on the Logo Sheet.)
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim PicName As String
Dim Pic As Picture
PicName = WorksheetFunction.Index(Worksheets("Report").Columns("S"), WorksheetFunction.Match(Range("B2").Value, Worksheets("Report").Columns("C"), False))
With Worksheets("Front Sheet")
For Each Pic In .Pictures
[Code]...
Report - Currently I paste in a Report
Logo - This sheet has Logos of various businesses which is then used to populate onto the Front Sheet.
What I want to do is have a Report that runs (i.e File name: Property Schedule) that replaces the Report tab and will then insert the Pivot table, Front sheet etc from the Property Front Sheet Template.
Maybe I'm over complicating this with my explanation but want to give you as much info as I can, so in the end I will run a Report which will then produce a Front Sheet and Pivot Table.
I have a code which checks if the a worksheet exists, and if not it will add it, but I need to do this for two different sheets and I am stock.
Code: Sub check() Dim sh$ On Error Resume Next sh = Sheets("Quarterly").Name On Error GoTo 0 If sh "" Then Sheets(sh).Activate Else Worksheets.Add.Name = "Quarterly" End If End Sub
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.
I need a coding for count of "Reconciled" items and insert that number of template sheets. Attached is the sheet for your better understanding.
With the help of attached example I need a coding which will count the number of items the word "reconciled" appears in control sheet (L:L) and should insert that number of template sheets. In this example the count is 16 so it should insert 16 new template sheets. Pls note the count would not always be the same and count would not always be in cell L123.
After inserting the template it should be renamed as the combined value from the cells. for example from control sheet the template should be renamed as from E8,D8 & F8 First should be affiliate then subdivision and then currency. It should include "-" between the names.
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.
One of the sheets in my workbook has 5 rows (Rows 1 - 5) that I need to copy and insert at the top of all but 3 of the other worksheets. I can specify the names of the 3 worksheets that I want to avoid changing. The other worksheets will be named differently each time I use the macro.
On sheet1, I want to insert a Vlookup formula that will look in the other two sheets and will return a value (say the variable Y) if two lookup values are met and return a blank if they are not met.
Example. Sheet1. CellA1 = apple CellB1 = 2. I want to search Sheet2 and Sheet3 row by row and if Sheet2 has A and B cells which correspond(a row with "apple" in column A and "2" in column B, return the value Y. If not, return blank. Sheet2 and Sheet3 will never have duplicate values (and if they did, returning a Y would be fine).
One is the master and the othere sheet which has the raw data.
On each sheet you have a unique code for the product which is the same on both sheets.
From the raw data sheet i want to the weight for that product to be inserted onto the master sheet.
(the problem is that both sheets are not in order so you cannot just copy and paste the column)
the unique code on the master sheet is G9 and the unique code on sheet 2 is A27 the weight on sheet 2 is r27 and want the corresponding weight for the product to appear on the master sheet in column BI9.
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.
Sub AddSheets() Dim strName As String strName = Sheets(1).Range("A1") Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = strName End Sub
I have three worksheets that I am combining into one master worksheet. The data from Worksheets 1 & 2 are listed combined under the same headings onto the master worksheet. I have three additional headings on the master that need to populate information from Worksheet 3 under them. I would use VLOOKUP to populate those fields if it weren't for the problem that some asset numbers have more than one entry. The request I received is to insert copy the criteria row below it so that there are an equal number of entries for each occurance of information from Worksheet 3 to copy that data to.
(example = Asset 12345 has 3 different work orders on Worksheet 3, therefore I have three rows for Asset 12345 on the master with the data I need copied under the 3 headings.)
To figure out how many rows to insert I created a last column (that I will delete at the end of the macro) that uses COUNTIF to count the number of occurences of the asset number on Worksheet 3. I want my macro to look down this column and for every value over 1 add rows equal to that value-1 beneath the row. I then need to copy the information from the criteria row into the newly inserted rows. I will need to figure out how to populate the data from worksheet 3.
I tried recording a macro of me using FIND >1 values while selecting the column containing criteria and it didn't work because it likely was literally looking for ">1".
I have a master data sheet with four columns, A, B, C and D
Column A has the primary data and B,C,D has dependent data values;
So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;
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
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...
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.
I have received following macro from someone to delete series but the problem is that it gives error when some sheets are protected, and I want those sheets to protected. When run it will ignore/leave protected sheets but delete series only from unprotected sheets
Private Sub CommandButton1_Click() Dim Rng As Range, i As Long, r As Range, lVal, uVal Dim DeleteCount As Double Dim lRow As Long Dim dr As Long Dim dc As Long dc = Sheets("Deleted Numbers").UsedRange.Columns.Count - 1 dr = Cells(Rows.Count, Sheets("Deleted Numbers").UsedRange.Columns.Count - 1).End(xlUp).Row + 1 If dr = 60001 Then ................
on sheet1 I have a button I need to do the following when clicked: (1) name the next 30 sheets based on cell values in sheet1 (2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
I have merged 336 individual spreadsheets into one book, now I want to merge the data in all the sheets into 1 individual sheet. All the sheets have the same size and range, I need to copy a constant range(row,column) from all the different sheets into one.
I have a workbook with 26 sheets, labelled A to Z. Column A in all the sheets have names from rows A6:A35.
I need a macro or a code to extract all the names from each of the 26 sheets and paste it to a new sheet 'Names' under column A, such that names starting with 'B' paste under all the names 'A' and so forth till 'Z'.
I have copied and then pasted & linked numerous cells from one sheet to another within the same workbook. When I copy the 2 sheets (Edit>Move or Copy Sheet>Create Copy) the linked cells on the duplicates remain linked to the original sheet. How can I copy the 2 sheets and have the cells on the copied sheet be linked to the copied sheets and not the original?
i wanna copy C and D columns in Sheet1 and insert these 2 columns (copy + insert copy cells function, not copy + paste) to all other sheets except Infopage sheet
I want to create a hyperlink to a sheet named "adsf"
I am currently in a worksheet named: "62b Arcus"
I want the hyperlink to be set by grabbing the name from another cell.
For example, In cell h7, I have the text: adsf
In cell g7, I want to place a formula such as: =HYPERLINK("adsf!")
Except, instead of this, I want: =HYPERLINK("h7!")
In this way, i want it to hyperlink to a sheet name based on the text that is in h7.
But neither of these formulas work. Both say the following: "Cannot Open the Specified File"
After reading up on this I have discovered that I must save the file and include the file name inside the formula.
My file name is: [Maintenance Color Codes of Houses - colour coded2.xlsm] =HYPERLINK("[Maintenance Color Codes of Houses - colour coded2.xlsm]adsf!A1","LINK")
This hyperlink actually works. Yet I have a problem. What if I rename the file. For this reason, I want it to grab the current file name using "filename". I have tried this by the following:
I know that I have probably created a ridiculous formula for what I am after. I'm almost there but not quite. You may know something far, far more simple.
I run into is that many of our staff don't know there are tabs at the bottom of the page that show different worksheets. writing some VBA code that would grab the names of all the sheets and create a single sheet with the tabs listed as buttons that would hyperlink to each sheet of it's given name. Kind iof an index for the sheets.
Also bonus if 3 rows could be insterted on each of those pages with a button that links back to the index page.
