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 RepliesI 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?
View 10 Replies View RelatedI'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 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 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 Relatedcombining 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'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 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 RelatedI 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”.
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.
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 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 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.
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...
I am using the following code to identify any blank rows in a sheet and delete them:
View 3 Replies View RelatedI 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 RelatedI want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
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.
What I am trying to do is to write a macro that will automatically copy six columns from worksheet (Sheet 1) to another worksheet (Sheet 2). i.e. ‘Description of Project’, ‘WBS Code’, ‘Rate’, ‘Employee Name’, ‘Premium’, ‘Invoice’, ‘Status’, ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ from Worksheet (from Sheet 1 to Sheet 2)
The problem arises as I know the names of the columns to be copied in Sheet 1 (as details above) but they can be in any order in sheet 1.
In additional the columns ‘Total Cumulative Hours’, ‘Total Cumulative Amount’ are total columns so when they are copied from ‘Sheet 1’ to ‘Sheet 2’ their values should be copied as opposed to the formulas
I have a worksheet that utilizes a Worksheet Change Event. I created a macro to copy the sheet and that works fine except for the fact that it doesn't contain the Worksheet Change event.
View 5 Replies View RelatedI am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues
One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet.
There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet.
ive done basic c++ programing years ago, but know nothing about vba or excel macros.
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I have in my Workbook_open a macro,called Workbook_SheetActivate
with a line : Msgbox ActiveSheet.Name ....
In a sheet I have a macro SortWorksheets ....
My problem is , when I run the macro SortWorksheets it always stopped
by each worksheet until the macro is ended, this because the macro by opening workbook.
How can I make the macro SortWorksheet in one run?
to write a Macro which will be able to read a numeric value in a cell and find the equivalent of the row number in the sheet.
For instance, if I type "12" in cell A1, the marco will be able read cell A1, goto and highlight Row 12 of the same work sheet.
I have some code where I use and statements, as follows:
View 4 Replies View Related