Combining Worksheets With Unknown Number Of Rows
Dec 16, 2009
I am trying to find a way to combine two worksheets with identical columns and an unknown number of rows. Both sheets use columns A through K.
I want to add a new sheet called "Combined orders", then copy data and headers from Sheet1, paste it to Combined orders, copy data only from Sheet2, and paste it on the row after the last row of data from Sheet1.
Sounds easy but I am easily confused by the unknown number of rows in each data set. Can someone please help?
Also, are there a few lines of code that I can copy and paste into new macros that will make the unknown rows problem easier for me in the future? (i.e., instead of just showing me the code, can you also please explain the concept behind it so I can learn for future reference?)
View 7 Replies
ADVERTISEMENT
Nov 4, 2009
I have the following setup,
Down column A I have a list of jobs, job 1 , job 2 etc
Down column b I have either a blank cell or other data (will be numbers but is irrelevant)
What I want is a formula for a cell that tells me which titles in column A have any data at all in column b. I need it to look like the following : Job 1, Job 2, Job 4
assuming b1 b2 and b4 have any contents and b3 and b5 and onwards are empty
I am seeing that concentate gives me close to what I want but the number of rows in column a and b change constantly and also may be above 30
View 9 Replies
View Related
Sep 20, 2007
The colours are just to mark the ranges
As every month is different the number of sheets adding up to the Red sheets(week total)
will change and the same with the Blue sheet(month end total)
Is there a code I can run for this summing up to be done?
Yellow is where data is entered
Red is where the Yellows range sheets need to add up before it
The Blue is where all the Red range sheets need to add up
View 9 Replies
View Related
Jun 28, 2006
I have a macro set up to copy and paste data from worksheets into worksheet 1. But the number of worksheets often changes. Is there a way to write this to include all worksheets even if the number changes?
Windows("0285 WORKING FILE 0406.XLS").Activate
Sheets(3).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R501C1"
ActiveSheet.Paste
Sheets(2).Select
Application.Goto Reference:="R500C33"
Range("A9:AG500").Select
Range("AG500").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets(1).Select
Application.Goto Reference:="R1001C1"
ActiveSheet.Paste
Sometimes I will receive this file and there will be additional tabs. I just never know how many.
I copy down 500 rows knowing there will never be more data than that. And for each worksheet I paste to sheet 1 I add 500 rows to not paste over other data.
I would like the computer to read it as "select last indexed worksheet, copy paste to index 1, repeat while selecting the left adjacent worksheet until you reach index 1.
For each new worksheet selected add 500 rows to the last pasted amount."
View 7 Replies
View Related
Sep 15, 2012
I need to put a value on every line having "file:" in the second column. The value requested is a sum of the numbers in the forth column following this line until the next "file:" line.
I know it is not trivial, but sure it is possible.
57
file:
HIRES-~1
#VALUE!
58
208
1
1
[Code] ...........
View 9 Replies
View Related
Sep 30, 2009
I am working with 2 workbooks the first one "LH Enrollment" is used to enter information for new students being enrolled in the program. After the information is entered the macro is then copying the information to the second workbook "LH Children Records". The part I'm stuck on is that I now need to sort alphabetically by last name which is entered on Sheet 1, Column B of "LH Children Records". The number of columns will stay the same but the number of rows will change each time a new child is added. Eventually all this information gets copied over to several other workbooks, but I'm stuck trying to sort. I have attached both workbooks
View 5 Replies
View Related
Jan 24, 2007
In the code below, a formula is placed in column F to compute the total of that column. I first find the number of rows and place the formula in the cell below it. I'm dividing the sum by 2 since there are subtotals in the column.
Sub AddColumn()
Dim NumRows As Long
NumRows = Range("A65536").End(xlUp).Row 'get the row count
NumRows = NumRows + 1
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:F308) / 2"
End Sub
The problem with the code is that I don't really know that the last row in the column is F308. I need to replace that part with a variable. It will be something like this (which I know is incorrect):
Worksheets("Report").Cells(NumRows, "F").Value = "=SUM(F9:NumRows) / 2"
How would I use a variable in the SUM function in place of the cell names? Should I even be placing the formula in a cell, or should I compute the sum in the macro and place the value in the cell?
View 2 Replies
View Related
Dec 15, 2009
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
View 4 Replies
View Related
Apr 26, 2007
For example
Name Address Phone # zip
Danielle 4561
Danielle 9852
Danielle 22
Danielle 69
Joe 895
Joe 28
John 9821
John 1114
John 698
Say I did a search for Joe. I want to report back all the addresses in which he resided but there's no way to tell how many rows of data each person has. Joe has 2 rows, Danielle has 4 rows and John has three. How do I report back all the relevant rows?
View 11 Replies
View Related
May 28, 2014
I am trying to come up with a macro that selects values from one sheet and inserts them into another sheet. The number of values will change each time based on the user's entry, as well as the number of times that each entry should be pasted.
For example:
Entry: X | Y | Z
Number of Times to be Inserted to New Sheet 3 | 2 | 1
Result:
X X X Y Y Z
I have spent a while trying to figure it out, however the best I can come up with is using an array, but I can only get one value from the array to paste multiple times:
(*Note: In my testing, I didn't insert into new sheet or set up the array to handle different values, I was just trying to get the basic idea to work)
Dim A(1, 3) As Variant
A(1, 1) = Range("C3").Value
A(1, 2) = Range("D3").Value
A(1, 3) = Range("E3").Value
[Code].....
View 3 Replies
View Related
Oct 26, 2009
I've been working on a spreadsheet and these forums have been a great help. I'm now at the very last section and, surprise surprise, it's also the hardest!
I'm creating a stock trade recording sheet. I have a userform ask the user to enter a date, a time, the number of stock purchased, and the price of the stock. These are then entered in a new row.
Now what I want to do is have summary cells which say how many stocks were purchased and the total profit made for each day. Since each time is given its own row, I can't know in advance which rows to sum over. I also don't know on which days a trade was made. So a summary cell should only exist if a trade was made that day.
Could I do something like.... check if the date matches then sum over all the values for that date? So if column A has the dates, can I say "Search which rows in column A have this date" then "for those rows, sum column C"?
Also, how would I create a a row for each traded date's summary cell and enter the date in it? I've attached a spreadsheets which manually demonstrates what I want to do (no macros) and a spreadsheet with what I have so far (basic macros).
Any and all help much appreciated, I just need to get my head around creating and dealing with variable ranges. Is that a really advanced task? I don't think this is a one line solution so please bear with me while I make mistakes!
View 14 Replies
View Related
May 25, 2008
I am trying to average a range which continually changes depending on the amount of registries entered in a given week. for example, one week there may be 5 registries which would fill five columns while another week there may be 15 registries, once again filling 15 columns. I would like to find the average of x amount of columns.
My initial approach was to select the first blank cell to the right of the data and then find the last filled cell (which should be the first column of data.
with this range i tried hopelessly to use the average function (did not work):
Sub averagemake()
Dim iLastColumn As Integer
Dim Rng As Range
Sheets("All Data(Values)").Select
Range("A6").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
iLastColumn = Cells(6, Columns.Count).End(xlToLeft).Column
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-iLastColumn]:RC[-1])"
End Sub
View 5 Replies
View Related
Apr 24, 2006
Need to sum through an unknown number of row generated by advanced filter at run-time
Formula at C16
Data starts at row 57 (from copy/paste - advance filter)
Last row unknown
Criteria to match in A16
Data to be evaluated in B57:B (row unknown)
Found this formula on microsoft.public.excel.misc
=SUM(A57:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))
Which I modified formy start row
View 9 Replies
View Related
Feb 13, 2012
have a function that takes an unknown number of ranges (worksheet cells) as arguments that can then be joined with the provided delimiter.
It should ignore null values.
I saw that someone posted the following line of code:
Replace(WorksheetFunction.Trim(Join(myArray)), " ", ",")
So I guess my real question is how to pass an unknown number of arguments into a function and put the values in an array.
View 3 Replies
View Related
Jul 15, 2009
I need to know the function to copy an unknown amount of rows. After a certain row it should copy everything.
So like after row A6 it copys every row below it with data in them then I want to paste it in another worksheet. I dont want to just do
Rows("6:65000").Select
Selection.Copy
If someone could point me to a good site that explains alot of useful VBA commands that would help me alot. Most of my problems come from just not knowing the full function names.
View 10 Replies
View Related
Jul 1, 2014
I have a workbook has many worksheets, I would like to be able to delete worksheets if, for example, the number of rows in the worksheet is less than 100 rows.
View 3 Replies
View Related
Aug 1, 2008
How to I add data to an existing summary sheet, in a specific cells, from an unknown number of sheets?
Tickets will be generated with unknown names.
I want the summary sheet to be able pull the ticket numbers into the top row of the summary sheet, to an undefined number of sheets.
As well I would like the summary sheet to pull the job# on each ticket and the hours for each person on each ticket.
View 5 Replies
View Related
Mar 19, 2008
I'm working to build a macro to help clean up data I download out of our company's online resources. I know that the data will run from column A to N but the number of items (and thus number of rows)will change. I need to sort all used rows except for the last one (which containes totals and I don't want to include in my sort). So right now I'm just trying to figure out how to select from A3:NX, where x is the row above the last used row.
What I've managed to find so far on the forums is
Offset("$A$3", 0, 0, (Match(9.99999999999999E+307, "N:N", 1) - 1), 14).Select
which I think should find the last row in N to have a number in it, minus 1. However I'm getting a compile error that function or sub are not defined. (seemingly refering to the Match, but I'm not sure).
View 6 Replies
View Related
Aug 21, 2007
Four worksheets (A,B,C,D) all begin with row2 and end with the last non-blank cell.
What VBA code would merge the information from the four worksheets to Worksheet E?
View 9 Replies
View Related
Jan 21, 2014
I have two worksheets...let's call them worksheet A and worksheet B. How can I get a list of people who are on worksheet A AND also on worksheet B? I would actually like those names on a separate worksheet.
View 6 Replies
View Related
Oct 15, 2009
I have a project that requires merging several worksheets into a "Master"worksheet in the same workbook. But because each of the worksheets have a different number of columns, I need to add each worksheet to the Master horizontally. The finished Master should appear just like each of the origional worksheets are lying side by side.
The code below adds the Master worksheet, copies each of the worksheets, and inserts it into the Master. So far so good. It does so perfectly on the first worksheet to be copied. But as it loops through the rest, the column widths get messed up. Is there any way to keep the column widths the same as the origional on my master worksheet?
View 5 Replies
View Related
Jun 3, 2012
I would like to use VBA to combine specific sheets. I have a workbook containing about 15 sheets but want to combine 6 specific sheets. All the sheets have the exact same headers and number of columns but the number of rows are different.
I found this VBA code that works but it combines ALL of the sheets in the workbook. How to modify this to combine specific sheets. How to combine all visible sheets if that's possible.
Here is the code I found...
Sub CopyFromWorksheets()
Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Master Worksheet
Dim rng As Range 'Range object
[Code] .........
View 1 Replies
View Related
Sep 9, 2006
I need to make named ranges from an unknown number of columns(at least 1) each with an unknown number of rows. Each column has the name of the named range as the first row, and then a variable number of rows containing part numbers.
I can do it 1 by 1, but id rather do it in a loop so that blanks dont cause errors. there will be different people using versions of this sheet with different model/part number information What i've tried: Count number of colums with row 1 containing data (11 max, which is more than will ever be used) add into array(I know i dont really need to add into the array, but i might use it later for some other code). The problem i'm having is finding the range of rows that need added to the named dynamic range and adding it.
modelcount = Range("G7") 'G7 (for now) contains =COUNTA(H1,I1,J1,etc)
For i = 1 To modelcount
Redim Preserve Models(0 To i)
Models(i) = Cells(1, i + 7)
Range1 = Cells(2, i + 7).Address(xlA1)
lastRow = Cells(rows.Count, i + 7).End(xlUp).Row
Range2 = Cells(lastRow, i + 7).Address(xlA1)
Reference = Cells(2, i + 7).Address(xlA1)
ThisWorkbook.Names.Add Name:=Models(i), _
RefersTo:="=OFFSET(Reference,0,0,counta(Range1:Range2),1)", Visible:=True
Next i
This gets me the range i need, but doesnt create the named range properly. If i go to insert>names>define, the named ranges are created, but they dont relate to the data in any columns. It shows the variable names rather than the cell range the variable represents.
View 2 Replies
View Related
Nov 25, 2009
I am trying to combine two worksheets into one worksheet. In the first worksheet I have countries in the first column. In the second column, I have the statistics for how many people belong to a certain religion. Then in the second worksheet, I have the countries in the first column and birthrate in the second column. How do I combine this information into one table in a third worksheet?
View 3 Replies
View Related
Feb 19, 2014
for ii = 1 To 3
shts = "XX0" & Str(ii)
Create_worksheet (shts)
Next ii
when I run this, my worksheets are like
"XX0 1"
"XX0 2"
"XX0 3"
what's wrong that I did? How can I do "XX01"...?
View 3 Replies
View Related
Jul 26, 2007
would anyone be able to write the code that will combine the data from all rows from all worksheets within a workbook. i've struggled with this one....
here is the deal:
all worksheets have the same columns and column headings but differnet amount of row counts. the width of the sheets is to Column "M" or "13" and there are no blank columns.
There is no need to have the columns headings repeat within the compiled worksheet.
the amount of worksheets will vary depending on when i run it so it will not be able to use specific naming conventions.
View 9 Replies
View Related
Jul 24, 2009
I've been looking through code online, but i'm still not understanding how I can combine data from multiple worksheets into one master worksheet.
I have 16 worksheets, with identical column formatting (6 columns), but with different amounts of rows.
The master sheet is called Master Holdings. The first worksheet's data should be placed stating on row 2, and the second sheet's data should follow right after sheet 1's data, etc...
View 9 Replies
View Related
Feb 15, 2007
mapping data. Attached is a sample of what I need help with.
I have a Two Worksheets with Data and a third containing the template with which I want the first to to combine into.
The first sheet looks like this:
WWID | YEAR | LITER | MAKE | MODEL | SUBMODEL
The 2nd sheet:
HOUSE_ID | YEAR | LITER | MAKE | MODEL | SUBMODEL | Part_No
I want to map these two sheets together into the 3rd that looks like this
WWID | HOUSE_ID | Part_No
by using the similar data found in the first two sheets (year, liter, make, model, submodel)
I've attached a sample of what I need..
View 4 Replies
View Related
Jun 10, 2013
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]....
View 3 Replies
View Related
May 12, 2006
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 Related
