I have an Index Sheet where I would be typing the name of the Sheet and a Command button to execute the operation of Adding the Sheet with the name mentioned in the Column C..
View 14 Replieson 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 been running a simulation for about 18 hours now and just received:
Run-time error '1004':
Method 'Add' of object ' Sheets' failed
I have been creating new sheets, importing data, pulling some values from the data then deleting the respective sheet. I am using:
ActiveWorkbook.Sheets.Add after:=Sheets(Sheets.Count)
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...
Can a macro be written that will automatically NAME a sheet with the contents of a particular cell?
View 3 Replies View RelatedIf I opened a new workbook so i had sheet1 sheet2 sheet3... Starting at sheet1, how could i move on to sheet2 using VBA without actually naming it....
Is there a selectsheet.next or something?
I want my charts to be located in a new sheet each. I also need their names to start with "GR-Chart[number here]". So they should be GR-Chart1, GR-Chart2, GR-Chart3,......
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= "GR-Chart" & [this part I got wrong]
Do I use i? or .Count?
I have a workbook with a sheet titled Variables. There are 6 additional sheets in the workbook and each sheets needs to be named with the cell contents of A2:A7 in the Variables sheet. I have attached the workbook and you can look at the names in the sheets tabs to see what I'm trying to do here.
The workbook is too large to attach but can be viewed here:
http://www.shuffleupanddeal.org/name_sheets.xls
or
http://www.shuffleupanddeal.org/name_sheets.zip
I have got this macro working OK but now I need to add a 2 letter prefix/suffix depending on what time it was created (am or pm). Detail: If the sheet is created between 0600hrs and 1800hrs then "DS" and likewise between 1800hrs and 0600hrs then "NS". Outcome: The sheet will then have a name like - "22 Mar DS"...code included below
View 4 Replies View RelatedI have a huge db that has a column "C" with a list of names. I have been creating tabs and naming them according to that list. I wonder if there is anyway I can create and name the tabs automatically. I already searched for threads in the forum, but only found this this backwards "tabs to cells". By the way, the names in column "C" sometimes repeat.
View 2 Replies View RelatedIm sure there is a post somewhere on this forum as I have seen it before but I cant find it anywhere. All I want to do is be able to select Sheets Sun,Mon,Tue,Wed,Thu,Fri,Sat without naming each of the sheets. The post I seen would select all the sheets between the two sheets that were stated in the vba code. something like: Sheets( Array("Sun" To "Sat").select
View 5 Replies View RelatedIs there a macro available which will create a pre-defined name for a pivot sheet, which will contain the word "Pivot" followed by a underscore "_" and the name of the sheet on which the pivot is applied.
Example: I have data on a sheet called "Salary" and put a pivot on "Salary" on a new sheet, the new sheet should be auto named as "Pivot_Salary"
Am using Excel 2013
There is a folder which contains some CSV files. These CSV files are updated say every 5 mins. By updations, I mean new data is appended to these CSV files, keeping old ones. Desire:
1. I want to have one master workbook which will have all of the CSV files in the folder as different sheets in the master workbook.
2. The master workbook sheet should be updated as soon as the corresponding CSV is updated.
My Approach:
Get the list of the CSV files from the directory. Open the CSV files, one after one and copy the newer data, by comparing to a marker that is updated after the new data is read.
We have a scenario like this:
1. Data (Let's say 5 fruit names are entered) will be entered in sheet1 (row wise)
2. Data (Let's say 5 flowers names are entered) will be entered in sheet2 (row wise)
2. Entered data in sheet1 and sheet2 should get automatically populated in sheet3 under respective headers (header 1 - fruits and header 2 - flowers)
Rules : 1. We will have Headers with one default empty row
2. With addition of every row in sheet1 or sheet2, a row should automatically inserted in sheet3 under respective headers and populate data.
I have a workbook with four worksheets. Sheet1 is the master. On sheet one there is information in a 10x10 table. The first column of the table contains a status of "No, Maybe, Yes." A record will start with a status of No and move to Maybe and then yes. What I want to do is be able to dynamically fill sheet2-sheet4 with information from the master. If the status is "Yes" move that row to sheet2. If the status is "no" move that row to sheet 3. Etc. Further complication that is depending on status, I want specific columns from the master, not necessarily all of them.
View 2 Replies View RelatedI have found following example:
This month I will show you some handy formulas used with a twist. SUM ALL SHEETS.
The first one is how we can use the SUM function to Sum, say cell A1, on all Worksheets in your Workbook. With this method, all new sheets that are added to the Workbook are included in the SUM. Here is how;
Add a new Worksheet to you Workbook at the far right. To do this select "Worksheet" under "Insert" on the Worksheet Menu Bar. Then left click on this new Worksheet name tab, holding down the the left mouse button drag to the far right and release.
Name this sheet (double click the name tab) "Spacer" (although any name will do).
With "Spacer" being the active sheet, go to Format>Sheet>Hide. Repeat steps 1 to 3 above, but this time name the Worksheet "Start " and drag it to the far left. On the Worksheet you wish to have the result shown on enter: =SUM('Start :Spacer'!A1)................
I have sheet with data which needs to be sorted out.
I already started with some vba code but am stuck.
Please have a look at the attached file..
Here we go..
I have attached a file for reference. I have another file I use that is similar to the one attached, however it has 500+ rows and is still growing. The issue I am having is that in this file I want to make a "Template" worksheet. Basically I need 50ish sheets that are more or less the same. I have a column that describes a item, a items weight, a row with headers for the columns that I want to appear identical on every worksheet. I want the variables to be the count. These will change from sheet to sheet with formulas to than update the totals.
My issue I am having is that I constantly find myself needing to insert new rows for new items. In my real worksheet I have rows grouped together in a logical way so the rows cannot just always be placed in the next available row at the end of a sheet. How I can have sheet 1 be my "template" and the others take their item lists and weights from sheet 1. In the example file I created sheet 1. I than created sheet 2 and selected a cell entered "=" and selected the cell that I want it to have the same info from on sheet one. I fill handled these items down to generate a list. I than held down control and dragged a couple of new sheets. This process works, and is what I am currently doing, but what do I do when I wish to add a new rob between row 4 & 5?
I have made a text box in one sheet where the user will enter the text that is to be added, and it works! How do I get the exact same text box to be entered into all of the sheets in my workbook?
[Code] .....
i am working in office. my boss has given me 68 work sheets to analyze. i have completed all the task except one. i want to add a capital D to the left of numbers into column B of 68 worksheets. excel sheet is attached. it is only sample. i have more than 6000 record in each work sheet. but how can i do this in only one click.
View 9 Replies View RelatedI have multiple sheets (Sheets "A", "B", "C", etc) with the same structure and formatting. For these sheets ("A", "B", "C", etc.), I want to create dropdown lists in column F (cells F2:F100) based on values from a different worksheet (Sheet "DropDown", Cells "B2:B130").
I saw a previous post [URL]... which had a single sheet example. I am trying to come up with vba code for multiple sheets and so far it's not working.
VB:
Dim wkst As Worksheet
For Each wkst In ThisWorkbook.Sheets
ThisWorkbook.Names.Add Name:="listdata", RefersTo:= _ "=dropdown!$B$2:$B$130"
With wkst.Range("F2:F100").Validation .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=listdata"
End With
Next
End Sub
I am a beginner with vba coding
I will be adding a weekly report to each sheet, which will have varying rows within it, but always the same column headers.
I want a master sheet that will automatically pull all the rows of data through from each sheet, create a new row if needed but merge rows with the same data name and combine the figures.
I want to collect data from multiple files that have multiple sheets and data in them. Basically what I want to do is to copy every 600th data point for 6 times (1 hour) and then move to the next sheet. Once done with the sheets, I want to move to the next file. I had done a similar code before that worked, and now that I tweaked it, it doesn't work. Plus I'm getting a compiler message telling that "For control variable already in use". I have attached the code I'm using below.
[Code] ......
Have two sheets, both very different, but each containing Last Name, First Name and a column for email addresses (one sheet has the email addresses entered, the other does not)
What we are trying to do is look for matches in Last/First columns between the two sheets and if/when found copy the email addresses from one sheet to the other.
Worksheet A: Last=B2 First=C2 Email Address=F2
Worksheet B: Last=A2 First=B2 Email Address=C2
I need to stop users from adding and deleting sheets in a workbook
My idea was obviously to disable the command bars to add or delete sheets in open event and then put back in before close.
But then i thought...whats stopping them from right clicking the sheet tab and inserting a sheet, can i remove that menu to...?
I also wasnt sure if there were short cut keys to add or delete sheets.?
Some of my users use excel alot so i want to account for an tips they know that i might not.
I'm trying to set up a macro that will capture all the unique values in a column, and then create a new sheet for each value. What I've got so far technically works, but it's also adding an extraneous sheet (with a generic sheet name, not the specified name I'm giving the sheets) and also giving me "Run-time error '1004': Application-defined or object-defined error" when it runs.
View 3 Replies View RelatedI 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
[Code] ......
[URL] ....
I want to sort the Inventory Checklist sheet based on Column D but it gives me nothing but references errors.
i have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
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.