I've got code that loops through approximately 700 excel files and unprotects the workbook. I have to run this code for five consecutive days, and the issue I'm having is for the files that are unprotected on Day 1, the code still runs for those files the next 4 days.
There may be only 10-15 new files submitted on day 5, but the code still runs through all 700.
I have a workbook which contains 52 worksheets, all of which need protecting. Is there a way of protecting / unprotecting all these worksheets without having to enter my password on each separate sheet?
View 3 Replies View RelatedI have created a form that allows users to input data to an excel sheet, and also will enable users to view the data once they are done.
Is there a way to unprotect the sheet using VBA so that the data can be written, and then protecting once the data has been input?
I currently have set up two macros that will unprotect and protect all sheets. When I run the macro I noticed that it will not unprotect the sheets that have been set as a new sheet for the chart (the chart is not a object in a sheet it is a sheet all by itself) Can anyone tell me where I have gone wrong?
View 14 Replies View RelatedI would like to unlock the work sheet based on the username. However this code does not seems to be working.
The object is that only these users have access to the locked cells on a sheet and those users not on the list would only have access to the unlocked cells.
I cannot get my spellcheck to check any textbox. Is there a way to do this? Unprotecting sheets before spellcheck will work, but nothing about texboxes.
View 7 Replies View RelatedI want to run a macro that puts information from one sheet into another. The destination sheet is protected so I want to unprotect the sheet, do the copy and pasting then reprotect the sheet. The sheet has a password and I can't work out how to put that into the macro. It works fine without a password.
View 9 Replies View Relatedi would like the user to be able to unprotect the workbook via clicking a button. this button would first ask the user for a password before unprotecting the workbook.
how can i code this through VBA?
i already built the following VBA ...
I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.
I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.
I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.
I have the following VBA in my spreadsheet.
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
ActiveSheet.Unprotect ("kellyz")
If Intersect(Target, Columns("o:o")) Is Nothing Then Exit Sub
For Each C In Intersect(Target, Columns("O:O"))
[Code] ......
As soon as i type anything in the columns, my sheet is unprotected. I need the sheet protected so that the users can not change my formulas.
scenario: sheet & work book are protected to users; however need them to sometimes insert a comment to this sheet.
all columns/rows are locked except columns K, R, Y and every 7th column thru CJ (rows are from 4 - 100).
how do I enable a user to insert a comment without unprotecting sheet / WB? note: there's no pattern, it could be any cell within aforementioned columns.
I can get it working standalone, and when I protect it, but when I share the document it errors on me and I dont know why. I know there are limitations, but this macro should still run (the macro should unprotect everything, then protect and share after it has run).
Public Sub RQV()
Dim lngRow As Long
If MsgBox("Do you wish to move RQV data to yesterday? Note that all amendments will be reset also.", vbYesNoCancel) = vbYes Then
ActiveWorkbook.UnprotectSharing
worksheet1.Unprotect Password:="unsecureme"
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
. ScreenUpdating = False
End With...................................
I've got data being scraped from a site, putting 1 new workbook in a folder each day
each workbook has 40 sheets in it.
i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.
ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..
is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?
I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.
All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.
I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...
The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".
I would rather not have to copy this by column for each of the 4 workbooks
[Code].....
I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.
Sample:
Location A Class:AA Segment: Sports Bar Rep: John Smith
Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1
I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.
I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.
I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.
Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26
I tried to adapt the below to get one item copied/extracted. However it would no work.
I am new to using macros
Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False
I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.
View 9 Replies View RelatedI would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.
I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:
On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)
Code to merge worksheets from different workbooks stored in different location.
I have a sheet called "Master" in all the workbook I want to combine.
I have a unique password for all the workbook as well.
All the workbooks are stored in different folder location.
I would like to do a paste special values when the consolidation takes place.
I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.
Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.
I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.
I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.
Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.
Sub SearchAllXLSFiles()
Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook
On Error Resume Next
Set wbDestiny = ThisWorkbook
I'm trying to determine how much time our agents are spending for their lunch/breaks during 30 minute intervals. Range A8:A200 lists the agent's names, Range B8:B200 lists their lunch/break start times, Range C8:C200 lists their lunch/break end times, and Column E lists the times (8:00, 8:30, 9:00, etc.). If an agent starts their break at 10:57 (Column B) and ends at 11:10 (Column C), the value next to 10:30 (Column E) would have 0:03:00 and the value next to 11:00 would have 0:10:00. But would need to sum all the agents which took a break/lunch between 10:30-11:00, and 11:00-11:30.
View 2 Replies View RelatedIn this workbook Test1.xlsm under column "D" i have Po numbers. Some PO numbers are the same and some aren't. I have a specific code that creates an invoice for a customer when i hit the code however i must select a cell under column "D" in order for the code to create an invoice for the customer on that row. I usually create the invoice for all the customers of the same PO number. Sometimes the amount of invoices i have to create is overwhelming so i was wondering if it's possible that when i select a cell under column "D" that has, let's say, PO number "654" if the code can create an invoice for all customers that have the same PO number of "654"?
View 1 Replies View RelatedI have essentially the same issue with my spreadsheet, but mine is a little easier. I have written code to do the first few steps. I could just use some assistance with the middle. I know my thought on the process is "the long way", but with my experience in VBA I have to keep it simple. Here is my data and then the way I want it to look. I can add a macro to make it pretty afterwards, unless your way is easier (and I understand what is happening). --Oh, I am trying to automate my inventory process.
I have a spreadsheet and i wish to move every nth cell in column A to the initial cells in the row.
example:
Cell A2 moves to E1
Cell A3 moves to F1
Until the end and then select all and sort by column B (this would get rid of blanks and sort everything) before moving to the pretty stage.
How the data comes in. And my Final Goal.
This document is several thousand lines long. I made up the data to protect the weak. There can be duplicate usernames and vendors, but never serial numbers and computer names. Duplicates should be highlighted to be found easily and researched.
I only need to get to the middle picture. After that I can modify with what I come up with.
I have a excel sheet with several columns and 2700 numbers in each column. In the first column there is standing from witch quarter to witch quarter of the day the values are (and this for several days in one sheet). I want to take the average of all the first quarters of the day, the average of all the second quarters of a day,.... But if I would do that by just sorting my table on the quarters and than manually make the formullas I would need to give in 800 formules. (way to much)
So I would like to find an easier way to take the average of column B for the valeus were A is "00:00 -> 00:15" (this are avery time 28 numbers in the month february)
In attachement you can find an example of my problem, although it isn't with real values.
I have 2 columns with data in them, basically representing a gaussian distribution. Column A has the "X-axis" values and so is uniformly ascending with no duplicates. Column B has the "Y-axis" values and increases up to a maximum and then decreases again (this is data from an instrument and so its not completely smooth but is close). An example is below.
0 4
1 8
2 16
3 27
4 50
5 27
6 16
7 8
8 4
What I would like to do is get the 2 Column A values where the corresponding column B value is half of the max (in the case above, 25 is not available so the closest is 27). I am trying to calculate the difference between these values, so in the example, I would have 5-3. Is there a way to do this?
ok is there a way to have excel fill a certain number of cells with specific data specified X times? For example, the end result would look something like the pic below. The column on the right would change according to the numbers specified in the yellow column.
View 9 Replies View Related
