Adapting Code To Merge Data From Multiple Ranges
Mar 15, 2009
I'm in the process of combining a lot of worksheets into one and found the code on the following site:
[url]
which almost does what I need. I understand how it works (i think!) but as a vba novice I can't work out how to adapt the code to copy more than one range from the source workbooks.
What I want to be able to do is copy say the following cells from each workbook:
A1
D1
d4:D7
I think what I need to do is call the getdata macro multiple times with different ranges, but I can't quite work out how to do that without simply creating multiple copies of the master file.
View 9 Replies
ADVERTISEMENT
Jul 20, 2007
if there is a way to select a group of cells, select another group of cells and keep the previously group of cells selected.
Example: ...
View 6 Replies
View Related
Jul 31, 2014
I have 3 different sets of macros and i want to Join them together so i only have to run 1 macro to do all 3. I am not sure how the best way is to do this.
Code 1:
Code:
Public Function UserName()
UserName = Environ$("UserName")
End Function
Private Sub RefreshTeamQueryOnWorksheet(worksheetName As String)
[code]....
View 2 Replies
View Related
Jul 8, 2014
I have 3 files out of which Inv.txt is the main file.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140701 Inv.txt and it adds the data from 20140701 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140701 Inv.txt and it adds the data from 20140701 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140617 Inv.txt and it adds the data from 20140617 INv.txt column 7 to the last column in INv.txt.
I would like that the other two files get opened and the the number in column 3 gets vlookup in the file 20140617 Inv.txt and it adds the data from 20140617 INv.txt column 7 to the last column in INv.txt.
The two file names that it looks up from are always with a date stamp (Current date-6 Days) so this needs to be dynamic.once it has all the data in Inv.txt just save the file.
View 1 Replies
View Related
Aug 2, 2013
I have three source workbooks that needed to be consolidated into a master workbook pasting the data with values & formatting.
The Master workbook also contains 6 additional worksheets that link to the data pulled in from the source files.
My question is how do I write the code so the source files populate the master in a specific order.
For example, the 3 source files are named "Central" "NED" and "WEST" and I need them to populate the master workbook in that order.
This is the code I am using to consolidate the data:
Option Explicit
Sub ConsolidateSheetsFromWorkbooks()
'Author: Jennifer Starr
'Date: 7/12/2013
'Summary: Open all files in a folder and merge data (stacked) on all
[Code] .....
View 1 Replies
View Related
Apr 4, 2009
I have a spreadsheet that has two different data sources (i.e., A and B). The amounts of these data sources (i.e., the number of columns) varies from sheet to sheet. I have to calculate the averages of these data sources independently and together. Because this data is spread across twenty or more spreadsheets, these calculations can be time-consuming. I want to do the following:
1. Locate the last occurrence of the first data source "A" in row and then insert a column after that cell.
2. In that cell, I want to get the average of each data source for each row of data (i.e., there are always 19 rows of data).
I want to do the same two steps for the second data source "B". Then, I want to insert a column after the "B" average and this column will be used to get an average of each row of data from A and B together. Please keep in mind that the number of data sources for A and B varies from sheet to sheet.
So far, I am working on code to try to " find" the text in a range (i.e., find the last instance of "A"), but I cannot figure out how to get it to get it to stop at the last occurrence and then insert a column. I have some ideas about how to calculate the average, but any of yours are much appreciated.
Also, the row with the type of data (i.e., A or B) is named because this function is part of a larger macro. Therefore, it is relatively easy to get to it.
I have attached a version of the file that displays how I want it to look.
The code I have thus far is:
Cells.Find(What:=A, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
View 9 Replies
View Related
Jul 7, 2014
for creating the macro As I dont have any idea about macro.
My question is that I just want to marge all the tab into one tab however some header in all the tab are dirffernet however I need one header.
I have attached the example file, in that tab named as "OUTPUT" that is what I needed by macro.
View 14 Replies
View Related
Dec 6, 2008
I know how to merge data in a CSV (see attachment) from 2 columns into a single cell using =A1&" | "&B2 so that A1 (Safety Products) plus B1 (Fire Protection) becomes Safety Products | Fire Protection in cell C1, but how can I do this across multiple rows so that each pair of names is combined in each row? The job I'm working with is a product CSV file that has 6370 lines so I don't want to do them one line at a time!
View 2 Replies
View Related
Nov 10, 2008
can I merge 2 named ranges with a build-in function and give this merged range a 3rd name ?
View 4 Replies
View Related
Oct 7, 2006
I have cells containing data within C15:C22
I also have cells containing data within E25:E32
Some of these cells have the value '5' in them.
I want to have a running total of all the cells in these two ranges that have a value of '5' in them.
I did this formula:
=COUNTIF($C$15:$C$22,"=5")
This works okay but unfortunately this only covers the first data range. How do I specify the other data range in this formula?
View 5 Replies
View Related
Mar 21, 2007
I have a workbook that contains 52 spreadsheets (one for each week of the year). Each contain the same column headings. The columns contain both numberic and text data which I need to pivot. I can individually pivot each sheet to obtain weekly data but I would like to obtain year to date data throughout the year. How can I merge all of my 52 sheets together to utilize just one pivot table.
View 6 Replies
View Related
May 2, 2008
I have been asked to create an attendance worksheet where employee names and data are entered on a "main" worksheet and hours are entered on monthly worksheets. The names on the monthly worksheets are referenced from the main worksheet. Therefore, if I add a name and do a sort, the names on all pages will move, but the data will not. I imagine I will need an ID column to help sort. How do I make a macro to do the sort?
View 2 Replies
View Related
Oct 20, 2009
I am trying to apply data validation to a column of cells using named ranges. However, each row has a unique associated named range. For example:
A_______B
Birds____*
Dogs____*
Cats____*
I can easily apply data validation to these three rows separately using named ranges.
Ie three separate named ranges:
=Birds
=Dogs
=Cats
However, I need a way to quickly apply data validation to column B using different named ranges for each row because there are about 2,000 rows. Is there a way to reference text in the cells of column A that contains the name of the named range? Or maybe a bit of VB code that could do it quickly?
View 3 Replies
View Related
Dec 30, 2008
hello. i'm new to excel and i'm really hoping for some serious help here. i have the basics down however i'm really stumped at this point. Let me try to explain my worksheet. the worksheet has a sheet for data which is used for drop list values in the new patient template sheet. the new patient template which is just that a blank entry sheet that the user duplicates and adds a new patient to the worksheet to track the visits made by medical staff. there could be over a hundred new patient sheets (each named by the patient) at any given time.
here is what i need help with: my sheet is setup on a monthly basis so each patient has a total of 4 - 5 weeks listed with entries for everytime a nurse visits that patient. what i need to know is how to search the cell entries for the first time a visit occurred and the date it occurred and this needs to be broken down by the 1st of the month - the 15th and then again for the 16th - the end of month for every patient sheet in the workbook. the ranges are not together that i need to search for example f14:f20, i14:i20, f35:f41 and so on for the first 15 days of the month. this all needs to happen like in a macro or somehow automatically.
View 14 Replies
View Related
Feb 23, 2010
Once again, I'm seeking some further assistance to an issue that I have yet to resolve.
I have several rows of data which I'd like to be able to sort by the info in column "A".
The data rows are not all consecutive. The range for which the data resides are:
Range("A25:A73,A85:A105,A145:A193"), When I sort I want my data to stay within these limits/ranges.
View 8 Replies
View Related
Mar 25, 2014
Can you create a Pull-down List that contains data from Multiple Ranges.
If I have a List of Names Running down column A (A2:A10) another List of Names Running Down B (B2:B25) and a Third down C (C1:C15) each of them named Ranges ("List1", "List2" & "List3"), can I create a Pull-down list in Cell A1 that would include the names from all three ranges?
View 4 Replies
View Related
Jun 25, 2014
I have a requirement where I need to summarize multiple work sheets. And each work sheet as different range. Column names are same in each sheet but number of rows in each are different. Like consider there are 3 sheets with employee details. Each sheet has Employee Name, Employee Number, Employee Location. But in first sheet as 10 employees and second sheet has 20 employees and third as 25. So the requirement is I need to summarize all employees.
View 1 Replies
View Related
Feb 26, 2008
is it possible to refer to more then one named range in my validated list's source field? If not, how can i make more than 1 named range to be the source of my validated list.
View 9 Replies
View Related
Jan 26, 2014
I have been using a macro in Word created from pieces all over the internet (credit to a user named matt198992 for recursion script). The code prompts a user for folder, then runs a macro called Publish as PDF to all word files in the folders/subfolders.
I want to adapt the code in Excel, but I am having trouble. Error on the line "Workbooks.Open Filename:="Path & DirN".
[Code].....
View 10 Replies
View Related
Nov 7, 2012
Although I have seen lots of formulae to total a dynamic column, I found a brilliant formula on the Internet, as follows
HTML Code:
=SUM(OFFSET(INDIRECT("R1C",),,,ROW() -1))
The problem is I cannot work out how to adapt it to total across rows (which will expand with new columns).
View 5 Replies
View Related
Feb 14, 2008
Is it possible to display a name on a chart that is not the X or Y value?
I've been sent this macro which displays the school name on a chart when the mouse is on the data point - its all well and good but on the graph I am working on, the X and Y value are both values and I want the school name to come up as well.
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
'macro that gets chart to show user details of a point on the chart when the mouse is moved over it
Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX, myY As Variant
With ActiveChart
.GetChartElement x, y, ElementID, Arg1, Arg2
If ElementID = xlSeries Then 'checks to see which part of the chart the mouse is over
View 9 Replies
View Related
Aug 19, 2009
The solution to a challenge here does almost exactly what I need except it doesn't manage partial matches.
Function MatchColors(strValue As String, rngList As Range) As String
Dim regEx, Matches, i, strResult, bFlag
'Create and set the parameters for the regular expressions object
Set regEx = CreateObject("vbscript.regexp")
regEx.Global = True
regEx.IgnoreCase = True
View 9 Replies
View Related
Apr 27, 2012
I have a large spread sheet 3000 lines. I have two sheets on sheet 1 is the data shown
CSAMonthABCDEFGCSA01Jun/20111110000CSA05Jul/2011101101CSA03Aug/2011500000CSA06Jul/2011520000CSA11Jul/201100100
On the next sheet I need to convert the data so I can produce a pivot table. The fields required are value of the Product "A" "B" etc by monthly columns and rows showing Product for CSA.
ProductCSAJun/2011Jul/2011Aug/2011aCSA01 - - - aCSA02 - - - aCSA03 - - - aCSA04 - - - bCSA01b
CSA02bCSA03bCSA04cCSA01dCSA01dCSA02dCSA03dCSA04eCSA01e
CSA02eCSA03eCSA04fCSA01gCSA01gCSA02gCSA03gCSA04
The sheet is going to very large so need something that does not take hours to recalculate every time a change is made.
View 7 Replies
View Related
Nov 10, 2008
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is
Public Sub assignDVList(WSD As Worksheet, sListName As String)
Dim DVListName As String
DVListName = "DV" & sListName
Application.Goto Reference:=sListName
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & DVListName
It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View 4 Replies
View Related
Apr 21, 2013
I've recorded this Macro to sort the cells in a column alphabetically if any cells have content.
I would like to use the Macro on the whole workbook however the AB column range varies between the worksheets.
How should I adapt this Macro to sort simply to the END of AB column??
' PWRII Macro
'
'
Cells.Select
[Code]....
View 5 Replies
View Related
Feb 12, 2010
I am required to merge a group of blank cells in my Macro, but for the life of me I Cannot Get it to work. I have spent some time googling with no luck.
Basically I just wnt something like:
Range("O96:R96").Merge
Can anyone tell me if my Syntax is off at all?
View 9 Replies
View Related
Sep 6, 2005
Couple ways:
1) Format the cell as Text: Format->Cells->Text
2) Enter the zip code with a ' preceding the number, i.e. enter 00345 as
'00345
3) Enter the zip code with the formula ="00345" ...
View 11 Replies
View Related
Jun 2, 2012
In a big data sometimes I have identical rows (maybe the 'Quantity' column has different value). I would like to merge them into one and add the quantities together. I have to use B and C (I need both) to find out if these rows are identical.
For example:
A1=date B1=111222 C1=ABCD ... and G1=quantity (1)
A2=date B2=111222 C2=ABC ... and G2=quantity (1)
A3=date B3=111222 C3=ABCD ... and G3=quantity (2)[code]......
View 3 Replies
View Related
Oct 25, 2007
I need help creating a macro that will search through my excel spreadsheet and for every instance where column A isn't empty it should cut a range of columns from that row and paste them in a different range of columns in the row before it. It should then delete the row that it cut the columns from and keep searching until it has done this for the whole worksheet. I can modify which range of columns are needed, but it has been so long since I've worked with excel macros that I haven't been able to do it.
View 5 Replies
View Related
Oct 7, 2009
Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code?
The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number).
I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.
View 6 Replies
View Related
