Free Microsoft Excel 2013 Quick Reference

Macro to copy a row, insert row below & paste into new row

My requirement is for a macro where:
user selects a cell in column c
runs macro (command button)
macro checks that value in column c starts with "7"
macro then copies the selected row
inserts new row below the one that was selected
pastes the copied row into the new row (all formulas, formats included)

Code preferably to be backwards compatible to excel 2003 and MAC friendly.

Thanks


Hello,

I am trying to record a macro to copy selected rows on a sheet and paste the selected rows into a new sheet. The number of row changes based on using an auto filter.

Thank you.

IHAP

Hello everybody,

I have a macro to copy a range of cells (A2:AB100) from sheet1 and
paste it to another file as values.

Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A2:AB100" )

I would like to insert one more code so as it should copy only todays
data;

If B2 = todays date then the range of cells to copy should be (A2:AB2)
If B3 = todays date then the range of cells to copy should be (A3:AB3)
If both B2 and B3 = todays date then the range of cells to copy should
be (A2:AB3)
If all the cells from B2 to B100 = todays date then the range of cells
to copy should be (A2:AB100)

Any help is greatly appreciated.

Thanks & Regards
Dileep Chandran

Hello...I have a master workbook (Source.xls) with 8 sheets of data, covering a two year period.
Each sheet (tab) contains 1640 rows of data for one quarter of a year.
Each row in each sheet represents one physician's data for that quarter.
Each column in each sheet represents the different criteria or measures for that physician's file.

No physician will have data for all columns.
A sample (Source.xls) of the original master workbook is attached with only 15 of the original 1640 rows shown per sheet. A sample of a physician workbook
(Target.xls) is also attached.

The second workbook (Target.xls) has only one sheet, representing one physician.
Each row in Target.xls represents one quarter of the year.
Each column represents the same criteria as found in Source.xls for that one physician.

We need to be able to look at each physician's file twice a year, hence the decision to create Target.xls for each physician.
I need a macro to copy each row in each sheet in Source.xls (each row represents one physician), create a new workbook with the mnemonic of the
physician (found in B3 of Source.xls) and paste the data in the row that matches the correct quarter in Target.xls.

Depending on the physician, there may only be one quarter's worth of data in Source.xls (and Target.xls), but I still need to see that there are
blank rows (quarters) in Target.xls for that physician.

Any help would be most appreciated.

i'm trying to write a macro to copy a row from sheet 1 to sheet 2, but the row can be varied by the user. in other words they can select row 15 or row 35 or any other. i can do the copy and paste bit but cant figure out how to get the macro to copy only the selected row. can anyone help please

I have created a worksheet to track project status each month. (copy attached) Currently I have drop down lists to select the appropriate status symbol. There are four options. Problem is that my 'Key' now contains symbols and letters. Can I create a macro to copy from the key and paste into the selected cell each month? (instead of using a drop down list)

Best Regards,
Joanne

Hello.

I'm new to VBA and I'm trying to create what I think should be a simple bit of code to automate a cut and multi-paste process.

I have worksheet from which I want to copy a row and then paste that row into another worksheet n times, where n is the value in a cell in the copied row.

A slight complication is that I want to do this twice, one pasting n0 times and then appending a "0" to the newly pasted rows, and one pasting n1 times and thne appending a "1". (n0 and n1 are cells in the original row).

I've attached a cutdown version of the spreadsheet I'm working on. I seem to be stuck on getting the command rows().select to accept a variable.

Many thanks if anyone can help.

Rob

My data is from A35 to GB160. Formulas in this worksheet are protected. I
would like to do a macro to copy a row to the last row of this range that is
blank (row 35: row 160).
Example:
If I shaded 1 entire row (example row 40), I would like to copy the whole
row and paste special value and formula to the last row (from 35:160) that
have no data yet (example in this case: blank row is row 68) . Remember that
the workbook is protected, so that in a row there is a chunk of values,
following by formulas, then values, than formulas...and so on.
Thank you

Hello all,

I am trying to set up a simple invoice system. The way that I want to work it is, that if you hit a check box, this will run a macro to copy that row to another sheet (invoice template), as then you can go down the list and tick all items that need invoicing? I think that I need to use the offset funtion, but don't really know how to get the macro to identify that it is the row that each indiviual check box is in it needs?

Alex

PS Thanks in advance

Hey again,

I was wondering if there is a way to use a macro to copy a merged cell without the blank spaces at the end? I'm attaching an example worksheet and I need to have the user copy the value of cell H3 so they can paste it in a program without the spaces at the end caused by the merged cells.

Thanks everyone!

Hi, I have 63 separate files which are sent to our stores each month
and returned, and are identical in format. I have password protected
them also so our sites cannot alter the layout.

Each site name is entered in cell B17 and I have created a comments
section from rows 41 to 47 and wish to copy these rows from each file
and condolidate into 1 sheet (create a summary of all comments)

My skills writing macros are fairly basic and would value some
suggestions on how I can create a report which will extract the site ID
(B17) followed by rows 41 to 47 from each file.

Thanks,
Dean

Hi All,

I would like to copy a row onto a second worksheet if in column B:B there is the text "stage 5" However each row is made from 3 cells merged together.

Thanks in advance for any help

Anuj

Hi Everyone,

I would like to have a macro to delete a row of a worksheet but with a password confirmation via input box. Is it possible?

Thanks again in advance.

RNF

Hi all,

I am currently working on this project and have been stumped. It basically includes three layer.

1 is the surface layer.

2 is the intermediary.

3 is the input layer.

I am trying to make 2 pull data from 3 that will be used by 1. 1 already pulls data from 2 to generate its data. This will be used to compare two models down the road and to eventually phase out 3.

I am stumped because I am trying to copy a row of data from 3 and paste it into 2 based on a criteria in the row. However, I need it to update 2 and not replace it as 1 will always search into 2 for the information it requires.

Anyone have an idea on where to start on this?

Hello Guru's,

I would like a macro to add a row after each different account code and then subtotal costs. See before and after.

The trick is the amount of rows per account code will change and so will the account codes month to month. Anothe issue is there are 4 and 6 digit acount codes.

Note: subtotal line on after file, row 33

Thanks in advance.

BG
Boston

Hello!
How to copy a row with transposing to a column retaining links to input
cells? Excel transposes with a links change or only values.
Best regards,
Dima
+7 9163876746

Hi All
Can any one tell me how to write a macro to delete a row

I receive peoples' Sports Picks via email, from a 3rd party form processor.

I then copy their picks, go to Excel and execute this Macro: (Just recorded
with the Macro Recorder)

Application.Goto Reference:="R68C72"
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Selection.TextToColumns Destination:=Range("BT68"),
DataType:=xlFixedWidth _
, FieldInfo:=Array(Array(0, 1), Array(6, 1)),
TrailingMinusNumbers:=True
Range("BU68:BU77").Select
Range("BU68:BU77").Activate
Selection.Copy

What I would dearly love it to do is:

* Look up the reference in BU77
* Find that in the range J67:BB67
* Paste into rows 68-77 below where it finds that reference.

If someone could also insert some code to automatically click "OK" when the
dialog asking "Do you want to replace the contents of the destination cells?"
into the "Text to Columns" code above, that would be like heaven!!

Thanks for your time and attention!

Eddie

I receive peoples' Sports Picks via email, from a 3rd party form processor.

I then copy their picks, go to Excel and execute this Macro: (Just recorded
with the Macro Recorder)

Application.Goto Reference:="R68C72"
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
Selection.TextToColumns Destination:=Range("BT68"),
DataType:=xlFixedWidth _
, FieldInfo:=Array(Array(0, 1), Array(6, 1)),
TrailingMinusNumbers:=True
Range("BU68:BU77").Select
Range("BU68:BU77").Activate
Selection.Copy

What I would dearly love it to do is:

* Look up the reference in BU77
* Find that in the range J67:BB67
* Paste into rows 68-77 below where it finds that reference.

If someone could also insert some code to automatically click "OK" when the
dialog asking "Do you want to replace the contents of the destination cells?"
into the "Text to Columns" code above, that would be like heaven!!

Thanks for your time and attention!

Eddie

Hello!

First of all, this really is an amazing resource!!

Now, to my query. Any help will be much appreciated. I have attached the file. The row being copied is row "X" for both buttons

I have 2 buttons in the same worksheet to copy a row and insert the
copied row below it. I have this macro running for 2 different rows in the worksheet, assigned to the respective 2 buttons. See my code below.

______________________________________________________

Private Sub
CommandButton1_Click()
    Range("$B$7:$L$7").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

Private Sub CommandButton2_Click()
    Range("$B$12:$L$12").Select
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub
________________________________________________________

The problem is:

1) I want just the formulas/formatting to be pasted, and not the
entered values.
2) And more importantly, if I use Button 1, then row 7 is copied and the copied row is added below row 7 and the rows move down. But then, for Button 2, row 12 is different....now as it has moved down (it has become 13, but the macro for button 2 is still copying row 12), if you understand? How do I keep it fixed that it always copies the contents of row 12 even if a row is added above and the rows move down, making row 12 into 13 and so on.

Thanks again,

Shivam

I am trying to create a macro to scan the data in Column H, when a "1" is
found, move one row up, copy that row and insert the copied row of data. I
have tried the following code, but it does not work and I cannot find an
answer in the searches I have done.

I would greatly appreciate help!

For Each Cell In Range("H:H")
If Cell.Value = "1" Then
Cell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Copy.Insert Shift:=x1Down
End If
Next Cell

Lost in Alabama

Hi,

I've got a spreadsheet at the moment where a lot of data has to be entered on a daily basis. To combat this I have created a main data tab(To clarify, this is seperate to the tab which the final data will be displayed on) and have created a range of formulas which run across the range (C:CC) The data is updated via a copy and paste method daily and is automatically pulled thorugh via said formulas. Some users aren't particularly Excel savvy and therefore I want to automate the process of copying the formulas down to the next row and pasting the original values into the cells which previously contained the data therefore retaining the values.

I'm looking to incorporate a macro and attach it to a button which will do the following:

Locate the last row in which figures were entered
Copy the formulas in these cells down (C*:CC*)to the next set of blank cells
Copy and paste special the original row to retain the values whilst also removing the formulas.

The formulas will then continue to function normally and will pull through the next days figures once entered into the main data tab.

I hope this is clear but if need be I could probably cut the spreadsheet down to size to provide an example.

Many Thanks in advance.

Mark

Hi all. I am new to macros, vba and this site. So that being said I am sure alot of you went running.

I have a workbook with 38 workbooks in it. It is comprised of values and formulas. Each month I am having to copy the previous rows links and formulas down. I would like a macro that accompolishes this for me in all the sheets. The row that will receive the paste currently has projected values in it. Every macro I have been able to find on this searchs for last row or inserts a row and deletes previous row. I just need a simple macro to copy the previous row down to the next one across the workbook. Some of the sheets do not have the exact nbr of columns. Here is the one I attempted to use Range("B72:AC72").Copy Destination:=Range("B73"). Note there is data in Column A, but it is just a date. This macro runs in the current worksheet, but then won't advance to the next line.

Thanks to all.

I looking for a macro to copy one row to a new sheet, but I need it to place it in the next available row in that sheet. For example, in the screen shot below, I need the range of b3 to o3 copied over to sheet 2, and placed in the next available row starting in column B. Hope that makes sence. Thanks for any help.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB1=
BCDEFGHIJKLMNO1CUSTOMER / STOCKJOB #LINE #MATERIALMATERIALMAT'L QTYPAPERPAPER QTYFinish QNTYSIZE RUN DATEDue DateSTATUS2 3CUSTOMER A2594101TET0043 17959 # 80234 / 24070217 17/32X21 31/32 .008105/156/5 4CUSTOMER B2602771TET1049 266 # 834 / 100088.5X11 .010105/195/20 5CUSTOMER C2602251TET1532 ROAM 9600 / 5760017.125X12 5/16 .010105/195/22 Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi,

Having searched the forums, and not finding exactly want I need, I wonder if anyone could advise. Apologies in advance for being a Visual Basic novice.

I would like to employ a macro to copy particular rows of data from each of 4 sheets to 4 consecutive rows on a fifth sheet.

Ideally, I would like to run the macro on the first sheet, possibly after highlighting a desired row, and for it to find and copy the same numbered rows from each of the 2nd, 3rd and 4th sheets, pasting them onto the fifth sheet (this may not the most efficient method). So, for example and picking an arbitrary number, row 31 on sheets 1, 2, 3 and 4 are copied/pasted to rows 1, 2, 3 and 4 on the 5th sheet.

I would like to be able to choose the row number (always the same) to be copied. I also need to repeat the process with other rows, copying/pasting over the previously pasted data (I've printed in the mean time).

Thanks.