Over 1 million tech questions and answers.

macro to copy rows based on cell value

Q: macro to copy rows based on cell value

Dear Fellows

I am looking for help for a macro which can copy data to other sheet based on adjacent cell value which i define.

For example i have data attached in a sheet.

i want to copy data based on value present in E column to different sheets. if it is ES then all ES rows should be copied to sheet ES with roll number name father per and dept.

Same is for other departments, on different sheets. There will be 9 departments overall.

Looking for response

Read other answers
Preferred Solution: macro to copy rows based on cell value

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)


Hi I am trying to copy and insert rows based on a number in cell E. If the number in cell E is 4, I would like to copy 3 additional rows beneath the original and then also number the 4 rows into column F consecutively for each group. I am attaching a spreadsheet of before and after. Sheet 1 is the before and sheet 2 is the after. Can anyone help me with this. I have tried another code but it not work properly. I did find one that would put blank rows in but I cannot get past that. Any input is greatly appreciated.

Thank you


A:insert rows based on number in cell and copy the data down into the new rows

Hi Peg

Try this solution and see if it is ok for you. I've put in some code to help prevent the running of the code multiple times on the same sheet. If you don't need this safeguard you can delete the block of code that does this.

Also, make sure and make a backup of your file before running this - just in case.

Read other 1 answers

Hi there guys

Looking for help in excel 2007 for the macro part.

Basically in sheet1(BOM), we have configurations which our customer order to us (in the form of CP1,CP2..CPn). In Sheet2 (CP) details about CP are mentioned, basically each CP has list of items under it. Typical order from customer includes combination i.e. CP1&CP2 or could be CP2&CP3. What I want to do is based on the order in BOM sheet, need to search the details of CP's in sheet2 (CP) & copy the rows belong to that particular CP in the BOM sheet. So that I can make the BOM list for that particular order. Each CP has variable number of rows. Appreciate your help in making macro for this.

Rgds, MintC

A:Excel Macro to copy multiple rows based on selection

Read other 11 answers

I am trying to hide rows in excel 2010 based on cell values in a certain column. I know that I have to use the VBA but I don't know how to do that kind of stuff. I know it should be simple to do but I don't know where to start. Let me know what you need in order to help me accomplish this task.

A:How to hide rows in excell based on cell value

Read other 8 answers

You must be bored with this question cos I've seen lots of them, however I can't seem to fathom out what to do..........

I've got a block of data within a spreadsheet 4 rows always in the same place.

How do I put into code:-

If column d43:e46 is blank delete rows 43 - 46 if not ignore it.

As I said I've tried a few things and it will delete the four rows regardless of them being empty of not.

Can someone help please.

Many thanks,


A:Deleting rows based on empty cell.

Is there anybody there? - Help!

Read other 2 answers

I have a sheet with several hundred rows. Starting in Row 4 through the end of the sheet are mixed dates in column 1 and ALL rows from Row 4 through the end of the sheet hidden. I want to be able to type in a date in Cell A1 and have all rows that have that date in Column 1, Row 4 through the end of the sheet to become unhidden. Secondly, I would like to type some other code like "999" or something in Cell A1 to have ALL rows become unhidden. Thirdly, I would like to either type another code or just delete anything in Cell A1 and have ALL rows from Row 4 through the end of the sheet to become hidden again.
Tech Support Guy System Info Utility version
OS Version: Microsoft Windows 7 Professional, Service Pack 1, 64 bit
Processor: Intel(R) Core(TM)2 Duo CPU T6600 @ 2.20GHz, Intel64 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 3963 Mb
Graphics Card: Mobile Intel(R) 4 Series Express Chipset Family, 1853 Mb
Hard Drives: C: 453 GB (298 GB Free); F: 931 GB (710 GB Free);
Motherboard: TOSHIBA, Portable PC
Antivirus: Webroot SecureAnywhere, Enabled and Updated

Read other answers

Hi all, long time reader, first time poster. I searched around and have found similar queries to what I require however I haven't been able to adapt these solutions with any success so here we go....

I have a list of businesses in a spreadsheet with information such as name, phone number, e-mail, website, postcode etc. I would like a search box on the side, say in cell O2 where a user can input part or all of a postcode e.g. (LS19 or LS) and those that do not begin with LS would be hidden. The postcodes are in column G.

I'm trying to make this as simple and user-friendly as possible so employees can find businesses in a certain area with ease. Thanks in advance for any help and please feel free to ask questions.

Chris Needham

A:Excel VBA : Hide rows based on cell input.

Seems nobody had the answer so I found a work around myself. Just in case anybody else wants to do a similar thing. Using auto filter is the easiest way.

[SIZE="3"]Sub Search()
ActiveSheet.Range("$G$1:$G$999").AutoFilter Field:=1, Criteria1:="*" & Range("O2").text & "*", _
End Sub[/SIZE]

Where G is the column to search for and O2 is the users input data.

Guess this can be closed now, thankyou all for your help.

Read other 1 answers

Hello to evrebody,
I have look into the answers and the solutions about "hide/unhide rows based on the cell data" but i am not abble to understand how its work and apply to my sheet.
On theattached sheet i explain what im looking for.
Pleaseo play it and hope to help me and give a solution please.
Thanks in advance.

Read other answers


I cant seem work out a solution for what I'm trying to do. I have an Excel workbook that has multiple sheets. On sheet 1 i want the data from cell "G3" to be copied onto sheet 2. But i want the location on sheet 2 to be based on whatever was entered into cell "D3" on sheet 1.

For example: Sheet 1, cell D3 I have the name John, in cell G3 i have 68. I want "68" to be pasted in sheet 2 in cell B26.

But if the name in Sheet 1 cell D3 is Suzie, then I want G3 to be pasted in Sheet 2 in cell D26. So I would need to identify the paste location for each person.

I want the data to paste to the next cell so that the next entry can be pasted below the last entry for that person (for John the first entry would go into cell B26, then the next entry would go into cell B27 and so on).

But i want it to be a specific range, i dont want data to be pasted past 20 cells (cell B45). If possible a message box could be created to let the user know that the max is reached.

I would appreciate anyone's help with this as i have been struggling for awhile to try to get this. Thank you

A:Excel - Copy paste cell into range based on another cell

Read other 12 answers

Good Day,

I would like to copy the rows in a worksheet to another worksheet where a value in the row say H2 <900. I would then like to delete those cells from the former worksheet. Can you assist with this. Thanks

A:Solved: Macro to delete rows where cell meet a condition

Read other 7 answers

I selected a cell, and then recorded a macro when I copied the cell, moved down to the cell below it and pasted it. But when I select another cell, and then run the macro, it goes to the initial cells I did when I recorded the macro.

So is there a macro where I have selected a cell, whatever cell I decide, and then run the macro, and it copies the cell selected and paste it to the cell below the cell that was copied?

A:Solved: Macro to Copy and Paste From One Cell to Cell Below

Read other 16 answers

I have this spreadsheet where I am trying to get a macro to compare column b with a and if they match, to move column b under column a to reorganize a gigantic spreadsheet. Any ideas?

In the example below, the macro would match "eggs" in column b and move those rows under column a which has eggs too... i need the macro to do this for potential "juices" and "sodas" etc. As well as any other things that may come up. Thnx in advance.

A. B.
eggs bread
milk juice
soda eggs
Juice eggs

Read other answers

I have the following data that i need to create a set of rules or code that if multiple criteria is met to delete the row. I have included some examples of what i need. i tried doing a Recording macro but didnt work since sometimes some of the criteria is not there at the time of running the macro and it fails.

I need to have code look for specific values, i.e.

Account paid Y
Date of Payment Blank
Highlight all cells
'this is to review information
after this then check if it has Code 3 = C1 and Code 4=D1
Highlight all cells
'this is to review information

Check if Code 1=B2
Check if Paycode is blank or other
Highlight all cells
'this is to review information
Then delete other rows

AccountBalanceDateCode 1Code 2Due dateCode 3Code 4Code 5Account paidPaycodeJonnathan5009/1/2011A1B1C1D1E1YCollectedJessica252.359/2/2011A2B2C2D2E2YCollectedMaria321.549/3/2011A3B3C3D3E3NPayPlanLeon652.339/4/2011A4B4C4D4E4NPayPlanDavis525.329/5/2011A5B5C5D5E5YCollectedCarmen277.679/6/2011A6B6C6D6E6NOtherKarina346.869/7/2011A7B7C7D7E7NJose 677.659/8/2011A1B1C1D1E1YCollectedMary550.649/9/2011A2B2C2D2E2YPayPlanMiguel302.999/10/2011A3B3C3D3E3NPayPlanJessica372.189/11/2011A4B4C4D4E4NCollectedElaine702.979/12/2011A5B5C5D5E5YOtherPatricia575.969/13/2011A6B6C6D6E6NCollectedKaren328.319/14/2011A7B7C7D7E7NCollecteddavid397.59/15/2011A1B1C1D1E1YPayPlanJoe728.299/16/2011A2B2C2D2E2YPayPlanJoseph601.289/17/2011A3B3C3D3E3NCollectedwilliam353.639/18/2011A4B4C4D4E4Nbill422.829/19/2011... Read more

A:MS Excel 2007 - VBA or Macro to delete rows based on multiple criteria.

Read other 8 answers

Hi there! I'm new to using these forums, but have spent some time scouring them from time to time to look for simple solutions to any excel/VBA problems I'm having. I consider myself relatively intermediate to advanced in Excel as far as formulas and spreadsheet creation goes, however VBA is foreign to me, and an area I'd love to learn. If any of you have recommendations on a good resource to begin learning VBA, please send it my way. And now....onto my macro question.

I've got a workbook with many tabs, and one or two input tabs feed information into another summary tab using various lookup and index formulas. There are any where from 40 to 80 rows to be qualified depending on the specific summary sheet (therefore I will likely need the macro to reference only the active sheet as it needs to work the same way on different sheets independent of each other). What I would like to do is if the result of the formula in column A of the summary sheet is "----------" (starts at A7) then I would like to hide that row, and if the data is changed on the input tab to result in anything other than the qualifying "----------", for that row to be unhidden. If this can be done dynamically that'd be great, however, if a button needs to be put into the spreadsheet to execute the macro, that will be fine too.

Thanks so much in advance for your help, and I promise I'll do my best to begin learning VBA so I can help contribute in return!

A:Macro to Hide/Unhide Rows Based on Results in Column Equation


Here is a code that may help you. You can copy and paste it too a module.
When you copy it to a module you can assign a "Short" cut key to activate the code.
To do this you select "Tool", "Macro" and when you see the name of the macro you want to run you can select it and then select "Options" and assign the short cut key. You will be able to use the short cut key on any active sheet.

Sub Hide_Row()
Application.ScreenUpdating = False
Dim Mysh As Worksheet
Dim cel As Range
Set Mysh = ActiveSheet
For Each cel In Mysh.Range("A1:A" & Mysh.Range("A65536").End(xlUp).Row)
If Not (Mysh.Cells(cel.Row, 1).Value Like "--*") Then
Mysh.Cells(cel.Row, 1).EntireRow.Hidden = False
Mysh.Cells(cel.Row, 1).EntireRow.Hidden = True
End If
End Sub

Read other 2 answers

I have a list of 150 stores of which 20 are designated "gold" which means they get better service for deliveries. In the spreadsheet that contains these stores they are already configured to have the name of the store blocked in yellow (gold). I have to manually retype these onto another sheet, is there a way of transferring this info using the colour as the argument.

Many thanks.

A:copy rows into another sheet based on colour

Read other 10 answers

Hi All- a newbie,

I've been trying to figure this out since yesterday through Excel forums, but to no avail.

This is my basic table I currently have.

Col1 Col2 Col3 Col4 Col5 Col6 Col7
Item1 24 2.00 48 1.50 96 1.00
Item2 24 3.00 48 2.50 96 2.00

I want to be able to use a macro and do the following for everyrow in the Worksheet. Basically copy two different ranges and paste in new rows.

Col1 Col2 Col3
Item1 24 2.00
Item1 48 1.50
Item1 96 1.00
Item2 24 3.00
Item2 48 2.50
Item3 96 2.00

Any help is appreciated.

A:How do I Copy Two Cell Ranges to New Blank 2 Rows?

Welcome to the board.

We don't know if there are any blank cells in your current setup. If there aren't*, try this (which assumes cols I:K are free):

Sub test()
Range("I1") = "Item"
Range("J1") = "1st"
Range("K1") = "2nd"
x = Range("A" & Rows.Count).End(xlUp).Row

For Each Cell In Range("A1:A" & x)
Range("I" & Rows.Count).End(xlUp).Offset(1).Resize(3) = Cell
Cells(Cell.Row, 2).Resize(, 2).Copy _
Range("J" & Rows.Count).End(xlUp).Offset(1)
Cells(Cell.Row, 4).Resize(, 2).Copy _
Range("J" & Rows.Count).End(xlUp).Offset(1)
Cells(Cell.Row, 6).Resize(, 2).Copy _
Range("J" & Rows.Count).End(xlUp).Offset(1)
Next Cell
End Sub

*if there are, post back for some fancy formulas.

Read other 3 answers

Excel Masters, I am not sure how to loop through the following code for multiple row formatting. The code works fine if the user has selected cells within a single row. However, when multiple rows are selected, the 'With ActiveCell' formatting only works for the first row selected. How can I loop the 'else' statement to format the ranges for all rows with a cell selected?
Private Sub Button_DeleteRow_Click()
Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then



Selection.EntireRow.Interior.ColorIndex = xlNone
With ActiveCell
Range(Cells(.Row, "AA"), Cells(.Row, "AN")).Interior.ColorIndex = 15
Range(Cells(.Row, "c"), Cells(.Row, "d")).Interior.ColorIndex = 15
Range(Cells(.Row, "a"), Cells(.Row, "a")).Interior.ColorIndex = 15
Range(Cells(.Row, "j"), Cells(.Row, "j")).Interior.ColorIndex = 15
Range(Cells(.Row, "n"), Cells(.Row, "n")).Interior.ColorIndex = 15
End With

End If

End Sub

On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.

A:Solved: Excel Macro - Loop selected-cell formatting for multiple rows

Something like:

Selection.EntireRow.Interior.ColorIndex = 3

msg1 = MsgBox("Delete this row?", vbYesNo)
If msg1 = vbYes Then



Selection.EntireRow.Interior.ColorIndex = xlNone
SelRows = Selection.Resize(, 1).Cells.Count
Cells(ActiveCell.Row, 27).Resize(SelRows, 14).Interior.ColorIndex = 15

'(and so on)

End If


On a related note, I already have a separate worksheet change event (ByVal Target As Range) running on this worksheet.Click to expand...

If you mean "how do I bypass that for Selection.EntireRow.Delete then:

Application.EnableEvents = False
Application.EnableEvents = True

Read other 2 answers

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it

Read other 3 answers


I have been working on a Macro in excel to take a specific criteria (cell B2, Sheet1) and search column A sheet3 for this criteria. If it is found paste that row (A-H) on sheet1 beginning at A:17. There is the possibility for 2+ rows of data with this specific criteria. I want to be able to type in cell B2 on sheet1 and have the macro lookup all rows on sheet3 and paste them beginning at A:17 on sheet1. Any help on this would be much appreciated.

A:Solved: Copy multiple rows in excel based on one criteria

Read other 9 answers

Hi All,

I need to make a macro that will add borders to certain cells.
I have a generated report from another product so I don't know how many rows there will be until the report is generated. The number of rows is specified inside the report, so I do have access to it. It is stored in cell C5.

So what I want to do is add borders around C5 number of rows starting at A7..E7

I tried using a rule, to just put borders around cells that contain data, but that won't work because sometimes cells are blank and I still want the border around them.

Help?! Please

Thanks a bunch!!!

A:Excel Macro to add borders based on cell contents?

Hi Lori

Here is a macro that will take the value in C5 e.g. 10 and then apply borders to all cells in A7 to E16
Sub add_borders()
lRow = Range("C5") + 6
Range("A7:E" & lRow).Select
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlC9ontinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
End Sub

Read other 1 answers

Hy guys

2nd time i am posting stuff for help, and as i was helped before i will again look forward the response.

I have a file of excel, in which i am sending emails to different candidates of admission, with scan letter placed in the same folder by name.

I want to edit this code, which could select attachment based on Column A list adjacent to the email address

I am attaching the file also pasting the code

Sub Test1()
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
'Working in Office 2000-2013
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim cell As Range

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")

On Error GoTo cleanup
For Each cell In Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And _
LCase(Cells(cell.Row, "C").Value) = "yes" Then

Set OutMail = OutApp.CreateItem(0)

strbody = "We at Graduate School of Engineering Sciences and Information Technology are extremely pleased to know that you have selected Hamdard University as preferred choice for your graduate/post-graduate Studies. " & vbNewLine & vbNewLine & _
"Hamdard University is a pioneer Higher Education Institute (HEI) of Karachi producing Masters and PhDs in the fields of Engineering, Computer Sciences, Information Technology, Energy and Environment since 19... Read more

A:Attachment based on cell value in a excel email macro

anybody ???

Read other 2 answers


I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?

A:Excel 2003 replace macro based on value in other cell

Read other 6 answers

I have the following macro I created to merge A1-A4 to one cell, how do I run the same for the entire sheet? I have 975 rows on my spreadsheet

DimoutputText As String
delim =" "
On ErrorResume Next
For Eachcell In Selection
outputText= outputText & cell.Value & delim
Next cell
.Cells(1).Value= outputText
.HorizontalAlignment= xlGeneral
.VerticalAlignment= xlCenter
.WrapText= True
End With
End Sub

A:Solved: copy macro to remaining rows


Try this ... it assumes that the columns you are merging are A-D and that the first row is Row 1.
Sub JoinAndMerge()
Dim lRow As Integer
Dim col As Integer
Dim i As Integer
Dim outputText As String
delim = " "
lRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lRow 'Change the "1" to the first row you want to change
For col = 1 To 4 'Change "1 to 4" to the columns you want e.g. "5 to 8" would be cols E to H
outputText = outputText & Cells(i, col).Value & delim
Next col
Range(Cells(i, 1), Cells(i, 4)).Clear
Cells(i, 1) = outputText
With Range(Cells(i, 1), Cells(i, 4))
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
outputText = ""
Next i
End Sub


Read other 2 answers

I am having data structure as below. I need some macro or some means to populate formula in Column D based on column C (as and when it changes) . All other column data are populated. This is beyond me. Any help or direction is greatly appreciated. Thanks in advance

Col | A | B | C | D | E
Row | 1 | Test1 | | | 10
| 2 | Test2 | =A1 | =MAX(E2) }| 15
| 3 | Test3 | =A2 | =MAX(E3) | 20
| 4 | Test3 | =A1&","&A3| =MAX (E1,E3)| 30

A:Excel macro or formula - change field based on other cell

Read other 7 answers

I need to create a macro in Excel that will look for a certain text within a column (Column A), Copy the data in another column (Column B), and paste the data in another column (Column C).

For Example....

Column A

Column B

So, I would like to copy all the data in Column B that correspond with "ABC1" in column A, and paste that data in Column C.

A:Solved: Excel Macro Search and Copy Rows

Read other 16 answers

Hi... I'm somewhat new at Excel and really new at Macros.

This is what I'm trying to do:

I have two workbooks -

on the first workbook I have about 1000 rows of informtion with the following six colum headings...
ID# Last First Mid Dorm Decal#

The first five colums are all filled in already. I have to put the Decal#'s in as I get them.

What I need is that when I put a Decal# in (lets say F23) for a particular person, a Macro (or Formula) would then trigger that row (A23:F23) to be copied and then pasted to the next available blank row on a sheet that is located in a different workbook.

The first workbook is called -
Student List_Decals Input (SheetName is ResidentCommuterList)

The second (target) workbook is called -
Veh Plate_Decal Info Master (SheetName is Master Plate Info List)

I don't know if a Formular can handle this type of thing or if a Macro would be better... but if anyone has a good suggestion, I would be greatful.


A:Solved: Macro to Copy & Paste Rows to Different Workbook

Read other 11 answers

I am trying to sort the colored rows, copy and paste it in particular format.
I have attached a sample excel file.
1. A cell has values which are Purple in color.
2. B cell has values, yellow, Blue and White in color.
3. I want concatenate A-Purple, B Yellow and paste it in next work sheet "Result"- Col2
4. Blue rows need to be copied and pasted to Col2
5. White row need to get pasted to Col4
6. Want to repeat this for whole worksheet.
Can you please suggest a macro for this?

A:Need excel Macro for copy paste colored rows

Read other 7 answers


I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?


Read other answers


My VBA/Macro knowledge is very basic, but I am able to record macros and edit them. I have a scatter plot (with 5 series) and I want to automatically change the line style (i.e. solid or dashed etc.) depending on the text in a cell. This is what I tried, but I cannot get the code to reference the cell which contains the text:


Sub Macro14()
' Macro14 Macro
ActiveSheet.ChartObjects("Chart 1").Activate
With Selection.Format.Line
.Visible = msoTrue
.DashStyle = msoLineSolid
End With
End Sub

For the line ".DashStyle = msoLineSolid" I tried ".DashStyle = Range("A7").Select" but this does not work.

Is there a simpler way to set-up a macro to format graphs automatically based on a cell content/format/colour, does anyone know a good guide to do this?


A:Excel Macro to automatically change chart line style based on cell text


I'm not familiar with charts. But, see if this helps.


Read other 3 answers

This Excel document sends an email reminder to follow-up on tasks. I've added two more reminder columns F & G. I want the macro to run in columns F when I change to due date in column C. I'm going to be keeping the information in Column E for tracking purposes.

What would be the Maro for this?


Read other answers

So first of all let me thank everyone that had input on my last post....i certianly appreciate the help.
so heres what i got going now. i have formatted the class rosters that will be used and i have a master data base of numbers that automatically updates each time a new class is added. and while some ppl think its great others are like well why cant it automatcally populate a list of everyone who failed, got dropped, or didnt show up. so thats what i need to do. i need some vba help on this one ( i actually got them to let me use macros now). i have the class rosters set up to where if a student does not show up then the row get highlited red and yellow if they get dropped, and i will need to add another color like blue for fail. anyways, the workbook is built like this: there is an annual training sheet ( total of all monthly numbers), then 12 monthly training sheets, and 52 weekly training sheets and then an unknown number of daily class rosters. looks something like this:

/ANNUAL//JAN 2011//3-7 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//10-14 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//17-21 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//24-28 JAN//CLASS 1//CLASS2//CLASS 3//CLASS 4//FEB 2011/......ECT.

ok so what i need to do since the tabs that say "class 1, class 2" and so on will actually have their own unique name depending on the date, type of class and location it was taught. is to be able to have a vba macro (that i will use on each weekly tab and just change the fi... Read more

A:need to search multiple sheets and copy based on cell color

If it's a macro, you can test if .font.color = vbred then .... if .font.color = vbgreen ... etc.

If other then you would have to list the color value or colr index as a variable and test on that.

Read other 3 answers

In the "Checkout" tab, I am wanting to copy rows if/when a cell (column "B") in that row is greater than zero. I need this macro to go to the next row and the next row and the next row until the cell in column "B" is blank. Once the selection has been copied, I need it to pastespecial only the values and not fomulas in the "Database" tab.

I have attached an example spreadsheet.

Any and all help is greatly appreciated.

A:Excel Macro to copy row if cell is greater than zero

This is a duplicate of your other post

Read other 1 answers


A pretty inexperienced VBA'er here.

I have 2 named ranges:

- LastStatus = "J8:J500"
- CurrentStatus = "K8:K500"

What I would like to do is create a loop to copy the cells in CurrentStatus over to the LastStatus column if and only if there is a value in CurrentStatus (column K). If there is no value in column K and there is a value in column J, I do not want change the value in column J.

Any help would be greatly appreciated.



A:Excel - Conditional Copy / Paste (Same Worksheet) based on cell values

You don't need a loop.

Sub test()
Range("LastStatus").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[1]<>"""",RC[1],"""")"
Range("LastStatus").Value = Range("LastStatus").Value
End Sub

Read other 1 answers


I would like to be able to input a number into a cell B3 on sheet 1 - 'Head Count'. Then get the macro to look up the number that was input and copy and paste all the rows relating to it from sheet 2 - 'Data' and paste them on 'Head Count' next to the input cell.

I am having trouble with getting the macro to copy and paste depending on the target cell. This is what i have so far:

'Copy cells of Data from rows containing cost centre in col G of the active worksheet (source sheet) to cols
'of Head count (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Head Count")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1
For sRow = 1 To Range("G65536").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "G") Like "Worksheets("Head Count")Range("B3")" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols

' With Range("A14:AA14", Range("A65536").End(xlUp))

Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
Cells(sRow, "D").Copy Des... Read more

A:Excel macro copy and paste depending on a cell

Can you upload a workbook with dummy data so we can see how you've structured the data.

Read other 1 answers

update on the below question:I think something is wrong when I opened the .csv file.If I leave the file open then run the macro, it works ok. If I open the file within the macro, I get the format problem.Any advice?Thanks!Hi,

Please can someone give me some advice on this?

I am writing a vba code in Excel 2003 to copy from a source .csv file and paste into xls file.

First, I tried this using macro recording, it worked fine. so I recorded the macro and tried to apply it. Something strange happened.

One of the columns is date, and originally is format Date (*01/01/01). If I manually copy and paste, the format is fine. If I use the macro, some (and only some) of the cells in this column will change format to General and align to the right, while the others keep the original Date format and align to the left.

How did this happen?

I viewed the .csv file using Notepad, and cannot see any difference between these dates that end up in different format.
And if I manually copy/paste, there will be no format issue at all.

Please can someone help?


A:copy/paste in excel macro changes cell format

upon a closer look, i think I found some clue, but still far from a solution, here is what I observed.

for date 09/07/2006, excel interpret it as Sept 07 06
for date 20/06/2012, excel interpret it as June 20 2012

please note the year format is different!

any advice pls?

Read other 1 answers

I have been reading up on creating log files with Excel Macros.

I am trying to figure out if it is possible to have a Macro output the contents of the currently selected Cell to a text file?

A:Solved: Excel Macro Copy Cell to log file.

Read other 16 answers


I have a table that looks like the attached sheet 1. This table goes on for a number or columns, that varies on different data downloads. I am looking for help to create a macro to grab the information under each heading for each person and put it into rows as per attached sheet 2. Some will have "Term Date" and some wont.

View attachment example.xlsx
Is there a macro I can write or have assistance with to do this easily. Unfortunately our data download format cannot be fixed.

Thank you to any who can help me! This will save me hours of work on a weekly basis!!

A:Solved: Macro to copy, paste and repeat based on coloumn header

Hi Loiuse, welcome to the forum.
You Sheet1 is not consistent but I managed something

The macro EmployeeDetails2Table will append data to Sheet2, no check for dupliactes, just plain appending data
Try it and let me know.

Read other 2 answers

Hi guys,

I've attached the dummy file. You see the report groups all the relevant data pertaining to the person's name and puts it under the name of the person. I need a macro that will copy the name of the person in column A across all the rows of data pertaining to the person's name in column Z. I will not know which row the name of the person will be in or how many rows of data each person will have, so the macro needs to search through all the rows to find specific names.You'll see an example of what I need in column AA.

I then wrote a macro just based on reading in the net to remove the blank rows in column A. For example A6 is blank, so the macro I wrote deletes that column. The macro also deletes the "Total No. of Cases" row.

This is the macro:

Sub Deletejunk()
' Deletejunk Macro
' To delete rows containing *No. of Cases* & Blanks
' Keyboard Shortcut: Ctrl+Shift+O

With ActiveSheet
.AutoFilterMode = False
With Range("a1", Range("a" & Rows.Count).End(xlUp))
.AutoFilter 1, "*No. of Cases*", xlOr, ""
On Error Resume Next
End With
.AutoFilterMode = False
End With
End Sub
I would like that after the previous macro copies the names to column Z this macro could run straight after that. Even better if this macro could delete those names in column A. I'm thinking we would then have to make an array of things to... Read more

A:Solved: Macro to copy text from a cell to a column with specific criteria

Read other 8 answers

I am trying to create a macro that will select the text contents of a cell andcopy it into another cell. I know recording a macro isn't the best way to go,but I'm still pretty fresh in coding VBA.

When I record a macro on the following set of data (shown below) I cancreate a row above the first row containing "CASH" and copy the word"CASH" into the cell above it.

(sample data)

CASH 83 521.01
TRES.CARD 11 54.11
HELP 0 0

(recorded macro)

ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "CASH"
ActiveCell.Offset(-1, 0).Range("A1").Select

If I run the macro on the row containing "Tres.Card" it willreplace &#8220;TRES.CARD&#8221; with "CASH".

Essentially I am trying the make two rows for each of the existing rows. Iwould add "#" to the label in column 1 on the first line and add"$" to that label on the second line. The first line would thencontain the data from column B and the second line would contain the data fromcolumn C. So that the end result would look like:

(Example of end result)

CASH # 83
CASH $ 521.01
TRES.CARD $ 54.11
HELP # 0
HELP $ 0

If anyone has any suggestions of the best way to do this that I'm not thinkingabout or aware of, I would appreciate it.

Otherwise, does ... Read more

A:Solved: Creating an Excel macro to select text content of a cell and copy it into ano

Read other 9 answers

Hello everyone,

I am completely stumped and I hope to find some help from someone here... I am trying to format an excel spreadsheet that has over 10k records is a very strange format. Please see attachment...

I have created the logic to grab the numbers to the left of the "carrot" in cell E3 and replicate that number in cells B3 B4 B5 B6 B7 B8 B9 B10. That process is replicated to pull the numbers to the left of the "carrot" in cell E13 and replicate that number in cells B13 B14 B15 B16 B17 B18 B19 B20...And so on and so fourth. The problem I am stumped on is how do i replicate this process all the way down the spreadsheet?

One FYI is that each record "bock" is not always (as in the example spreadsheet) 10 lines it can be 11/12/13 or more lines. Basically the record starts at the letters "MSH" (see A1) So I the logic I am thinking of is to push the code in red in the "E" cells to the "B" column after the "EVN" (in cell A2) and to stop before the "MSH" (in cell A11) and continue that process down the spread sheet.

I hope this is not too confusing but I have been banging my head on the wall for hours and just cant figure it out. Thank you all for your help

A:Solved: Very hard macro in Excel (copy part of one cell to several areas with logic)

Read other 8 answers

I need to copy n number of rows from a sheet in my local machine to a sheet in fileserver.
My requirement is - if I select last 2 rows in the sheet in my local machine I need the rows to be copied after the first blank cell in the sheet in fileserver.
Also it would be helpful if the macro code is only in the local sheet and not in the fileserver sheet.

I am a newbie to macros pls help....

Read other answers


I need help to create an Excel macro that would

1. ask users to select x number of rows to be copied from one worksheet of one Excel file
2. once users have selected the rows to be copied, then the rows are copied from the one original Excel file into X number of target Excel files

the target Excel files are all based on the same template
there is one worksheet in each of the target Excel files

in the target files, the rows should be copied from the first available empty row, going down

Looking forward to your help!

Thanks a lot.

A:Macro needed to copy x number of rows from one Excel file into a batch of Excel files

Hi, welcome to the board.
Not much info there to get the correct picture.
Sample? Of source and template please
And not to forget, what version of Excel are you and the users using?

Read other 1 answers

Hi. I need help! I created a macro to copy two rows from worksheet to another. I need it to be able to repeat mulitple times. I need it to always paste to the last available blank row. The problem is that it will only run once. I try to run it again and it is selecting rows further down on the worksheet that has the rows I need copied.
this is what I have so far....
Sub copy2()
' copy2 Macro
' Keyboard Shortcut: Ctrl+w
Sheets("Orders In Production").Select
ActiveCell.Offset(0, 0).Select
End Sub

A:macro to copy two rows from worksheet to another worksheet to the next open row

To select the last filled row I suggest the following

The last available blank row is:

Sheets("Sheet2").Range("A" &rows.count).end(Xlup).row + 1).select

This locates the last filled row and then goes one down.

Try this to checkit
Sheets("Sheet2").Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select


Sub Test
Sheets("Sheet2").Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Select
End Sub

Read other 1 answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers

I have a sheet set up with the list with the description (text) in column B, and summary scores (numerical, percentage) in column D. I want to do a summary row at the top of the sheet that pulls the data from the B cells, based on the lowest 3 values in column D.
I plan on using the formula =SMALL(D7:D32,1) (with d7:d32 being the list of percentages), to figure out the lowest 3 values. But the formula just pulls the summary score, not the description. I want to pull the description into but I am at a loss.
I am using excel 2013 on windows 10. Any help would be appreciated.

Read other answers


I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
'If no Issue, Location/Obsevations should be blank
Next i

End Sub


A:Solved: Clearing Cell Contents Based off other Cell

try this variation;

Dim p As Long
p = Cells(Rows.Count, "a").End(xlUp).Row

For i = 1 To p
If InStr(1, Range("k" & i), "None") > 0 Then
Range("L" & i) = ""
Range("M" & i) = ""
End If
'If no Issue, Location/Obsevations should be blank
Next i

End Sub

Read other 2 answers