Macro To Copy & Paste Variable Range
Aug 15, 2007
I am trying to write a very basic macro to copy and paste, but when I record the keystrokes, step 4 (see below) seems to be recording absolute cells and not relative (?). At any rate, I can't seem to figure out how to get it to select the text the way I want it to. These are the keystrokes I want:
1. Ctrl-c in the current cell <copy the current cell>
2. DownArrow <move down one cell>
3. Shift(hold)-End-Down <select the blank spaces from here down until the next occupied
cell>
4. Shift(hold)-Up <move the selection up one from the bottom to exclude the occupied cell>
5. Ctrl-v <paste>
6. End-Down <move down to the last occupied cell>
I have set the record tool to record "relative" references.
Below is the code that was recorded. It's line 5 that's not working - how do I fix it? What is the command to tell the program to press the shift key and an up arrow?? Such a simple thing, and yet I can't find the answer anywhere! Things sure have gotten complicated since the old Lotus 1-2-3 macro language...
Sub Fill()
Selection.Copy
ActiveCell.Offset(1, 0).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A17").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
End Sub
View 8 Replies
ADVERTISEMENT
Jul 16, 2009
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies
View Related
Nov 14, 2012
Sheet 2 has 3 cells with values:
C14, C15, C16
I need VB code to:
copy the values in those cells
Return to Sheet 1, let the user click a cell in any row in Colum F, then Paste them (Special, Values and Transpose)
View 4 Replies
View Related
Jan 13, 2009
I am working on a macro where I am creating a formula to string together some text columns and then copy the formula down the entire column. The data source I will be performing this on will change in number of rows period to period. The data would be in columns A,B & C and the formula is in D. The formula in D is stringing together the data in AB & C and then I want to copy and paste that formula down to the bottom of all of the data. What would the code be for the copy and paste with variable rows?
View 9 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Oct 12, 2011
I wish to copy a range which has the variable adderss in a cell.
Example the address is located in D2 and the address in that cell is B4:R113
I want to select the information in D2 for copying.
View 4 Replies
View Related
Oct 15, 2012
I have a macro that allows me to copy the value of a dedicated cell (I4) and paste it at the start of a range (N4:S4) via a Form Button. When the cell (I4) value changes, I can run the Macro again and it will enter the new value in the next cell along in the range.
This works well across one row. What I would like to do is use this arrangement on more rows. The ranges would be directly underneath eachother.
Is there an easy way of doing it? Or will I have to write out a new Macro for every row?
View 9 Replies
View Related
Mar 9, 2009
I have a macro that copies and pastes into another sheet. When I add a row of data into my spreadsheet I want the macro to be able to change the range size and copy and paste the add row with the prior data.
View 2 Replies
View Related
Aug 9, 2009
I created a macro that copies a range from one worksheet to another as shown below, which correctly copies everything including formulas.
View 9 Replies
View Related
May 24, 2007
Is it possible to write vba code to copy the range A1:A20 from my workbook to a new workbook?
View 4 Replies
View Related
Aug 3, 2012
I have a macro that takes info/data from multiple sheets in the Basin workbook and summarizes it into the Median Database workbook. I need to copy the values in range B5:EM5 in every sheet (each sheet name is site #) in the Basin workbook and paste that range into the median database workbook row with the corresponding site.
Here is what I have so far
VB:
Sub Median_Database()
'This Macro takes the median from each station tab in the current Basin workbook
'And inserts it into the Median Database workbook
[Code]....
how to grab the sheet name and insert it in the median database. The problem is the copy/paste of the range in each sheet. The macro locks up every time.
View 2 Replies
View Related
Mar 26, 2014
I have recorded the below macro and I am not very happy with it. enhance the macro and add the following:
I need to copy the range "ROW" and past it below the range "MENU". Also the range "ROW" (ROW = copy of 3 line with some cells merged) is hidden.
I need to unhide and hide back the rang ROW. Right know I have to leave the range appearing otherwise my macro doesn't work.
HTML Code:Â
Sub ADD()
' ActiveWindow.LargeScroll Down:=2
Range("ROW").Select
Range("ROW").Activate
Application.CutCopyMode = False
Selection.Copy
' ActiveWindow.LargeScroll Down:=-2
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Range("A7:A9").Select
End Sub
View 1 Replies
View Related
Mar 16, 2012
I have some code that uses a form so you can choose the folder that contains all the spreadsheets that I need to copy. My issue is I have about 20 spreadsheets, some have the tab labled M3 and others Sheet1. Next is each spreadsheet has a different amount of rows in them. My data will always start in cells B4:N4 but may 20, 100, 1000 rows long.
My code works but only copies the first four rows of the last sheet open.
Code:
Private Sub cmdbtnProcess_Click()
Check_Path
End Sub
Sub Check_Path()
If txtboxPath.Value = Empty Or txtboxFile.Value = Empty Then
[Code]...
View 2 Replies
View Related
Jul 12, 2007
Using a macro, how do I tell Excel to:
1. Copy the xth row and paste it before the yth row
2. Copy the mth column and paste it before the nth column
3. Copy the cell a,b and paste it in he position c,d
where x,y,m,n,a,b,c,d are variables, the value of which the user inputs, say in cells A1, A2, A3, A4, A5, A6, A7 and A8 respectively.
In other words, I need to read numbers m and n which are the values of cells A1 and A2, then I need to go to column number m (for instance, if m=4, I go to column D), copy the whole row, go to column number n (for instance, if m=6, I go to column F), and insert the copied column.
Also wondering if VBA provides any way to quickly horizontally and vertially flip copied tables while pasting. While I am aware of the transpose function which swaps rows with columns, I am looking for a way to reverse the order of either the rows or the columns as per my need, without having to copy-paste row-by-row or column-by-column.
View 9 Replies
View Related
Feb 5, 2014
I have data (departments) going down column A Starting in Row 4.
Then I have data (months) going across Row two. Both of these will vary in length.
I need to copy a formula from cell C1 and paste it into cells where there is a value in column A and a value in Row 2.
View 7 Replies
View Related
Sep 29, 2012
I'm in the middle of building a code that will look at the value in Sheet: "DATA" column "J", for each row that matches a criteria that I pull from a cell - Sheet: "Test" Cell: "C1".
The number of rows varies. I want to paste columns: "I2, K2:P2, U2:AJ2" when the value in Sheet: "DATA" column "J" matches the cell "C1".
I'm using a Command button to click every time I want to generate the filtered data. And I would prefer the code to always copy data starting at Sheet: "Test" Cell: "K2".
I've copied, below, my test code that I have so far which only copies row 2 from the Sheet: "Data", and copies it into Sheet: "Test", starting at K2.
VB:
Private Sub CommandButton2_Click()
If Sheets("DATA").Range("J2").Value = Sheets("Test").Cells(1, 3) Then
Sheets("DATA").Range("I2,K2:P2,U2:AJ2").Copy
Sheets("Test").Cells(2, 11).PasteSpecial Paste:=xlValues, Operation:=xlNone
End If
End Sub
View 3 Replies
View Related
Jan 29, 2014
I would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
View 1 Replies
View Related
Oct 1, 2012
I have one workbook that needs two macros.
On the "Complete Backlog" tab of my workbook, I want users to enter in the requested information based on the column header. Then I would like a Macro attached to a button that says "Refresh" that the user would click after they have entered in all of the information. This macro should look in Column M (WIP Status) and if any of the cells say "Close", it should Cut the entire row from the spreadsheet(Ex. A2:M2) and Paste it into the speadsheet titled "Closed Jobs".
This is so that as jobs are closed/finished, they are removed and stored on a separate sheet. The items would have to be pasted so that it pastes into the next available row - not just on top of each other.
I also need another macro that i can put into a button that doesn't "delete" a row from the sheet, but just copies over to another sheet - so that there are two instances in the workbook.
If would look something like: If a cell in "Column G / Director" of the "Complete Backlog" speadsheet is equal to "Snodgress" then copy columns A-L of the same row to the spreadsheet titled "Snodgress" - of course skipping down the rows to the next blank row.
.....is equal to "Herr" copy row to "Herr" spreadsheet.
....is equal to "McCormick" copy row to "McCormick" spreadsheet.
and so on.
View 2 Replies
View Related
Jul 19, 2012
I am writing a code wherein I want to assign specific excel range to a declared variable and then paste this excel in outlook message ody but I am finding difficulty in assigning that range to variable " brng"
I think code is right and issue is there in excel setting.
Code:
Sub mailer()
'
'
'
Dim Ash As Worksheet
Dim brng As Range
Dim OutApp As Object
Dim OutMail As Object
'Windows("Copy of FF RPL REPORT_JULY").Activate
[Code] .......
View 1 Replies
View Related
Mar 2, 2008
I tried searching for code for each part of the task separately and trying to piece together multiple macros, that did something similar, but I’m not have a lot of luck, so I’ll break it down into two treads, I hope this will follow the rule of one question per post, but if I should have separated this request even farther as two treads, please let me know and I’ll be happy to comply.
I was given a spread sheet with a macro that loads data from a SQL Data base (not mine), it returns anywhere from one row to 100’s of rows data. Since it’s not my spread sheet and macro, I copied this sheet to my destination workbook. The Data loads in 7 columns starting in B4:H4, with the column headings titles: Acct#, Id#, Name, Qtr1 Totals, Qtr2 Totals, Qtr3 Totals, and Qtr4 Totals. Data cells in spreadsheet not formatted (general)
What I’m trying to do:
part 1:
I need a macro to copy the data only for columns Id# thru Qtr4 Totals (C5:H5 down thru the last row with data) to another sheet. The destination sheet range starts with rows B13:G13, with formulas below and to the right in columns H13, and J13:N13
(column I is blank).
The formulas below the range are a subtotal of the copied columns (D:G). They could be remove and totaled after they’re copied, so there's nothing below the copied range? Since I need the column totals as a subtotals, my thought was to have the row heading and formulas already on the destinations sheet and just insert a blank rows between as each row was copied?
part 2:
As each row of data is copied from the source sheet, I need the macro to copy the formulas to the right of the copied range (H13, and J13:N13) on the destination sheet down for each new row of data copied from the source sheet
View 10 Replies
View Related
Mar 16, 2013
Trying to assign code to comm. button on User form to copy lets say:
(sheet1, rangeB2:B21) to (sheet2, first blank row rangeB2:B21) and paste it as text value one more question: what to be aware in case of sheets format (merged cells, hidden rows...)
View 4 Replies
View Related
Oct 7, 2009
Need a code using application.inputbox to get a range, then use that range to copy and paste the range's link and format to a different sheet? The specifics don't matter, I just can't figure out the syntax. Here is what I have currently:
View 2 Replies
View Related
Jan 22, 2007
I want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
View 9 Replies
View Related
Jun 16, 2014
I have data in E6-E67 on Sheet 1. Based on the date in A2 on that sheet, I need to paste to a column in Sheet 2. In excel, I am able to get the cell location through vlookup and get the correct column number/cell reference. When it gets to the paste location, I am stumped on how to format that line of code? Do I need sometime of variable? I tried to use the address/lookup code but it does not work.
I have excel 2013.
View 4 Replies
View Related
Oct 2, 2008
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.
View 5 Replies
View Related
Feb 19, 2008
I have been struggling for a while to copy data from one worksheet to another and reset the target range.
The copy bit is cool, the range resetting bit is not. I have tried various methods, but none seem to work.
For example, the below code generates an error: "Compile error: Argument not optional"
I have stuck the particular command button script below to let you see what I am trying to do:
Private Sub cmbFilter_Click()
Dim sCriteria As String
On Error Resume Next
View 4 Replies
View Related
Mar 3, 2008
I have the following code that let's the user choose and " import" data to an existing sheet. It works well up until now. The problem is that the three ranges that I am trying to copy the data from on workbook to another has changed size. In previous version of my workbooks the range was two columns by 10 rows. Now, it is 1 column by 10 rows. So, when I run this macro it doesn't work because the two ranges are different. Is there any way to:
1) Only copy over one of the rows of a range thus making the macro run?
2) Do not run that part of the macro if there is an error?
Thanks so much for reading this long-winded description but the error is a big problem
Private Sub CommandButton1_Click()
Import_Data_Form.Hide
Run "UnProtectAll"
Set b = Selection
ad = b.Address
' Local Variables
Dim wkbDataFile As Workbook
View 4 Replies
View Related
Feb 19, 2010
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
View 6 Replies
View Related
Jul 6, 2007
I have below code (option 1) I was hoping would do what I need except instead of copying formula from A1 it copies values... (option 2) copies formula but does not preserve references...any ideas how to copy exact formula so that references are not changed?
Assumptions:
A1 formula = SUM(B2:E2)
in A5 I would like to copy exact formula to keep references to row 2...
I agree I could use $ in original formula but that would complicate other requirements.
OPTION1:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
Range("A5") = vRange1
End Sub
OPTION2:________________
Sub test_var_object()
Dim vRange1 As Range
Set vRange1 = Range("A1")
vRange1.Copy
Range("A5").PasteSpecial
End Sub
View 9 Replies
View Related
Aug 8, 2006
A text file is imported to Excel Worksheet. I use this code to Select/Copy a section of that import;
Selection. Offset(4, 0).Resize(15, 9).Select
Selection.Copy
'The number 15 would mean there are 15 rows of items.
But there are not always 15 rows. There might be 18 rows, but because the code is set to 15 it will miss selecting 3 rows preceding, or if the code is set to 12 it will not select the full item list. However, whatever the amount of rows of items, be it 15,2 18 or 100 for example, directly under the item amounts is a "marker" purposely put in the file before the import for other search functions.
Above that marker is the total Item count! So I need a code to search for that marker, then move up 1 cell and whatever that number is, if "15" would then determine the correct row count to Resize
The "marker" is : ^3^
So the full code is:
Macro1 ()........................
View 8 Replies
View Related
