i have my employees data in sheet1. If i entered any project code in sheet2
column b1 it will diplay all the employees details along with emp code and name in next rows
sample data file attached
Is there a way to protect a VBE project using a macro instead of going to the properties and manually typing in a password? I'm using a macro that emails just one worksheet in my workbook (creates a new temp file, emails, and deletes it) but when the new file gets to the recipient the VBE project is unlocked.
I found a way to protect the worksheet before saving the temp file, so the worksheet is protected as it should be, but the project explorer window is completely open. Then anybody can see the passwords I'm using to protect the sheet in the first place. So I need to somehow re-protect the project before it saves and emails.
I am looking at the code in a MODULE, I can use "F8" to step through it.
But if I have no module just the main project code I cannot find a way to step through it.
I understand that we can protect the VBA coding through check the "Lock project from viewing in the Tools>VBAProject Properties. However I would like to know if this is possible in code?
I have a mainworkbook for users that has the VBA coding protected (it contains passwords to unlock protected sheets / main workbook). In my program, the users will be entering data and then push a button which calls a macro to email a "skinny down version" of the main book to other users. Problem is, these users that receive the email will be able to get into the VBA code and see what the password is on the sheets. (even if the new workbook/worksheets are password protected) Is there any way when I save the new book to add this protection to the VBA code?
I need a code that will look at each row on sheet 2 then highlight any cell that is different to the corresponding row/cell on sheet 1.
View 4 Replies View RelatedI have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
I want text that's in bold in column B in sheet 1 of my spreadsheet to copy over into column A in sheet 2 of my spreadsheet - is there a quick way of doing this using code or a formula?
View 3 Replies View RelatedI am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END
[code].....
I have data beginning in cell A6 and continues through cell A13 for this instance. I want data in cell A6 on sheet 1 to be copied to cell B1 in sheet 2; data in cell A7 sheet 1 to be copid to cell C1 sheet2 and so on till all of the data in the continuous range beginning in cell A6 of sheet 1 has been copied to row 1 beginning in cell B1 of sheet2. This seams relatively easy but below is my failed attempt at this.
Code:
Sheets("Data Request").Select
Dim DATAREQUEST As Long, TYPESRws As Range
TYPES = WorksheetFunction.Max(Range("A" & Rows.Count).End(xlUp).Row)
DATAREQUEST = Range("A6", "A" & TYPES).Rows.Count
Sheets("Data Retrieval").Select
For i = Range("B1", "1" & DATAREQUEST).Columns.Count To 1 Step -1
Sheets("Data Retrieval").Select
[B1] = i
ActiveCell.Offset(1, 0).Select
Next i
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
I have a large file + 400 000 rows that has zip code but not city name in sheet2, in sheet1 I have a list of zip code and city name. I need to get the correct city name for the zip code in sheet2 by using the info from sheet1. I attached a file as an example how it looks. I do have lots of other data in the original files that I have removed. This is not doable manually by using filter, to many rows so I need a script to run it.
View 2 Replies View Related1 . Now i need amount to be written in sheet 1 from taking value from sheet2 based on the code. The code & description column has the same value. but it will be placed in different order
Ex:
the amount should get filled up only for the status yes & no . others it should return null.
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.
View 9 Replies View RelatedI have 2 Worksheets in an Excel 2010 Workbook -
Sheet1
Column1: contains the word "dog"
Column2: contains the word "bark"
Sheet2
Column1: contains the sentence "I like dogs a lot."
Column2: is blank
What I need to do is search Sheet2/Column1 for the presence of "dog" and if it's present, populate the word "bark" in Sheet2/Column2 from Sheet1/Column2.
How can I do this?
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies View RelatedI have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
I have data in sheet 1 like below
Custid loc city
123 us newyork
124 uk wales
876 in mumbai
Sheet 2
custid newcustomerid
123 756
124 394
Outputsheet:
Custid loc city
756 us newyork
394 uk wales
876 in mumbai
So it will check sheet1 custid with sheet2 custid. If both matches it will replace custid with newcustid.if cust id not exists it will load sheet1 recprds as usual.
I have two column (A and B) text data in worksheet1. If I type same text of column A (of worksheet1) in worksheet 2 column A, how can I retrieve data from worksheet 1 to 2 from same row.
View 1 Replies View Relatedwhat i nee is top get a formula to look for date then name and give me the aswer from cell c2 on sheet 1 and palce this on sheet 2 sheet 1 is populated by a excel form i have designed.
View 3 Replies View RelatedI am enlcosing a file that I partially acquired (the macro portion) and I am trying to add to that.
The workbook has a dde link in sheet 1, the macro will write the data to sheet2 at set times. I am creating a report of the data in sheet 2 in sheet3. at this point it is very rudimentary. I need some assistance to clean up the macro and the post of the dat in sheet2. I have created a detailed (probably over detailed) explanantion in sheet 1 in a text box.
I have:
- sheet1.xls, this is the source sheet.
column A = model numbers
- sheet2.xls, have the data that I need to copy to sheet1.xls
column C = product description text and in column D = product price
column G = product description text and in column H = product price
What I need to do is to write a Macro that:
open each row in column A in sheet1.xls and search in sheet2.xls in column C if it finds this text (model number) then it should copy row D (product price) and paste it to column E in the right row in sheet1.xls.
Also search in sheet2.xls in column G if it finds this text (model number) then it should copy row H (product price) and paste it to column E in the right row in sheet1.xls.
I hope please that you understand me.
And will be happy if someone could help me please to do that
I am working on a spreasheet that will automatically calculate the interest rate, loan to value advance, and other parameters from user inputs.
I have found a way to get the calculations to work correctly, but I have about 50 lenders to input - all with different rates and lending guidlines with respect to loan to value advances.
I am sure that I am going about it the hard way and I have no problem going at this to get it right.
I have attached the spreasheet I've started. I've only got one lender completed thus far. So if you need help sleeping at night, go ahead and see what I've done (yes it's boring).
Edit note: I don't know if using Access would make this an easier project to tackle but it is an option (I'll just have to learn
Access if that's the case - I've never used it).
I want to skip 1 row blank in two entries while transferring data from sheet2 to sheet1. There are about 100 entries on sheet2 which I want to transfer on sheet1. If I skip 1 row and paste the formula it skips 1 entry. How to avoide it?
View 1 Replies View RelatedDate & Time JOB NO PENDING
12/23/13 2:14 PM SICD-B00xxx *
My question is, there are three cells named- Date & Time, Job No and Pending. When I tick(*) in Pending named cell, I need to copied automatically the data of Date & Time and Job No to Sheet 2 in the same cells from Sheet
1. which formula I can use and how?
Sheet 1 : Date & Time (O5), Job No (Q5) and Pending (S5)
Sheet 2 : The same as Sheet 1
When I give * in Sheet 1 S5, the other data should copied to Sheet 2 in O5 and Q5.
I want to use formula in sheet 1 S5 because I have already used a formula in sheet 1 O5, as
IF(Q5<>"",NOW(),O5),"")
Can anyone tell me if is possible to copy data from sheet1 to sheet2 using VLOOKUP?
I had an interview where they asked me to copy data from sheet1 to sheet2.
I used the function (=Sheet1!A1:...) and for me it was right, but they told me that it was wrong since I did not use the (VLOOKUP) function.
Please confirm if VLOOKUP can be used in this regard.
I have some data in sheet1 (as per attached sheet), every month i have to enter this data more than 1000 rows..i want a formula so that automatically this data should go to sheet2 and get a seperate report shape (as mentioned in sheet2) and also add a row below of each report in case ColumnA (Shipp) data is increased for some shipp name and also report heading shoudl be change according to columnA (shipp) and renumbering it.
View 3 Replies View RelatedI have data in sheet1, which needs copied to sheet2
But in sheet2 already data is available. In Sheet2, i want code to go from header to down , to locate the first blank row in between data and to paste data by inserting same number of rows as number of rows copied.
Basically I have two sheets. Sheet1 and sheet2 on the same workbook. The sheet1 is where all data I want to extra to the sheet2.
Here is the sheet1 looks like:
1
B
C
D
E
F
2
Tester :
Mr.ABC
3
State :
CA
4
Phone :
123456
5
6
Header1
Header2
Header3
Header4
Header5
7
Value1
Value2
Value3
Value4
Value5
8
From B2 to D2 the cells are merged as well as the B3 to D3 and B4 to D4. Note: No Column A.
Here is the sheet2 looks like:
1
Tester
State
Phone
Header1
Header2
Header3
Header4
Header5
2
3
4
So, sheet2 has the header and what I need is to fill the information based on the sheet1.
I am thinking using the Vlookup but maybe VBA is better off to handle this situation?
I have workbooks which have data broken out of a larger file, I have solved the issue of updating the "Master List" from these broken out sheets. My problem is updating sheets. This has been done cut and paste manual style for some time and I am trying to streamline the work since being assigned to this project. I am trying not to change the workflow so I have to keep the sheets and work around the setup.
Sheet1 contains identical data as sheet2 with the exception of the completed work which is removed. In other words sheet2 contains an "assigned" list of work maybe 200 rows, this includes any work not yet completed in sheet1 as well as all the work previously completed but removed from sheet1 which may only contain 50 rows. Very simply, I want to parse through column A in both lists containing a specific ID number and match accross sheets, if column G and H on sheet1 match my needed criteria and column A from sheet 2 then copy that row from columns G:AT or 7 to 46, but only the values since there are formulas and formats I don't want in the updated sheet2 because the script I have for pulling from these sheets to the MasterList would fail.
As a bonus to eliminate the need to run another bit of code if I could delete the copied rows from sheet1 after they have been sent over to sheet2 would be great. Headings for all the sheets are the same so the data is identical. Column A is a customer ID number, G assumes initials from the clerk, and column H is date work completed. So if record A found and G=XX and H=Date then copy this rows columns G to AT values only. After which rows copied over will be deleted from sheet1. Headings are in row one and data begins on row two accross all sheets.