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

Free Microsoft Excel 2013 Quick Reference

Macro to copy entire row from a selected cell

Hi there,

I have a macro that will go through a set of numbers and check them against a condition. if the cell complies with the condition i want to copy the entire row to a new sheet. sofar I have tried the following to select the current row but to no avail.

'ActiveCell.EntireRow.Select
'or           
'ActiveCell.EntireRow.Activate
'or           
'Selection.EntireRow
here is the code i'm writing: (the numbers are normally variables, but changed them for ease of checking), and the delete function is there just to check that the conditional selection works.

Sheets("Sheet 1").Select
    Range("O13").Select
        For i = 1 To 2000
        ActiveCell.Offset(1, 0).Select
    
            If ActiveCell.Value > 39990 And ActiveCell.Value < 40010 Then
           
            'Now I want to be able to copy the entire row in which the cell is into a new sheet   
           Selection.ClearContents 'currently I use this to check that it is selecting the right cells
            
            End If
    
        Next i
Many Thanks in advanced


Post your answer or comment

comments powered by Disqus
Hello,

I have data of the time in/time out of different individuals taken from another program . I don't know how to make a macro to delete entire row if that particular person timed in/timed out the second time. I'm new to macro and know only copy paste and offsets a little. I attached a sample file.

Thanks.

Is it possible to build a macros that deletes entire row from A to F if cell A has specific content? (sample attached)

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

Hi,
Newbie here
Looking to copy an entire row based on text selected from a list box
I.E. if someone selects say "SRR" from a list box in Column J can I use a VLOOKUP to copy entire row?
Many Thanks in advance
Neil

I'm trying to create a macro to copy the values from a range of cells (R47:R90) on one tab (DRAWINGS), to a range of cells (D6:D47) on another tab (INDEX). My problem is that the cells on the INDEX sheet, have cells D:I merged for each row. I did a little search on the Web and found this code http://www.pcreview.co.uk/forums/re-...-t3752408.html where the contributor states "Drop the copy|paste special|values and just assign the value"
Dim FromCell As Range
Dim DestCell As Range

With ActiveSheet
Set FromCell = .Range("c8") 'The FROM is actually on Sheet DRAWINGS range is actually R47:R90
Set DestCell = .Cells.Find(What:="", _ 'The destination is on Sheet INDEX and is actually D6:I47 (because the cells
are merged)
After:=FromCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
Searchorder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If DestCell Is Nothing Then
'do nothing
Else
DestCell.Value = FromCell.Value
End If
End With
I've tried to modify the code as follows but I know I don't have the "DestCell" coded correctly
Sub Macro8()
    Dim FromCell As Range
    Dim DestCell As Range

    With Sheets("Drawings").Select
        Set FromCell = .Range("R47:R90")
    With Sheets("Index").Select
        Set DestCell = .Cells.Find(What:="", _
            After:=FromCell, _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            Searchorder:=xlByColumns, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
            If DestCell Is Nothing Then 'do nothing
            Else
                DestCell.Value = FromCell.Value
            End If
    End With
End Sub
Any help would really be appreciated.

Hi
I need an help to create a macro to copy the value from number of excel sheets and paste it into another single excel sheet.
I have number files which are located in one location (ex: test, test1, test2, test3, test4)
I need to open each file and copy the item number which is in Cell B2 in the file named test.xls and paste it in the FINAL workbook and then again copy Total_Liab value in the tab named Calculations in the same workbook test.xls (total liability value) which is the last cell value in the column Q (example: Q51 in the Calculations sheet) Q51 and paste it next to the same item number in the FINAL workbook. Once it perform the action it should close the test.xls worksheet and then open the next test1.xls worksheet and should follow the same procedure as explained for the test.xls. Could you please help to create an macro

Hello,

I was after a macro that would highlight the entire row of a selected cell.

The tricky part is that I would also like to highlight at the same time the 10th row before and after the selected cell.

This would obviously raise some problems if cell A1 is selected because there are no rows before row 1.

could someone please help with this?

edit: if more than one cell is selected, then the macro should do nothing to avoid further complications...

thanks
andy

Does anyone know how I could write a macro to delete all rows from the first
empty row to row 2000 please? If I use the control+shift+down arrow and then
go down one cell, the macro programmes the precise cell reference -- but this
changes on datasheets with different amounts of data. HELP PLEASE!!!

Hello, just registered with the hope that someone can help me with my complex (at least to me!) requirement. I have 2 workbooks (lets call them "A" and "B"). Consider Workbook A as the "master" workbook with LOTS of rows. Workbook B has a subset of the rows that Workbook A has, and the first 4 columns have the same type of data. I need to copy entire rows from Workbook A (the "master") into a new workbook C, where the first 4 columns of Workbook B match the first 4 columns of Workbook A. So essentially the first 4 columns comprise the composite key for the lookup. Is this possible to do with a simple function, or by combining different functions like VLOOKUP, INDEX, etc.? Hopefully an example will help. Lets say Workbook A has the following 3 rows of data:
[123, AL, Blue, A, ababababa, ytytytytyt],
[123, AL, Blue, B, erererererer, bvbvbvbbvbv],
[234, AL, Blue, A, dhfhdhfhfhf, hghghghghg].

Workbook B has 1 row as follows:
[123, AL, Blue, B].

How do I match the data in Workbook B with whats in Workbook A, and copy the entire row from Workbook A [123, AL, Blue, B, erererererer, bvbvbvbbvbv] to Workbook C?

Any help appreciated!

Does anyone know how I could write a macro to delete all rows from the first
empty row to row 2000 please? If I use the control+shift+down arrow and then
go down one cell, the macro programmes the precise cell reference -- but this
changes on datasheets with different amounts of data. HELP PLEASE!!!

Hi

I want to copy entire rows matching a condition to a new sheet.

The original sheet contains for example a column called impact with values
between 1 and 20. Is there a possibility to automatically copy all rows with
an impact > 16 to a new sheet called Priority 1, all with a value between 12
and 16 to a sheet priority 2, etc instead of copying the sheet manually,
sort the data manually and delete those rows not meeting the criterium?

Thanks very much

Hi. I need a macro to copy certain columns from one sheet to another. I already have code that sort of works that I have taken from elsewhere but I need little bit help to correctly finish it off. I have sheets called "Hyperlink" (the source sheet) and ME22 (the destination sheet).

The code that I have so far does the following:
It takes info from column 4 from source sheet and places in column 1 on destination sheet
It takes info from column 5 from source sheet and places in column 2 on destination sheet
It places letter "X" in column 3 on destination sheet
It takes info from column 14 from source sheet and places in column 4 on destination sheet
It places the following to col 5 on destination sheet - concatenation of text "Supp Recoll" + column 21 and column 14 from source sheet

Now the code that I so far have is the following:
Sub test()
Dim a, i As Long, maxval As Double: Application.ScreenUpdating = False
With Sheets("Hyperlink"): a = .Range(.[a2], .Cells(Rows.Count, "b").End(xlUp).Offset(, 22)): End With
With Sheets("ME22"): maxval = Application.Max(.Range(.[a2], .Cells(Rows.Count, "a").End(xlUp)))
For i = 1 To UBound(a)
    a(i, 1) = a(i, 4): a(i, 2) = a(i, 5): a(i, 3) = "X": a(i, 4) = a(i, 14): a(i, 5) = "Supp Recoll "
& a(i, 21) & a(i, 14)
Next: .Cells(Rows.Count, "b").End(xlUp).Offset(1, -1).Resize(UBound(a), UBound(a, 2)) = a: End With
Application.ScreenUpdating = True: End Sub
1) Now the problem with this current code is that it carries on copying information from source sheet to destination sheet as far as it column 24. I want it to stop at column 5 (column E) as the last column being filled in. Rest of the columns should stay clear. How can I adjust my code to make this happen?
2) Line : With Sheets("ME22"): maxval = Application.Max(.Range(.[a2], .Cells(Rows.Count, "a").End(xlUp))). How could I change this line as the "maxval" was defined in the original code that I used to learn and to get this far. This feature is not required in my macro but I have no idea how to remove it without rendering the rest of the code useless.

All ideas are ideas are welcome. I have attached a test spreadsheet.

Hi all,

I am trying to copy specific information from a web site to Excel using VBA. I have tried a couple of methods found on the internet to no avail. The type of copy proceedure that I tried was execCommand "copy". When I wrote the code ie.document.execCommand "copy". It just wants to copy the whole page. I just need on piece of the page. If a form name is needed, I think it is form1. I cant put the entire source code in here, due to this being a private site. Thanks in advance for your help.

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.

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

Hi I am very new to excel and looking for some help.

I have a spreadsheet that is a list of events. I need to basically need a formula that says If Cell C1 = ADOTG , Then Copy Entire Row Into A New Sheet. (ADOTG is the name of the event). Can anyone help this excel beginner?

Hi,

Does anyone know of macro that would delete the entire row where both column F and G are blank? Also I would only want this to delete between rows from 4 - 57.

Any help would be much appreciated

Thanks

:-)

Hi all,

my problem is this:

1'st I'm an Excel noob, haven't really used any advanced features.

2'nd I need to copy specific information from a cell that has a lot of other text that's not needed in it.

3'rd This has to be done several thousand times.

f.ex. I've got a thousands of cells with: "Product name, compatability, product number, product dimensions, product specifications". How do I copy "compatability" on all these to a separate cell for each of the "compatability" automatically? I guess I'll have to use a macro, but does anybody know how I should code this?

Would greatly appreciate any help.

Best regards
Thomas.

I am trying to build a formula that would say If cell contains text then copy/paste text from a different cell.

For example:

I am writing my formula in cell I5. If there is any text in J5 then I want the text from J3 to appear in I5.

Background:

-What I am doing is making an excel spread sheet for meeting notes.
-Column A has the project names.
-Column I is the date the project was last updated.
-Columns from J on, represent weeks. (Column J is for 1/1, Column K would be for 1/8, Column L would be for 1/15, etc.) The column heading (the cells in row 3) would be the date.

Each week, a new column is added (for a new week) and if there is any new information it is typed for that project for that week. If there is any new text in that week's column, I want the heading for the column (in my case the date for that week) to appear in Column I.

I hope this makes sense; please let me know if I need to clarify anything.

Thank you,

Laura

could someone help me with a macro to delete certain rows from a sheet based on the value of a cell?

I'm tryin to get it so if the value in "F" is NOT 150, 151, 152, 153, 154, then i want that row deleted.
this should leave me with data that contains either 150 - 154 in column F.

anyone got any ideas?

thanks

I would like to copy UNIQUE data from a column in a SINGLE CELL

Hi!!I saw a post with the same title and I need a similar thing. I want to copy the rows if in one cell is a numeric value and paste this row in a new sheet. I have a lot of sheets and I want to ejecute the same instruction to paste into a unique sheet.

i send and example file. In this example I want to copy the rows when there're a numeric value (a value) in colum c and repite the same operation to all sheets. I want to paste the rows in Hoja4

Hi. Thank you for reading and helping. I am trying to work out how to move an entire row once a particular cell is marked with an exit date.
The colums go to BK and once BK is marked with an exit date I need the data to move onto sheet "CLOSED"

Also, The data that I have on the active sheet has varios rows that contain entry date and exit date from one program to another. The formula calculates the days in each particular service but how can I get it to calculate the days a part of the service.
EG: Date into "X" = 1/1/2010 Date out of "X" 1/3/2010 = 59 days. How can I calculate days within that period eg: 2/2/2010 to 29/2/2010??

Am I confusing??

Hi

I wonder if anyone could help me with this problem

I am responsible for compiling a compilation of my company’s data from each source to a Master workbook, I need a macro that can be run from each divisions workbook (all are identical apart from the name and date) that will copy entire rows from 3 tabs into the same tabs on my master workbook.

The following is the criteria.
Master workbook name
"All ships compilation"

Tabs are identically named on both reports
Cruise Report YTD
Tables & Slots YTD
Staff Hours

Cruise Report YTD, I need to copy rows 7 to 371 and paste them into the same tab “Cruise Report YTD” on the master report “All ships Compilation”, starting at the first blank row available.

Tables & Slots YTD I need to copy rows 8 to 372 and paste them into the same tab “Tables & Slots YTD” on the master report “All ships Compilation”, starting at the first blank row available

Staff Hours, I need to copy rows 2 to 100 and paste them into the same tab “Staff Hours” on the master report “All ships Compilation”, starting at the first blank row available

I am sorry to trouble you all with this as I have trawled the forum and found some examples of similar actions but have not got the VBA knowledge to change them to what I need or to understand them properly enough to do it.

Thanks for your help in advance.


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