I am trying to get this to sort out to a new workbook instead of a worksheet. There are only two components in C column, so there should only be 2 new workbooks created. I am a little unfamiliar with the terminology for the workbooks.
View 4 RepliesI have a file with the following columns:
ID Number - Name - Email - Comments
Is there a way that for each row and email is sent with the ID number, name and comments?
I am using the code below that I got off of these forums to email a particular sheet in my workbook, but I need to strip all of the VBA code and the command button from the sheet being sent.
View 14 Replies View RelatedI need of some way to capture a screen shot of a program on screen and send it to a specified email address at a certain time of the day. I would not think its impossible, could it be done using vba code?
View 10 Replies View RelatedI am trying to set up a new workbook for my home accounting, this will consist of a seperate sheet for each item which will store the monthly payment data and a master data input sheet, where i intend to select the month and year from a drop down list, then input the amount in each entry for that month. hopefully i will then be able to hit enter and all data will be sent to its corresponding position in its relevent sheet.
View 6 Replies View Relatedbelow is the code i use to create a lotus note email.
i would like it to display my email and send it manually instead of sending out automatically.
' Open and locate current LOTUS NOTES User
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
[Code].....
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies View RelatedI have to copy a column from another workbook and past in to the column in this workbook. However the column i am copying are all referenced from other cells. Is there a way to just copy the numbers and move them into this workbook to avoid double data entry?
I am attaching the 2 work books.
1. Is MSP commissions Structure that is where the data needs to be pasted
2. Grace - this is an example of what we will need to be copying from.
On Grace - copy Column G, Rows 65-81... The paste those numbers into MSP commissions structure column D, Rows 9-25......
We will have a file similar to Grace for EVERY deal closed....FYI so this process if possible needs to be replicated many times.
MSP commisson structure.xlsx
File Type: xlsx Grace.xlsx
I have a workbook which contains information that I would like to transfer to a new spreadsheet.
If column I (in the exsisting workbook) contains the words 'New Business' I would like the entire row (all values) to transfer to the new spreadsheet. I need the information from the entire workbook (containing 12 worksheets) to transfer over to the new spreadsheet if column I states 'New Business'.
I have tried many things, most recently VLOOKUP, but with no luck at all.
I understand that a macro is the best option, however my excel skills are nowhere near advanced enough to know where to begin
Basically I am trying to write a code:
Using a Month specified in an input box eg: July Payday
Look up that month on the "info" worksheet to then copy and paste all the dates for that month on to a worksheet called "Working Hours". This is the code I have at the moment...not much I know, I don't know how to format the code in to tags:
Sub WorkingDays()
Dim LastRow As Long
Dim Month As String 'setting the object of Month being the data we need to find
Month = InputBox("Please enter the month you wish to record, eg July Payday")
'Typing the Month which we need to copy the dates for
I am working with two files, both files have multiple worksheets. File A has a worksheet that is filled out almost entirely by equations. I need to move the information from that worksheet into file B. In file B i will process the information further. Also, I will print it and it has to look exactly the way it does in file A. I want all this to happen automatically so I am looking for some sort of function or script that will perform this task.
View 9 Replies View Relatedwe have an Invoice workbook "SampleInvoice.xls" with a drop-down list to select from a list of products, listed within the Data workbook "SampleData.xls" (which includes descriptions, codes, prices related to that product). So far, when you select the product, the macro will locate the product inside the Data Workbook, and return the Description of the product to the same cell in the drop-down cell.
My question is:
1) The cell to the left should bring up the Code for that product automatically
2) The cell to the right should display the Price. But there's a catch - there is a Distributor, Trade and Retail price. Ideally that cell will be a drop-down menu which gives the 3 pricing options. When you select the suitable field, the macro will determine the Code (as identified in point 1) and match the suitable pricing accordingly. Here is the code so far:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
If Target.Cells.Count > 1 Then Goto exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then Goto exitHandler
Application.EnableEvents = False
Target.Value = Workbooks("SampleData.xls").Worksheets("Stock List").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Workbooks("SampleData.xls").Worksheets("Stock List").Range("B1:B500"), 0), 0)
End If...........................
I have a piece of code that consolidates data onto one sheet.
Once the code has copied everything over, I would like it to also duplicate the Category field.
There is no option to add the duplicated column in the source information.
Here is the consolidation code:
[Code] ......
Can someone help me i have a few columns of information i need to move over to a another sheet, but the problem is the information i need to move is the end result of a formula and when i move it the answers are gone how do i fix this,,,,, and both sheets are on the same work book as well
is there a vlookup formula to use,,,im not experienced in Vlookup up formulas
I have a Macro with a main goal to download info from another workbook and reconcile it... the information being copied is always from a workbook called book1 so the following code is what i am using:
View 3 Replies View RelatedI have 2 separate workbooks that are linked together and work in tandem. One is the Master Workbook "Master Business Plan Worksheet For Sale.xlsm" and the other is the one containing all the Menu Items for the business "Menu Items Workbook.xlsm"
I have a Master Recipe Sheet that I created in the Master Workbook and made sure it all works. Now I have copied it to the Menu Items workbook and copied all the Userforms and created Macros for it but I need setting up the references to the other sheet. I have included the code from the first sheet for you to look over. The textboxes all need to populate to the Master Workbook and all the info for the combo boxes are also in the Master Workbook.
Code:
Private Sub UserForm_Initialize()
'Populate Measurement combo box.
Dim rngMeasurement As Range
Dim ws As Worksheet
Set ws = Worksheets("Start Here Sheet")
For Each rngMeasurement In ws.Range("Sizes_List")
[Code] .....
, just by using the ISIN code of a Bond in bloomberg, to get in excel:
- Coupon
- Cpn Freq
- Day Cnt
- Maturity
Everyday system generates a notepad with the information.. from the notepad i ve to copy paste the info to the excel manually.
Can a code be written where (after downloading information to the excel from the notepad) it automatically fill the information in the excel.
For your reference i ve attached both notepad and excel with dummy datas.
(how i do manually)
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it.
I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location.
I'm hoping the example will make this clearer.
On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header.
To make this more complex, I can't use vba on this one.
the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table.
something like this.
(edit changing 1:1 to $1:$1)
=IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0)))
It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!
Is it possible to automatically have columns repeat through all sheets in a workbook? I keep an annual book, broken down by sheets for each month. I always have to add 5 columns to each sheet, and was wondering if there was an easier way..?
View 3 Replies View RelatedWe have a Shared workbook on our Server and about 5 users work at the same time every day. I want to understand something, If I open it I can see all the information in it, all the rows and so on, but if the same workbook is opened by another user he will see other information, the rows aren't the same as in my case. Why does this happen ? I would like the second users to see the same information as I do.
View 1 Replies View RelatedI'm trying to get certain Excel cells (varies by column and row) to populate different areas in a Word document. I've tried using both Word bookmarks and form fields and have defined the Excel names with the Word bookmark names as well as using Word's generic terms (Field1, Field2, etc.) with no luck. The VBA code I'm using now is for word forms and will open the Word document but seems to delete the form fields. I don't know if I'm not coding the cells correctly or what.
Sub CreateProposal()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Set wdApp = CreateObject("Word.Application")
' Open word document
[Code] ........
I have created a userform but just unsure how to format the code in order to get information to appear in a spreadsheet
http://www.srfl.ca/userform.htm
Is it possible to VLookUp Data between multiple workbooks (12 different workbooks to be exact) in one master sheet?
I would like the master sheet to find the empty data for the item number. Basically, how can I automatically populate the empty data for each item number in the master sheet. Every item will be different. The 12 workbooks are invoices throughout the year (Jan, Feb, Mar, Apr, etc) and these workbooks have the data needed to fill the master sheet. How is this possible? Or is it not?
Master sheet.xlsx
-Contains item number
-Purchase Date
-Sell Date
-Purchase Price
-Sell Price
-Profit Price
The 12 workbooks are the 12 months out of the year, which are invoices.
- Contain the data needed in the master sheet such as sell date, sold price, and profit.
The reason I would like to keep the 12 invoices as workbooks instead of worksheets in 1 big file, is due to the high capacity of item numbers each monthly invoice would have. For example (approximately 500 items in each workbook)
I have for hours and weeks, actually months, But I haven't found any sources on the internet, although I have seen the VLook Up how to's on youtube between 2 workbooks, but not the 12 I would like.
I'm having a problem with getting a result from another workbook. The purpose is to extract information from a closed workbook representing data from a country (ie US.xlsm, UK.xlsm) into the open one (summary). I want to count the number of results in a column which are above a certain amount (in this case 100).
So this is my coding for getting this result
=SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))
Which works no problem for getting the number of results in the E column above 100. my issue is that I want to be able to change the name of the country in a cell, and then get the results automatically from the relevant workbook. I've been using CONCATENATE for these purposes and EVAL (from MOREFUNC)... and I get #REF!
So if I split up the above coding into two cells "=" and "SUMPRODUCT(--(('C:address[US.xlsm]Worksheet name'!$E$2:$E$30000)>100))" (just to keep it simple) and then use CONCATENATE to combine them in a cell. Using EVAL on that cell gives me #REF!
I've tested SUMPRODUCT with EVAL and CONCATENATE on formulae like the one above which do not link to other workbooks, and they seem fine. and SUMPRODUCT and linking to a workbook also works fine in the first example.
So the problem is somehow coming from the combination of SUMPRODUCT, EVAL and accessing an external sheet.
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
[Code].....
I have code that i use to copy a worksheet of information in one workbook to a worksheet in another workbook. All i need is some guidance on how to copy a second worksheet from workbook 1 to a second worksheet in workbook 2. Should be fairl straight forward.
In the below i am copying the sheet Phone_data to a second work book sheet also called Phone_Data, i would like to include in the same proccess a sheet called Sur_Data from workbook 1 copy to a sheet called Sur_Data_R in the second. All the other features like find next empty row also apply.
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)