Macro-Copy&Paste Fixed Data Ranges Into Last Empty Cell Of Specific Column
Apr 27, 2009
I have rather large Excel file that spans around 245 columns & has multiple users responsible for certain columns. Columns are chunked to provide data about a specific group. Every 72 hours, I need to provide to managers what’s currently on this file in a specific summary format.
I’ve recorded a macro that gets me half way there. Here’s where I’m clueless. After my macro deletes unnecessary columns, what remains are the columns for “Main Data” group + each of the 5 “Business” groups (Investors, Lawyers, Credit, Finance, and Support) which would have identical column headings.
(1) Name (2) Address (3) ID# (4) Control#
Furthermore, for each of the 5 “Business” groups, there are 9 slots (ie- a user can enter up to 9 unique entries for a particular group.)
Example- The Investor group contains 9 slots. Each slot will contain the 4 columns mentioned above. So there are 46 slots in total – 1 for Main Data, 45 (5 groups x 9 slots) for Business groups.
MAIN DATA (columns A-D)
Investor1 (columns E-H)… Investor2 (columns I-L)… Investor9 (columns AK-AN)
Lawyer1… Lawyer9
Compliance1… Compliance9
Finance1… Finance9
Support1… Support9
MY PROBLEM: I need a macro to now cut & paste all data from each of the 45 Business groups slots & then paste at the bottom of the MAIN DATA slot.
The final file would be 4 columns (as listed above) wide. Data from each of the 45 business group slots would be one below the other- all of it underneath the data in the Main Data group.
I attempted to record a macro where I’m copying a section’s data range starting from row2 to row4000 (I know there will never be 4000 entries. I did this just to ensure that all future data would be captured) then pasting that after the last entry in the Main Data.
Example- I’d copy data from Investor1 slot(starting at column E2-H2) & go about 4000 rows down. Then I’d press CTRL+Down Arrow key in column A & then click the down arrow one time to take me into 1st empty cell where I would paste my copied data. I was going to repeat this process for the remaining 44 slots.
I’ve added some extra entries to test the macro & the problem is that the recorded macro is pasting data in specific cell location in column A instead for looking for the 1st empty cell in column A & then pasting the copied data.
So far I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work. Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A. I've gone thought the net & tried to paste in a few VBA posts/tips that I thought would work.
Here's what I have so far from tinkering around. I need someone to tell me what to replace the xxxxxxx line with. This would be the language to tell Excel to paste copied data range into the first blank cell in column A.
Sub transposedata()
Sheets("ConsolidatedYTDReport").Select
Range("E2:H4202").Select
Selection.Copy
XXXXXXXXXXXX
ActiveSheet.Paste
End Sub
View 10 Replies
ADVERTISEMENT
Nov 30, 2008
I have a macro that automatically takes data from one sheet, copies it and pastes it on another sheet, but I want it to select the empty column that is to the right of the last column to contain data (so basically the next empty column). What code would I enter into editting my macro to be able to do this please?
Also, one thing I've tried to do with my macro is that when I select the data, it's suppose to select from the first name to the last. When doing the actions for the macro, I use "Ctrl + Shift + Down Arrow Key" to make the selection select the bottom of the list. However, when running the macro, if the list is longer (or shorter), the macro selects that number of cells instead of the action of just going to the bottom of the list. For example, if during the actions of the macro, I recorded using the Ctrl + Shift + Down Arrow Key that 50 cells were selected...next time if I have 60 cells, only 50 are selected, even though I used an action command which should select all the cells in that group (in this case being 60). Is there some code I can add to that so this would work correctly?
In case its required, here is the code from the macro:
View 11 Replies
View Related
Dec 13, 2013
I want to create a macro to paste some data into the next empty cell. Below is sort of what I need but this show it going to a particular cell, I need it to got to next open cell in the range from say BY3. So next macro run it would copy and paste the data from T3:T9 (always this range) to BZ3 then CA3, CB3 and so on.
[[Sub CopyData()]
'
' CopyData Macro
' Copy The Data To Build Graph
'
[Code]....
View 1 Replies
View Related
Jul 30, 2013
Is this possible using code: Copy Cell A1 and Paste in first empty Cell down Column D. This would be connected to a command button. Both Cell A1 and Column D are on the same spreadsheet.
View 3 Replies
View Related
Mar 25, 2013
I am trying to find a macro that look for last non empty cell in column A and them paste a formula/comment in all cells of column B.
View 3 Replies
View Related
Nov 4, 2009
I want to copy some data from internet and paste it in to worksheet location Range A1:A30. the data look like following information.
LocationKuwaitP.O.Box:4819 SafatAddress:ShuwaikhTel:-22253580Fax:-24343645Category:CONSTRUCTION COMPANIES
the above information have following header ie. Location, P.O.Box, Address, Tel, Fax and Category.
every time one of the header inforamtion is missing ie. Address or P.O.Box, Or Fax, due to this problem every time cell address of information aganist header is change. supose in first copy/paste Tel inforamtion is in A15, but second time it may be on A13, and 3rd time it may be on A17 and so on.
I want to make a macro which search header ("Location", "P.O.Box", "Address", "Tel", "Fax", "Category" ) from a Range A1:A30. than down two steps to copy header information (75325412) and paste in fixed cell location C1:C5 (C1=Name, C2=Location, C3: P.O.Box, C4=Address, C5=Tel, C6=Fax, C7=Category) in same sheet. and repeat the steps to copy information aganist header from all header in Range A1:A30.
View 9 Replies
View Related
Dec 29, 2009
Copy and paste data if row in Column N is empty.
Here is my current code
View 2 Replies
View Related
Dec 9, 2011
I need the code to paste my selection in worksheet SORT, into the first available cell in column A. Intention is not to overwrite existing values in column A.
'
Sheets("tis").Select
Range("AC2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("SORT").Select
'I need the code to paste my selection in the first available empty cell in column A, so I don't overwrite any existing values in column A
End Sub
View 2 Replies
View Related
Aug 23, 2007
I need my macro to copy and paste from one worksheet to another, which is fine. However I need the copied value to be pasted into the last empty cell in a specific column. It is currently pasting each copied value into the last empty cell in column 'A' regardless of which column I specify it to copy to in my code.
View 5 Replies
View Related
May 9, 2014
I am using a macro to determine the last row used in a sheet.
Code:
Sub sbLastRowOfAColumn()
'Find the last Row with data in a Column
'In this example we are finding the last row of column A
[Code]....
I now need to select cell d22 in "New Client Invoice" (sheet) and paste in in the row determined above but in column I of sheet "Transactions".
View 3 Replies
View Related
Apr 10, 2008
I am using Excel 2007. I have a worksheet called January and I have a range of formatted cells in 'January' between B15:18 and BA15:18. Some contain formulas, others are just formated to size and or color. I want to copy and paste this range of cells (B15:18:BA15:18) to the next available unformatted or blank cell of the column of the same worksheet by pressing a button. I also need the newly pasted range to reset to their original formatting assuming data was entered before the button was pressed.
View 5 Replies
View Related
Nov 27, 2008
I would like a macro that will select the 65536th row to be used to paste format from rows 2 to the last empty cell in column a.
View 5 Replies
View Related
Feb 13, 2013
I want to count the number of rows in a specific column up to an empty cell and assign this value to a cell. I don't want to count the total number of rows but instead I want the number of the first group of rows.
For example, column A may have cells ranging from row 2 to 10 and then from row 12 to 20, so I only want to count the first group.
The below code counts the total which is not what i need.
Code:
Sub test()
Dim Mycount As Single
Mycount = Application.Count(Range("A:A"))
Cells(1, 4) = Mycount
End Sub
View 2 Replies
View Related
Mar 15, 2013
I am looking to create a macro to be assigned to a button that copies the last row of data entered and then pastes it to the last empty row on a different worksheet. This is a dummy spreadsheet to work with (I have more data, but the concept is one in the same). Sheet1 ("Branch1"), Sheet2 ("Branch2"), and Sheet3 ("All"), the names in brackets are names of the sheets, but for ease I'll refer to them as Sheet1, Sheet2, and Sheet3. I have columns beginning in B as follows: Date, Branch, Currency, Coin, and Total (the branch and Total are tied to formulas, however I just need to the text values and formats to come over to the other worksheet). have the portion regarding the copy of the last row in Sheet1, however it won't PasteSpecial.Selection in Sheet3 as it says the cells are not sized or formatted correctly.
VB:
Sub CopyB2()
lr2 = Sheets("Branch2").Range("B" & Rows.Count).End(xlUp).Row
lr3 = Sheets("All").Range("B" & Rows.Count).End(xlUp).Row + 1
Sheets("Branch2").Range("B" & lr2).EntireRow.Copy Sheets("All").Range("B" & lr3)
End Sub
View 1 Replies
View Related
May 8, 2014
I'm never done a macro before and of course I'm very new to excel. I have a worksheet for lessons learned that have multiple sheets for each state. I want to create a macro that every time I enter a new data into a row and hit "Add to summary sheet" and it copy and paste the the next available row in summary sheet.
See the attached file for my workbook : Book1.xlsx‎
View 9 Replies
View Related
Feb 21, 2014
I have this macro and at the present time, can only compare two worksheets. One of my worksheets contains column headers with data, the other worksheet just contains column headers. The macro will match the data against the column header and paste below. I'm doing it this way because I have several tables I export from a MySQL database, but some of them have more fields than others so I was trying to get all of the data to line up in under the correct column header from each table. I need editing the macro to allow for
1) More than one worksheet to be analyzed at a time
2) If the first request is not possible, I would like for the macro to paste results at the next empty cell. As of now, it will only copy one at a time (which requires me to house the data in another worksheet everytime I run the macro).
Below is the code:
Dim ws1 As Worksheet, ws2 As Worksheet
Dim HeaderCells As Range, Hdr As Range, hdrFIND As Range
Application.ScreenUpdating = False 'speed things up
Set ws1 = ThisWorkbook.Worksheets("HEADERS") 'Define name of worksheets
Set ws2 = ThisWorkbook.Worksheets("B")
[Code] .......
View 1 Replies
View Related
Oct 29, 2008
I am trying to create a macro that will allow a user to click a button, enter a text string that will be an email address into a field and have it added to the first empty row in column A or column C on sheet 1. I have not been able to record this with the macro recorder. The input box will be in cell E9 on sheet1.
View 7 Replies
View Related
Jun 14, 2013
I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken
Cow
Donkey
Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
View 3 Replies
View Related
Jul 13, 2014
Copy paste specific column with specific heading using macros. Roll No
Name
1
John
2
Abdul
3
Baig
1
I Want the Macro to find the column with heading "Name"
2
Copy the list from Name till the last Cell that is B1048576
3
Paste in Sheet2 Cell A1(Please see result in Sheet2)
View 1 Replies
View Related
Mar 24, 2007
I am trying to write a macro to do the following: Loop through a range of cells on a worksheet Sheet1 M17:M46 and if there is text in the cell then Copy all the data to the left Of Coumun M in the same row and paste the values to the next open row of a range A17:L46 on another worksheet (sheet2.) Basically the text in column M is an idicator to add the data to an order sheet. If there is no text don't add the data , look in the next row etc.
I'm not sure if this is the way to go
Sub CopyRows()
Worksheets("sheet1").Select
FinalRow = Range("M65536").End(xlUp).Row
' Find the last row of data
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column M
ThisValue = Range("M" & x).Value
' could be any value
If ThisValue = "yes" Then
Worksheets("sheet2").Range("A" & x & ":L" & x).Copy
Worksheets("sheet1").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
ActiveSheet.PasteSpecial = xlValues
Worksheets("sheet1").Select
End If
Next x
End Sub
View 4 Replies
View Related
Nov 27, 2007
i need a method to find the end value in a row (right side) and select the empty cell to its right to paste data in.
problem is the row where this data is may change so using
limit = (row, col).end(XlRight).Col
View 9 Replies
View Related
Mar 24, 2009
There's one sheet that has a list of customers (this is updated very frequently). These customers are either ‘New’ or ‘Existing (as listed in a corresponding column) - would it be possible to have a macro that, when run, will place a copy of existing customers into a second worksheet (“view list – new”), and a copy of new customers into a third worksheet (“view list – existing”), with the main worksheet still listing both existing and new customers.
As the main sheet that lists all customers is updated very frequently (with customers being added and removed throughout the day), would it be possible to clear all contents of the other two sheets that each list of 'existing' and 'new' customers will be copied into, before the two lists are copied into each of the two sheets (just to ensure there isn’t any data in there from the previous time each list was copied in).
I’ve given this a go via the ‘record macro’ function - some users of this WB use excel 2000, and others use excel 2007, but it only seems to work on excel 2007 (which is what it was recorded on)…
View 3 Replies
View Related
May 20, 2008
I've searched for a solution on the board and the internet too but to no avail; I am trying to create a macro that will select the entire column based on a cell reference G1 which is infact a Date.
Column G to Column S are pre-defined with a specific Date in row 8. If the Date in one of these columns (G8 to S8) match with the Date in cell G1, say it finds a match with H8, then I want to copy the range F14:F2000 and paste as values in range H14:H2000. The possibility of a match found is limited to a single columns from G to S.
View 9 Replies
View Related
Aug 29, 2008
I'm trying to find a macro that will copy data from the areas of B120:E179 and I120:K179 for example (linked to another worksheet within the workbook) and special paste (Values Only and skipping blank cells) it to the next available open cell up top where basic data entry will be taking place B10:E29 and I10:K29. I need it to only copy/paste the rows with data (skipping all cells/rows with no data) and once it is finished coping I will need it to place an "X" in column M next to the row that it copied data from. I would also need it to reference the data in each row from B to E and if there is an entry say on B14 to E14 that matches it but if I10 to E29 are blank then paste that information on row 14. If it does not match or if those columns are full then paste on next available line.
I hope I'm making sense here. This is for a vehicle tracking log between checkpoints. Each driver and info will be listed on each row. Columns B through E will contain information for each driver: name, badge, #passengers, and vehicle #. The log lists location, time, and destination for outgoing travelers in columns F to H. Incoming info is listed on Columns I to K............
View 4 Replies
View Related
Aug 17, 2009
i have about 80 files that all have the same structure. I also have a general excel file that I need to fill with values from each of the 80 files. I'd like to use a macro that can be applied to each of the 80 files at a time (or at the same time, if that's possible) so I can copy the cells (only the text, not the formula) I need from each individual file to the general sheet.
e.g.,
each file is:
xyz 123
abc 456
ghi 956
I need to copy the "123" to a cell in the general sheet, "456" to a different cell in the general sheet and so on (to be able to choose which cell in the general sheet I am copying to).
View 12 Replies
View Related
Sep 30, 2010
In columnA I want to jump to the next blank cell in a list that continues to grow. So the first time I run the macro I want to go (blank) cell A5 to paste in some data. The next time I run it, it would need to go to cell A6 and so on.
When I do this manually, I simply hit ctrl down-arrow (takes me to the last cell w/data) and then one more down-arrow to take me to the empty cell below.
When I do this in the macro it seems to remeber the cell as an absolute reference (if that's the term) and so it just copies info in the same location as previous, instead of the next empty cell.
View 5 Replies
View Related
May 31, 2007
From mySQL database, i am getting a ton of data that is all being inputted into a single excel cell. Within this cell, there are certain data points that i wish to obtain (Bank #, SSN #, Routing #). There are multiple difficulties in obtaining this information.
1. these cells contain the same fields, but different number of total characters (differing in names, addresses, etc.)
2. some of the data points (like SSN) are inputted incorrectly, so even though a SSN is only 9 digits/characters, I may have to output all 11 that the SQL database placed within the cell.
Can I get some help? I'm thinking of a search function/macro within excel, because all of the fields show up correctly.
something like (i know this doesn't work)
= or("cellA1" contains/finds/function SSN,return the 9 characters after "SSN ")
um...yeah. difficult to explain, i'm sorry!
edit: not sure what is wrong the file. I have attached a new one.
View 9 Replies
View Related
Dec 17, 2012
I have a spreadsheet and in column D, I have look up formula which looks up value from another tab. What I want to do is if the look up finds #N/A or blank cell. There should be a message box appear warning that all the cells in column D might not be updated properly.
View 4 Replies
View Related
Oct 1, 2013
Right now I use Range() to define where I want something pasted. How do I use get away from that to just use the next empty row, so I don't have to keep changing the code everytime the data sheet expands?
Here is an example of what I'm using:
Sheets("Sheet1").Select
Range(Range("F4"), Range("F4").End(xlDown)).Select
Selection.Copy
Range("BX4").Select
ActiveSheet.Paste
Range("BX3").Select
ActiveCell.FormulaR1C1 = "Header"
View 9 Replies
View Related
Dec 22, 2008
I have two worksheets. One that contains material pricing and another that has misc data, formulas, etc. I have 100 rows of data with 2 columns. Column A I have numerical values. Column B I have a formula =(A1/36.4)*sheet1!C7. I try to drag down the formula but the value for sheet1!c7 changes as I go down (sheet1!c8 then sheet1!c9 and so on). How can I drag down the formula and have the SINGLE value from worksheet 1 carry to all rows?
View 3 Replies
View Related
