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 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?
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?
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.
I'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")
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
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 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.
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 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.
If 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..........................
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 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
In 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.
I 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.
I 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)
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.....
I 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...
If I have two columns which are lists of names, how do I make a new column which merges the names from each column into one list, and will refresh when new data is added to the two originals.
I recieve a data file on a monthly basis. Is there a way to take all of the monthly worksheets and combine into one workbook without doing a copy paste every month?
I have been trying to work between Access and Excel and my forehead is getting bruised from the brick wall in front of my desk. I am finding Excel much more useful, but request some help with the final tasks.
Column1 of Sheet1 is an identifer. This number, in some cases is repeated which is necessary for when I include this data back into the master sheet. I make note of identifing this as 'text' as to not lose any zeros or go 'scientific'.
Column1 of Sheet2 is the same identifer but does not repeat (hence fewer rows). Column2 of Sheet2 is the city name which the identifer in Column1 is located.
I need Column1 Sheet1 repeated on Column1 Sheet3 and I need the respective city name from Column2 Sheet2 in Column2 Sheet3. Once I have this I can re-insert it into the master sheet.
The two areas I am trying to learn is if I am to use an If or a Findit, and secondly incorporating the multiple sheets.
I have a spreadsheet with a name spread over 3 cells
A1 = MR B1 = JOHN C1 = SMITH
i want to create a macro or a formula that combines all of the data in these cells into the first column so that in column A1 it would read MR JOHN SMITH.
I need a macro that will ask for a crew # and then select all the rows off another worksheet that contains that crew #. Then take the selected rows and copy them to the active worksheet for reporting.
What I need , is a macro to take data entered from "worksheet1" and fill in next row on a separate worksheet based upon salesman selected from cell C20 in "worksheet1" .
For the sample, I have just 3 saleman in a dropdown list and have labeled worksheets accordingly. If i could get help with say just using info from cells C6,c7 and e6 from "worksheet1" and put it where it goes.
c6 last name is row b on data sheet c7 first name is row c e6 trade year is row o
