Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Macro to Add Row and Copy/Paste Merged Cells

Hello,

I need some coding guidance for VBA that will does the following:

1. User clicks on a cmdBtn to add a row / cmdBtn to delete a row to/from a
worksheet;
2. The code unprotects the ws, inserts the row and copies the formatting of
the cell; then protects the ws after each iteration.

The problem that I'm having is that some of the cells in the 'copy from' row
are merged and they do not retain the 'merged' properties once 'pasted to'
the new row.

Q: How do I force the new row to retain the 'merge' properties from the
original row?

Here is my code, and thank you for any assistance you can provide me for
this project:

Private Sub cmdAddRow_Click()

On Error GoTo Err_cmdAddRow_Click

Dim rowcount As Integer
'check the cell position
rowcount = Range("U1").FormulaR1C1
If Selection.Offset(-rowcount, 0).FormulaR1C1 = "Invoice Date" Then
Selection.Offset(1, 0).Select
Else
MsgBox "Please click on the last white line under the Invoice Date
column."
Exit Sub
End If
'insert a new row
Selection.EntireRow.Insert
Selection.Offset(0, 12).Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-2])"
Selection.Offset(0, -12).Select
'initialize counter for deleting row
Dim counter As Integer
counter = Range("U1").FormulaR1C1
Range("U1").FormulaR1C1 = counter + 1
'protect the ws
Call ProtectSheets

Exit_cmdAddRow_Click:
Exit Sub

Err_cmdAddRow_Click:
If Err = 1004 Then
'don't display the cancelled action message
MsgBox "Please click on the last white line under the Invoice Date
column."
Else
MsgBox "#" & Err & " " & Error$
End If
Resume Exit_cmdAddRow_Click

End Sub

Private Sub cmdDeleteRow_Click()

On Error GoTo Err_cmdDeleteRow_Click

'check the cell position
ActiveCell.Select
ra = ActiveCell.Row
RC = ActiveCell.Column
wc = Cells.Find("Invoice Date").Row
ws = Cells.Find("Service Start Date").Row

If ra > wc _
And ra < ws _
And RC = 3 Then
If Selection.FormulaR1C1 = "Invoice Date" Then
Exit Sub
Else
'clear cell contents
If Selection.Offset(-1, 0).FormulaR1C1 = "Invoice Date" Then
Range(Selection, Selection.Offset(0, 12)).Select
Selection.ClearContents
Exit Sub
Else
'delete the row
Selection.EntireRow.Delete
Dim counter As Integer
counter = Range("U1").FormulaR1C1
Range("U1").FormulaR1C1 = counter - 1
End If
End If
'protect the ws
Call ProtectSheets
Else
MsgBox "Please use appropriate Delete button for the budget category you
are working with on the form."
End If

Exit_cmdDeleteRow_Click:
Exit Sub

Err_cmdDeleteRow_Click:
If Err = 1004 Then
'don't display the cancelled action message
MsgBox "Please use appropriate Delete button for the budget category you
are working with on the form."
Else
MsgBox "#" & Err & " " & Error$
End If
Resume Exit_cmdDeleteRow_Click

End Sub


Post your answer or comment

comments powered by Disqus
I have a worksheet with 1 column of random sequences and maybe 800 rows.
can you help with a macro to add row above and ">" sequential #.
Example:

actga
gttcg
cccttt

would like it to display:

>1
actga
>2
gttcg
>3
cccttt

I am a beginner. I was able to add rows, and >1, but did not know how to get it to # sequentially. Please help!!!

I have a loan amrtization file that I use to evaluate loans. I have it set
up to allow me to change the number of periods and the payment automatically
recalculates and flows through to the amortization schedule. The problem is
if you go from 20 periods to 30 periods you have to copy the formulas in the
amortization schedule down 10 more rows to calculate the rest of the periods.

Is there a macro that would automatically add rows and copy the formulas for
the schedule down the necessary number of rows? I have an input section and
cell A5 is where I input the number of periods for the loan. Currently, the
20th period of a loan is on Row 28 and the columns used in the loan
calculation go from A-G.

Is there any way to automate the file to automatically update?

Thanks
Dave

Macro to add rows

Thanks for your help.  I'm looking to copy certain rows and maintain the formatting in excel.  I would like to have a user click on a cmd button and a box come up and ask for how many rows do you want to copy. Then, copy the entire rows from say A10, multiply from the amount the user wants to copy and paste then rows to say A20 and placing the amount the user asked for.  I have code, but its not working correctly.  I have data that will be on top and bottom of the place i want to insert these rows.  The code I have is placing the data at the bottom.  I'm not wanting to do that.  I want to copy and paste to specific rows.  Thanks

Dim Txt As String, NR As Long
On Error Resume Next
Txt = Application.InputBox(prompt:="Please Enter Rows ", Title:="Rows to Copy", Type:=2)
If Txt = "" Or Txt = "False" Then Exit Sub
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A29:DH29").Copy Range("A" & NR).Resize(Txt)

All.

I want to write a macro to insert a row at the cell pointer.
Check that the first cell is in column A
and then copy a row of formulae into the new blank row.

I have hacked together the following. It quite happily inserts the row and
moves to column A but it only copies the formula row onto itself.
I think that I need to record the active cell (Column A of the inserted row)
and then paste the Formula row into it, but I've no idea where to start.

No need to give me the code, just point me in the right direction and I'll
try to work out the rest myself. This is a learning opportunity

Sub InsertRowAndPasteFormulae()
'
' Macro to Insert Row and Copy Formulae
' Macro prepared on 02/02/2006 by Philip.Smith
'
Selection.End(xlToLeft).Select
Selection.EntireRow.Insert
ActiveCell.Activate
Range("PP_Acc_Formulae").Copy
Range("PP_Acc_Formulae").PasteSpecial xlPasteFormulas

End Sub

Kind regards

Phil Smith

Hi All,

I have created a Macro to for below.

A)I have Codes on Sheet 2
B) Source data on Sheet1
C) Set filter in Sheet1, filter the data in sheet1 based on the codes on sheet2, copy data and paste it in Sheet3

However, am facing following problems.

A) Header row is not copied and pasted
B)Initially it has to filter the data for the first code in Sheet2 and paste it in Sheet3 and later it has to set filter for second code and copy past so on.

Could you please check the code and make changes.

Option
Explicit

Sub CopyRowsThatHaveTheRightCode()
Dim iSourceRow As Long
Dim iDestinationRow As Long
Dim iCode As Long
Dim varCodes As Variant
Dim booCopyThisRow As Boolean

' Get the pass codes
varCodes = Worksheets("Sheet2").Range("A2").Resize(2, 1)
'Loop through all rows in source sheet

iDestinationRow = 0
For iSourceRow = 1 To 500 ' or however many rows you have
booCopyThisRow = False

For iCode = LBound(varCodes, 1) To UBound(varCodes, 1)
If varCodes(iCode, 1) _
= Worksheets("Sheet1").Range("s:s").Cells(iSourceRow, 1) Then ' Code matches.
booCopyThisRow = True
Exit For
End If
Next iCode

If booCopyThisRow = True Then
' Copy into next available destination row
iDestinationRow = iDestinationRow + 1
Worksheets("Sheet1").Range("A:Z").Cells(iSourceRow, 1).Resize(1, 28).Copy _
Destination:=Worksheets("Sheet3").Range("a1").Cells(iDestinationRow, 1)
End If
Next iSourceRow

End Sub


Hi All,
Thanks for reading my Thread,I'm new here and need some help on excel,
I get a Data on a daily basis at work, at present i sort them out manually and paste them on to sheet1 - sheet2 -sheet3 and so on, i have large number of data Appx: 12000 row, I want to set up a macro or formula,that excel can sort the data match it and copy, paste it on to other sheet in same workbook. ie,

A B C D
12 Expense reports France New =====> Sheet Name France
25 AP Documents France New =====> Sheet Name France
45 Invoice Italy Workflow ======> Sheet Name italy
55 PO Invoice Ivory Coast Workflow ======> Sheet Name ivory coast

I would really appraciate if someone could help me about this

I also attached sample file ,which you can work on

Many thanks for your kind help and look forward to receive your support on this

Benjamin

Good day everyone,

Please can I get some help with the request below:

I have a spreadsheet with about 120 rows of data ( Currently ). Rows 1:5 are headings.

from Row 6 - Some of the columns are for the user to enter data and others are formula.

The user can enter data in all the unlocked cells ( certain columns - effectively adding records ).

I would like a macro that - after the user has entered new data - unlocks the sheet, copies all formatting ( including conditional formatting) from row 6 down to the last row, and then copies all formulae ( cells that contain formulae only) from row 6 down to the last row. sorts the data from row 6 to the last row ( sort on column C - Ascending) , after the sort - select rows 7 to last row and copy-paste special-values, and then locks the sheet again.

The Last row is based on there being a value in Column A ( from row 6 )

I hope this explanation makes sense

Thanks, I would appreciate any help on this.

JVN

( I can mail a sample spreadsheet if required )

Hi,
I have a spreadsheet that has a list of invoices on one sheet and on another sheet is a breakdown of the items that make up those invoices.I am trying to create a macro that looks up a invoice number in each row and copy and paste each line that the makes up the invoice in another sheet.
Can someone tell me if this is possible?

Hi All,

I need some assisstant with a problem it have at work.
I've attached the worksheet here for easy understanding.

I have this worksheet called PO and this form is actually standard form which not allow us to add in column nor row. The big problem on this form is only allow us to enter 2 grades in 1 form.

My question is:-
1. Use macro to "Add Grade". When I click on "Add Grade", Row 20 : Row 26 will be copy & paste after row 26.
2. When print out. The form must be exactly look like the PDF file I attached. Which is 2 grade in 1 sheet.

Said if I have 3 grade now. G1, G2 and G3. I click "Add Grade" my current PO will allow me to enter 4 grade. After key-in G1, G2 and G3. The print out must be G1 & G2 in one paper & G3 in the other paper.

Hope someone can help me to solve this problem.

Thanks in advance.

Warm Regards.

I have 6 rows (from R4-R9) that include merged cells that I need to copy
in the sheet over and over in a loop. When I manually copy, it works
fine. My problem is that although the Macro below copies the 6 rows,
none of the merged cells are maintained! All the cells are individual. I
thought PasteFormats maintains the merged cell layout.

>>>>
' Copy 6 rows
Rows("4:9").Select
Range("C4:C9").Activate
Selection.Copy
Rows(NewRow & ":" & NewRow + 5).Select
Range("C" & NewRow & ":C" & NewRow + 5).Activate
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
<<<<

Any tips on maintaining the Merged Cell formatting in VB?

Thanks,
Pete

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I have a list of names in column A9:A200

what i need is every other row to add 7 rows and copy data from B2:B8 into them

i have this so far but dont know how to do it for all the rows

Sub Macro1()
Range("B10").Select
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Selection.EntireRow.Insert
Range("B1:B8").Select
Selection.Copy
Range("B9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Thanks for any help

I am very new to VBA and I am trying to create a macro to do the following:

1. Open the "D:Comp Admin FilesQPR ReportsQPR ReportsFY08FY08 Monthly Master QPR (Dec).xlsb" file.
2. Copy the first tab onto another worksheet.
3. On the copied worksheet, search in the column headers, row 3, and determine if they are bold and italic.
4. If they are bold and italic, then select those rows and copy paste special values.
5. The columns go from A to EA; 131 columns.
6. Save the copied worksheet as the tab name and the current month.
7. Repeat steps 1 through 6 until the first five tabs on the FY08 Monthly Master QPR (Dec).xlsb file have been copied onto new worksheets.

I have been trying trying to write this macro, but I continue to get errors and I think I'm missing some important steps. Thanks in advance for your help!!

Hi All,
Thanks for reading my Thread,I'm new here and need some help on excel,
I get a Data on a daily basis at work, at present i sort them out manually and paste them on to sheet1 - sheet2 -sheet3 and so on, i have large number of data Appx: 12000 row, I want to set up a macro or formula,that excel can sort the data match it and copy, paste it on to other sheet in same workbook. ie,

A B C D
12 Expense France New --------> Sheet Name France
25 Expense France New ---------> Sheet Name France
45 PO Invoice Italy Workflow -------> Sheet Name Italy
55 PO Invoice Italy Workflow -------> Sheet Name Italy

I would really appraciate if someone could help me about this

I also attached sample file ,which you can work on

Many thanks for your kind help and look forward to receive your support on this

Benjamin

Hi. I have a worksheet with around 25 rows that will already have a formula in them that adds today's increase or decrease to the previous day's balance.

Column A is for daily entries (values)
Column B is the running total(formulas)

For example,
B1 would be the beginning balance for the month (500, a value).

A2 would be the first day's amount: 250.

B2 would be =B1+A2 (500+250=750)

A3 would be following day's amount: -100

B3 would be =B2+A3 (750-100=650)

A4 would be the following day's amount: 50

B4 would be =B3+A4 (650+50=700)

When a cell is double-clicked, the code below inserts a new row and copies the formula down to the new row. If I insert a row at Row 2, the inserted row becomes row 3 and the formula is fine. BUT the formula in row 4 (which was row 3 before the insert) does not adjust the first cell reference in the formula the way it is supposed to. Example:

Row 2 =B1+A2

Inserted row, Row 3 =B2+A3

But the row that moved down(formerly row 3) Row 4, =B2+A4 instead ofB3+A4

Is there any way to make this work properly? Thanks!


	VB:
	
) 
     'David McRitchie,  2007-09-07    insrtrow.htm on double-click
     '-- will copy more often than  Extend Formulas and Format (tools option)
    Cancel = True 
    Target.EntireRow.Copy 
    Cells(Target.Row + 1, 1).EntireRow.Insert 
    Cells(Target.Row + 1, 1).EntireRow.Select 
    ActiveSheet.Paste 
    Application.CutCopyMode = False 
    On Error Resume Next 
     '-- customize range for what cells constants can be removed --
    Intersect(Selection, Range("b:IV")).SpecialCells(xlConstants).ClearContents 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi guys,
I am having trouble programming a macro that will retrieve entire rows of data from a reference workbook that contain a desired value in the H column. The trouble is, I would like the desired value to correspond approximately with a cell value on the first sheet in the following way.
Say for example the original sheet contains "John Smith" in cell A2. I would like the macro to search for and copy/paste into this sheet all rows of data from the reference workbook for which row H is titled "Smith"
If its too much trouble, I can use an exact match there.
I cant say I have any code to show you as every attempt I make at this seems to hit further from the mark. Thank you all so much in advance for your help.

Excel '07
I am trying to take data that is given in 2 cells on the same row and transpose the second cell which is comma delimited to multiple rows. I would also like to copy the adjacent cell (left in the sample below) to each new row. Preferably, this would be generated on a new sheet. I've been trying to find a Macro to do this. Any help would be greatly appreciated.

RECEIVED AS PROJECT1 ITEM1,ITEM2,ITEM3,ITEM4,ITEM5 PROJECT2 ITEM6,ITEM7,ITEM8,ITEM,ITEM10 CHANGE TO PROJECT1 ITEM1 PROJECT1 ITEM2 PROJECT1 ITEM3 PROJECT1 ITEM4 PROJECT1 ITEM5 PROJECT2 ITEM6 PROJECT2 ITEM7 PROJECT2 ITEM8 PROJECT2 ITEM9 PROJECT2 ITEM10

I have a spreadsheet with three rows of IDENTICALLY SIZED merged cells
consisting of 1 row and 2 columns, stacked in rows. The row height for all
three rows is 40. The column widths a D, 26.57 E, 5.14. The three cells
represent the top three issues on a summary sheets and so the contents
frequently move up and down. I cannot copy and paste. Whenever I try, it says
"This operation requires the merged cells to be identically sized". But they
are identically sized! What else could it be referring to other than the row
height and column widths? Is there some other property Excel wants me to make
equal that it calls "size"?

I am new to Excel VBA, and to this forum, and I have a query that is driving me mad.

I have one column of names in excel. The column may contain more than one row with the same name but these rows with the same name will all be grouped together. This is an exampe (each name represents a row in column A):

ColumnA ColumnB
andrew Data
julie Data
julie Data
julie Data
jonathan Data
jonathan Data

What I want to do is copy the rows with the same information, e,g, the rows with 'julie' above, paste them into a new spreadsheet and email this spreadsheet to specific email addresses and then do the same for 'jonathan'.

I can work out how to send an email using VBA but I am really stuck as to how to go through the rows and send the email in discrete 'chunks'. I have tried using a for next loop, looping through the rows and copying/pasting rows that are the same as the previous one into a new spreadsheet but this does it one row at a time.
If I include the instruction to email the spreadsheet within the loop this would also email the new spreadsheet one row of information at a time, i.e. three emails for 'julie' each containing a spreadsheet with one row of information on it, rather than one email containing one spreadsheet with all three rows on it.

I hope I have clearly explained what I am trying to do. I realise this has probably been asked countless times before (and I apologise if it has) but I have Googled this topic countless times in the last couple of weeks but, because I don't know that much about VBA, I haven't been able to find any help on my own.

I suspect using a for next loop is not the way to do this but I can't think of any other way. I would be really grateful for any advice anyone could give.

My code to copy and paste the data into the new spreadsheet is:

for i = 1 to numrows

if worksheets("Sheet1").cells(i,1) = worksheets("Sheet1").cells(i+1,1) then
worksheets("Sheet1").cells(i,1).copy

Destination:=worksheets("Sheet2").Range("A1")
Instructions to create new workbook and email go here

end if

next i

Hi guys,

I am having trouble programming a macro that will retrieve entire rows of data from a reference workbook that contain a desired value in the H column. The trouble is, I would like the desired value to correspond approximately with a cell value on the first sheet in the following way.

Say for example the original sheet contains "John Smith" in cell A2. I would like the macro to search for and copy/paste into this sheet all rows of data from the reference workbook for which row H is titled "Smith"

If its too much trouble, I can use an exact match there.

I cant say I have any code to show you as every attempt I make at this seems to hit further from the mark. Thank you all so much in advance for your help.

Hi guys,

I have a set of data that is around a 10000 rows long and it comprises of planned and actual data for production of apples by each base in different countries.
Now I would like to find the differences between the actual and the planned values for all the months I have data for.
The only way I can think of is to insert a new row and introduce formula in the new row to calculate the differences between the actual and planned values.
The problem is that I have 10000 lines of data and obviously I cannot introduce a new row and copy and paste the formula for each of them without going insane!
Can anyone help me on this.

Thanks.

Hi, I'm fairly new to Excel and am new to this forum. It's nice to have experts to help with problems. I have a problem in some code. I am trying to develop a macro that inserts rows and copies formulas into that row from the rows above it, then changes the formulas in tthe old rows that have been moved down ( I hope that is easy to follow).

I ask the user to choose the row below where they want to insert, then I ask them how many rows they would like to insert. Everything is fine until the point where the old rows formulas are updated with cell references.

Anyone have this code or a place to get it that I could compare what I am doing wrong??

Thanks so much for your help

I need to create rows of data based on days of the week. I start with a
calendar date and delete rows for days of the week where there are no
records. I need to now insert rows based on the remaining days of the week.

Specifically, on Wednesdays, I need to insert 1 row and copy cells from the
row above. On Thursdays and Fridays, I need to insert 3 rows and copy the
respective cells from each day. On Saturdays, I need to insert 4 rows and
copy the cells for Saturday.

This needs to go through the entire data set for one month or two months.
Any ideas?

I need to create rows of data based on days of the week. I start with a
calendar date and delete rows for days of the week where there are no
records. I need to now insert rows based on the remaining days of the week.

Specifically, on Wednesdays, I need to insert 1 row and copy cells from the
row above. On Thursdays and Fridays, I need to insert 3 rows and copy the
respective cells from each day. On Saturdays, I need to insert 4 rows and
copy the cells for Saturday.

This needs to go through the entire data set for one month or two months.
Any ideas?

Hi,

I need to create a macro that will hide rows and columns depending on values in different ranges and worksheets.

I have got some code that works in Excel 2000 (Sub hide_empty_rows_and_columns_ALL), but it seems to loop when I tryto use it in Excel 2003. I have found an alternative piece of code (Sub TEST_MACRO_3) which seems to work but I can't work out how to make this applicable to columns as well as rows. Can anyone help?
 Sub hide_empty_rows_and_columns_ALL()
'To hide rows and columns with no data

Application.ScreenUpdating = False

With Sheets("Data6")
Dim rowA As Range
For Each rowA In .Range("E5:E2353").Rows
rowA.EntireRow.Hidden = _
Application.Sum(rowA) = 0

Next
End With

With Sheets("Data3")
Dim colB As Range
For Each colB In .Range("E6:GV6").Columns
colB.EntireColumn.Hidden = _
Application.Sum(colB) < 2

Next
End With

Application.ScreenUpdating = True
End Sub


This is the alternative code that I found, but I can't figure out how to make it apply to columns as well, or how to include numerous different worksheets and ranges in one macro:

Sub TEST_MACRO_3()
Dim Rng As Range, Ary, i As Long, c As Long

With Application
.EnableEvents = False
.ScreenUpdating = False
c = .Calculation
.Calculation = xlManual

With Sheets("Data6")
.[E5:E2353].EntireRow.Hidden = False
Ary = .[E5:E2353].Value
For i = LBound(Ary) To UBound(Ary)
If Ary(i, 1) = 0 Then .Cells(i + 4, 5).EntireRow.Hidden = True
Next
Erase Ary
End With

.ScreenUpdating = True
.EnableEvents = True
.Calculation = c
End With

End Sub



No luck finding an answer? You could always try Google.