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?)
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
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?
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."
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.
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
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):
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?
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:
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?
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
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!
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
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.
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.
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.
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).
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.
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?
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
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.
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.
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?
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.
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...
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 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
