I have a workbook with about 52 worksheets, I have a script that will unlock the protection from all the worksheets. However the problem I'm having is the script runs and starts and ends at sheet1 I want the script to start and end at the sheet I run the script from lets say sheet 40?
View 4 RepliesI am trying to copy some data from on sheet(sales) to another sheet(totals) and later will try to copy to all the worksheets in my workbook
I am using the following Sub macro2()
Sheets("sales").Select
Range("c16:c17").Select
Application: CutCopyMode = False
Selection.Copy
Sheets("totals").Select
Range("b2:b3").Select
Active Sheet.Paste
End Sub
the data in cells C16 and c17 is 2000 and 1850. it shows an error: sub or function not defined
I'm trying to get a loop started to perform tasks and then go to the next line if it has info in it and do the same thing, until it gets to a blank line, and can't seem to get it right. It's been a while since i've written macros, so i'm sure i'm doing something completely wrong.
View 4 Replies View RelatedEvery time I open my excel spreadsheets the "Getting Started" window opens automatically. How do I turn it off so it does not open automatically
View 3 Replies View RelatedI've a set of tasks in Column A which I would like to measure. At times, Users flip from task A to task B then back to A and so on. I would like to be able to pause a timer on task A if when timer on task B is started and same as well with the other tasks.. When Task A is started again, it should continue the timer if not add the on-going time to the presently running time under that task.
View 2 Replies View RelatedI start my excel program from a "Shortcut" in "Folder 1".
That shortcut points an Excel program in "Folder 2".
When Excel is started,
Parent.Path returns "Folder 2",
but I really need to know what "Folder 1" was.
It seems that should be available as
when I do "Save File As"
it is pointing to "Folder 1".
Is there a way to find what "Folder 1" really was from the VBA enviornment.
I have 4 columns: X, Y, Z, AA.
X - Start month e.g. 1, 2, 3, etc
Y - Start year e.g. 2006
Z - Finish month e.g. 1, 2, 3, etc
AA - Finish year e.g. 2006
In column AK, I need to return a value, whether it is PLANNED, STARTED or FINISHED.
PLANNED is if start date and completed date are both > than now()
STARTED is if started >now() but completed date is > now()
FINISHED is if completed date is < now()
Issues:
1. Start date is computed using Y as year, and X as month and the day is assumed to be 1. Same princilple for the finished date.
2. Column X, Y, Z, AA can be blank. If month (X and/or Z) is blank but year is not, then date is beginning of the year. If either start year is blank but finish year is not, then status should be based on the finish year and vice versa.
3. If both year (Y and Z) are blank, then status should be "NOT SPECIFIED".
I am using the formula: LOOKUP(A1,{"S","M","L","XL"},{"S","M","L","XL"}) on a cell containing: EF-2147.(S) so that it will return a S [or M, L etc. if it were .(M),.(L)] but it just keeps returning #N/A. I tried putting in "(S)" etc. in the lookup formula but it then started returning the wrong letters.
View 2 Replies View RelatedI am, and have been using several sum- and count-ifs formulas for a time, but when the new year rolled around, decided to improve some of the sheet.
That all being said, the issue started after this. A specific set of my count and sum if functions started returning error values.
I analysed them, they seemed fine, and then selected them and hit enter. Problem over in that cell, as suddenly the correct value is returned.
But that's just it, I know the formulas are correct and working, as, if I select and hit enter on them individually, they return the right value. However, every time I close the sheet and re-open, all the values are showing error again.
I am perplexed. Did I inadvertently change a setting somewhere when I rebuilt the worksheet that is preventing the formulas from resolving correctly? As, like I said before, the formulas are fine, the data they are resolving, is fine. Just does not seem to calculate when I open the sheet.
I have a VBA macro that I pieced together* a couple of years ago to help with a pretty simple task. We have two excel sheets that pull in information daily from different sources. The macro copies and paste values for the worksheet, then 1) saves a copy of the values only worksheet with the current date, 2) resaves the values only worksheet as a .csv file that is then manually uploaded into our database.
View 3 Replies View Relatedi am looking to do a table which shows time started, time finished and then a total for hours that day, then that week.
Start 08:00
End 16:00
Total 8 hrs.
How can I get the total to display as 8 hrs? not 08:00? When I change the format to "number" it shows 0.33?
I am not sure that I can do this, but here is what I would like to do. I have a worksheet that I initial when I start a job in on cell and then when I finish in another cell. What I's like is to have a macro running in the back ground that will tell me the total elapsed time from when I started to when I finished.
View 5 Replies View RelatedI have set up a workbook with sheets named for each month.
I have 70+ separate excel files (one for each month) that each have a single sheet I want to copy into the new workbook's corresponding month sheet.
Each file is named different but the name of the sheet in those files that I need will all be the same.
Is there a way I could sit in the sheet I want it pasted to, run a macro that I can point to the correct file/workbook -- preferably without opening -- and then have it copy the contents of the sheet I want (again, will always have the same sheet name).
I want the sheet copied with formatting and all, an exact copy.
This link seemed useful - if i could control which sheet it copies more easily since for me the source data always has a different file name. [URL] ........
I thought I lost that original message so I rewrote everything - differently - then I saw the restore button so I'm leaving both phrasings.
I would like to know if there is a Macro that could ask me which workbook to copy from, select the sheet in that workbook called CAP (sheet will always have the same name but I need to copy the sheet from 70 different files/workbooks) copy the whole sheet with formatting and all, and then paste it in my active sheet on the new workbook I am in.
I want to take data about a particular partner we have (CAP) out of the monthly summaries for the last ~72 months (which each have their own file/workbook) and put them all in one workbook just for this partner, on each sheet by month still.
I am only interested in automating everything I have to do for 1 month, but in a way that would easily let me do it again for the next month, and the next and the next, 70 times.
But a Macro would still be easier than opening each file, going to the sheet I want, copying everything, going back to my new workbook, pasting everything, closing the extra workbook, then doing that ALL again 70 times.
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies View RelatedI need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
Workbook contains the following sheets : PIR TrackerChartsSAMPLEFINALValidations
When a change occurs on PIR Tracker, the following occurs:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim Rng As Range
Set Rng = Intersect(Target, Range("A1:A500"))
[Code] .....
I also want the pivot tables on SAMPLE and FINAL to be updated. What do I need to do?
I've got a spreadsheet with 2 worksheets in it.
On the first one we've got:
Name of the agent | petition REf num | Task
Each petition can generate several tasks, one line per task.
John Doe | XXXX-YYYY | NCO
John Doe | | RIL
John Doe | XERT-WWWW | RMT
Jane Doe | QSZE-AQWC | RIL
On the second worksheet:
Complete list of agents | number of petitions | Status
John Doe | 2 | OK
Jane Doe | 1 | [BLANK]
I want to be able to fill in the second worksheet automatically. For each agent in my worksheet 2, I want to check if they appear in worksheet 1 and if so count the number of petitions related.
I don't know where to start
I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.
View 11 Replies View RelatedI'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.
This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.
I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.
I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.
I have an order form created in excel with a list of about 1600 products. I have a column set up for the customer to place the ordered amount of each product. What I need now is a way to transfer only the rows that have a value in the "ordered" column to a new sheet. I have seen it before , but don't know how it was accomplished.
View 1 Replies View RelatedDropbox - Final.xlsm
Here is the above link. Am looking at a button which saves whatever is the temp worksheet row in the customers worksheet. The temp worksheet basically takes the data from the Quotations worksheet and places it in a row.
The new worksheet is created to the left of the existing source worksheet.
View 2 Replies View RelatedI am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
I have created an excel worksheet that will provide budgeting and estimating tools for my project managers. All data used to be manual entry and took a good while to complete. I am trying to automate the process with VBA.
I created a UserForm called InfoVerify1. On that form I have TextBox 1 - 10. When the UF opens, the boxes display project information from my worksheet called "Basis of Estimate", also known as Sheet26.
The TextBox1 ControlSource is set to "E4". When I run the macro with Sheet26 active, the proper information fills in. However, when I am on the Start page or any other worksheet and I run the macro, it tries to fill in the text boxes with E4, etc, from the active sheet. I tried changing the ControlSource to "Sheet26,E4" or any combo thereof with only error messages.
how to get it to refer to a cell on a particular worksheet and hold to that worksheet no matter which sheet I am on at the time I run the Userform?
I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.
Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.
List of target columns in sheet 'Final' and source cells in sheet 'Source':
Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount
So basically I'm creating an entry for every amount in the table.