I have many customers particulars and data coming in everyday. How i do create a code such that with one click i'll be able to update my worksheet in Excel without typing in manually?
View 10 Replies
Dropbox - 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.
I have about 600 patients' charts, which I audit every 3-6 months. I have created a macro which imports each patient's workbook into a single workbook (one worksheet per patient). Now I'm trying to create a macro which will import each patient's most recent audit results onto one worksheet. Here's how the results are organized:
2/1/2010 Audit 3/1/2010 Audit 4/1/2010 Audit
Y N N/A Y N N/A Y N N/A
x x x
x x x
...etc., with each audit taking up 3 columns and 32 rows. My code, however, is not working,
I want to run macro for copying specific cell values from worksheets according to their headers in one final worksheet of the same workbook. worksheets can go upto 30-31 ws as per the dates in the month. This would really save time and energy of copy paste.
Attached is the workbook : Datewise.xlsx‎
I'm trying to write a macro that selects file A, copy some particular columns in file A, copy them into a new workbook, then repeat the process for file B, and copy and paste the columns into the same working workbook. Does anyone have any suggestions on using any one of the workbook, worksheet functions to do so?
View 9 Replies View RelatedI have exported a report out of Quickbooks with all my customers that have done business with us from 2005-2008. I want to be able to delete the customers from the worksheet that have done any business in 2008. So that I am left with a list of customers that have not done any business with us in 2008. Can a formula or macro do this for me. I don't just want to delete the rows that contain any dates in 2008, but delete the customer as a whole from the workbook.
TypeDateNumMemoNameItem@Home RealtyInvoice10/20/200522813BIC Clic Stic Black Barrel Red Trim White and Red Imprint Black Ink Medium Point@Home RealtyCSInvoice10/20/200522813Printing an additional color on the merchandise@Home RealtyADDCOLORInvoice10/20/200522813Shipping & Handling@Home RealtyShippingInvoice12/01/2005FC 2058Finance Charges on Overdue Balance@Home RealtyFin ChgInvoice07/18/200730753Silkscreen add web address to shirts@Home RealtySilkscreenInvoice07/18/200730753Silkscreening Set-up fee@Home RealtySet-upTotal @Home Realty1 Source ManufacturingInvoice08/16/2007311575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice08/16/200731157Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice08/16/200731157UPS Ground Shipping & Handling1 Source ManufacturingShippingInvoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/2007318755-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice10/25/200731875Set-up for Printing Plate1 Source ManufacturingPlate ChargeInvoice10/25/200731875Shipping & Handling1 Source ManufacturingShippingInvoice07/08/2008343575-11/16"x7/16", Reversible, Standard Blade, Phillips Blade, Magnet Top, Nickel Plated, Heat Trea...1 Source Manufacturing8809Invoice07/08/200834357Shipping & Handling1 Source ManufacturingShippingTotal 1 Source Manufacturing
I have set up a control sheet that gathers information over the current week, eg: amount of deliveries and total cost. On the control sheet it gives the amount of deliveries and total like at say A10 and B10, on this same page I have a 52 week colomn that needs to gather this information. Now the problem is a new delivery plan is used each week but the control sheet stays the same, how can I get the data from A10 and B10 to copy to the different lines in the 52 week sheet. Have attached the workbook for reference.
View 2 Replies View RelatedI'm trying to consolidate data from different worksheets that has the worksheet name consisting of "Pg*" into a summary sheet. The problem is it does not loop through every sheet and only extracts data from the active sheet.
Code:
For Each ws In ThisWorkbook.Worksheets
If ws.Name Like "Pg*" Then
Set CopyRng = ws.Range("A2:G68")
[Code]....
I have below code, but does not work correctly. The problem is when it's run in main worksheet, it doesnt match the correct workbook in every worksheet and it only generates the data in first workbook for all worksheets. It seems that it lacks something link between workbook in main worksheet and workbook in others.
Sub HzWb()
Dim bt As Range, r As Long, c As Long
r = 1
c = 8
[Code]....
I have a complex sorting macro with userforms & Modules. Works great! Now I need to save the final Worksheet without the macros. The final step to my project is a Application SaveAs. Can I add a delete-all-macros step when the user clicks the Save As button which would create a new workbook with the finished worksheet and NO macros?
View 9 Replies View RelatedI have 8 Sheets in my worksheet with orders from my customers. Column C is their PO# and Column M is the Status of the order. What im looking to do is set up a summary sheet with the List of 8 customers that tells me what PO#'s are in a particular status.
I need a formula to list all PO's that are in "Fabricating" or "Completed" Statuses.
I have gotten this far:
=if(Sheet1!M25="Fabricating",Sheet1!C25,if(Sheet1!M25="Completed",Sheet1!C25,"None"
which works perfectly for the individual rows, but i need it to do it for all rows (1-2500). If i do Sheet1!M2:M2500, i loose it.
I have a set of related variables that are split over multiple worksheets, and I need to be able to take specific information, duplicate certain values and produce an output sheet for use in a separate piece of software.
The variables are:
Position Number (Sheet 1)
Position Title (Sheets 1 and 2)
Position Requirement (Sheet 2)
Requirement Importance (Sheet 2)
The output sheet requires a list of all the requirements for each position number, which means the position number itself needs duplicating (in new rows) X number of times, where X is the number of requirements assigned. The appropriate requirements are then to be pasted in next to each position number (and the requirements can be found by comparing position number to title, and from title to requirements).
Normally, I’d be able to do this using lookups and so forth, but my problem arises when I have multiple position numbers with the same related title (in the attached example, there are three plumbers with unique position numbers). I can’t figure out how to say to Excel “a plumber has five requirements, and there are three plumbers, so duplicate each position number for each plumber five times, then insert the appropriate qualifications (and their associated importance values) next to the position numbers”.
I have 3 list of data and I need to combine them.
Item list (5 to 100 items in a list)Quantity listStore list (2 to 10 stores)
For every item in my list, I need to add the list of stores to distribute to, and the quantity needed.
Example of order sheet
Stores
Items
74
22
70
71
59
[Code] .........
I need a macro to convert the order array list into column form such as Items A, Store B and Qty C
Item
QTY
Store
1001
2
74
[Code] ......
I have been trying for 3 weeks different loop combinations.
DATE
PRODUCT
BALANCE
1/2
KEY CHAIN
4
1/2
KEY CHAIN
8
1/2
KEY CHAIN
27
[code]....
How to get final figure each product from sample data above:
the result:
Key chain = 27
Gift = 4
Hamper = 1
I received assistance from NBVC for combining data from two cells. Post http://www.excelforum.com/showthread.php?p=2135144. I need to convert the formula into macro code. I thought I would be able to convert it on my own, but running into some troubles with run time errors. Here is the Excel formula, which is working fine.
View 5 Replies View RelatedThe project I am currently working on is to extract the from over 25,000 lines of data the top ten of our customers for 2012. Our customers are billed monthly. We have about 2,300 customers. There are several customers who have more than 1 account.
The easy part has been to create a "charges" report and download into an excel format. The information can is alphabetized, but then the challenge comes in. Ideally it would be a snap to total each customer seperately and then somehow be able to extract the top ten from those totals.
I'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 am trying to combine data from two separate worksheets onto one so it can be sorted for printing. Using the macro recorder, and the search function on the forum, I managed to ham-fist my way through most of it - except for one issue.
How can I have Excel/VBA go to the first open cell in column A before it pastes the 2nd batch of information? I get an "object required" error with the MyRange variable.
Code:
Sub UpdateSortedTab()
Dim MyRange As Variant
' Removes Old Information
Sheets("Sorted").Select
Columns("A:E").Select
Selection.Delete Shift:=xlToLeft
' Copies Bench Stock Information
[Code] ........
combining two worksheet change codes. I have the following
View 4 Replies View RelatedI have several workbooks (called Cons_age0, Cons_age3, Cons_age6 and Cons_age12) that I would like to combine into one workbook called Cons. Each of the previous workbooks should now be a worksheet within Cons by their name.
View 6 Replies View RelatedIf a cell in some parts of the worksheet (SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo in the code) are selected the input (4 digits) should be converted into time (00:00).
Cells in some other parts (Util,Assis) require 6 digits and should be converted into time (00:00:00).
I had the code working in seperate worksheets but combined (as is shown in code bellow) only part of it works.
Only the input entered into the 4 digit part (SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo) is converted correctly.
The input in the other parts (Util,Assis) is not converted (so when 800 is entered I do not get 00:08:00 but I get the hour equivalent of 8 days (19200:00:00) and logically the message from the EndMacro "You did not enter a valid time".
The cell formats are correct [u]:mm and [u]:mm:ss.
To me it looks like it does not jump to the 6 digits part when need but I can't figure why.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim TimeStr As String
On Error GoTo EndMacro
If Application.Intersect(Target, Range("SSMa,SSDi,SSWo,SSDo,SSVr,SSZa,SSZo,Util,Assis")) Is Nothing Then
Exit Sub
End If
If Target.Cells.Count > 1 Then..........................
I am trying to do is have a worksheet that gets its information from other worksheets within the same workbook. Some of the worksheets that it gets its data from are web queries, and as such change on occasion (columns stay the same but rows get inserted or deleted as data on the web page that it is importing changes). Also due to this being an import I can not change directly the format that comes onto the sheets (ig split into different columns etc). Much easier described within the example as I am not sure of the words to best describe the end result I am looking for. A copy of a smaller version of what I am doing is attached
View 3 Replies View RelatedIn my workbook data.xls i have 3 worksheets - data1,data2,data3
In all these 3 worksheets i have certain datas in range as A1:L60 only.
my 4th sheet in the same workbook is blank.
What i want in this 4th sheet ( sheet4 ) is the combined data in all the three worksheets data1,data2,data3 one below the other with a blank row after each sheet data.
I mean in sheet4 A1:L60 should be data in worksheet data1.
then a blank row - 61st row
then A62:L122 - data in worksheet data2
again a blank row - 123rd row
then A124:L184 - data in worksheet data3.
I was wondering if you could help me combine a load of Macros (Theirs about 20, but I've only put two here)The code I have is this...
View 10 Replies View RelatedI have three sheets within an excel file and I wish to run a macro to pull the results into one sheet. The individual sheets may vary in size and length so I want to copy all opf the used- range(?). I attach an example of the three sheets and an end result sheet.
View 4 Replies View RelatedI have been working on a macro that compares a existing list of data to an updated list of data and then either moves any data not on the new list over to a completed tab (followed by deleting the record on the existing sheet), and then adds any items not on the existing sheet, but which appear on the new list, to the existing list.
I have come across a stumbling block, i have managed to identify on the existing list the rows of data that have been removed from the new list and therefore need to be moved over to the completed tab, but when i select the data it selects the header row aswell (which will always remain the same row). Obviously this then pastes the header row aswell, and also i can't seem to get it to paste in the new sheet to the next available row (i.e this will be used daily and i don't won't to overwrite the infor already in the completed tab). the next issue i have is then when i go back to existing sheet to delete the data i just copied across, as the header was initially select this also gets deleted.
The code below, is the complete code, including filtering, copying some forumals etc. The area i am getting stuck on is highlighted in red:
Sub Update()
Dim bottomrow As Long
Dim My_Range As Range
bottomrow = Cells(Rows.Count, "C").End(xlUp).Row
Set My_Range = Range("A1:Y" & bottomrow)
[Code] .....
I have the below macro in a workbook that I need to combine the two sheets into one, (sheet1 and sheet2). Both sheets in use column "A" for id, but for some reason it is only copying over sheet one? One sheet has 12 columns and the other has 13, not sure if that is making the difference?
I have a macro that someone else wrote. it is suppose to combine multiple sheets into one. the problem is that when it combines the information it is adding rows between the info. I would like it to just put the info on the next available row and begin there. here is the macro.....
View 9 Replies View RelatedI have a step in a very long macro to populate a sheet (SheetMaster) with the course completion status based on another sheet (SourceData) in the same workbook.
On "SheetMaster" Col E starting with row 2, I want to look at whether a concatenated value of E1 + A2 is in Col G in "SourceData". The macro should run until it gets to a blank row in Col A.
Here is an example to help illustrate the logic:
E1 = Ethics101
A2 = 123456
A3 = 654321
If Col G in "SourceData" contains "Ethics101-123456", E2 = "Complete", if not "Incomplete"
If Col G in "SourceData" contains "Ethics101-654321", E3 = "Complete", if not "Incomplete"
...and so on until it gets to a blank row in Col A...
I have huge data of income of employees.
In column "A" i have names of employees,
in column "B" i have income of last month and
in column "c" i have income of current month.
In column "D" i have calculated difference between two month's income(C1-B1).
Now based on values in column D i want to find names of top 5 gainers and top decliners (i.e. i want names of top 5 people who are having highest value in column "D"). I don't want to use pivot and sort, I want these names by using any formula.
Its working perfectly. But now I have added one more column in the beginning as country names. Now i want to make a dashboard so that i will create a drop down with country names and country i will select top gainers should come with ur given formula.
