Free Microsoft Excel 2013 Quick Reference

copy and paste rows from one sheet to another

Hi,

I am new to excel and am unsure how to loop through a column continuously. What is suppose to happen is when I open the document, the Rows that have a Hire Date (column C, row 5 onwards) in the Future Hirings Sheet equal to or greater than today's date (cell A3), then that row gets copied and pasted over to the Current Hirings Sheet. However, it is only copying one row at a time, whereas if multiple rows had a hire date equal to or greater than today's date, then they should all get copied and pasted over and then deleted from the Future Hirings sheet, however this is not happening. I found out from experiementation that it is the delete code that stops this from happening. Is there any way of changing my code to work or adapting a loop to my code?

Any help is appreciated.

Below is my code..

Dim i As Long

With Sheets("FutureHirings")
For i = 5 To .Cells(.Rows.Count, "C").End(xlUp).Row
If .Cells(i, "C").Value >= .Cells(3, "A") Then
.Cells(i, "C").EntireRow.Copy Sheets("CurrentHirings").Cells(.Rows.Count, "C").End(xlUp).Offset(1, -2)
.Cells(i, "C").EntireRow.Delete
End If
Next
End With

Regards,
Travis


Post your answer or comment

comments powered by Disqus
Hi,

I need to copy a bunch of formulas from one sheet to another. I want the formula to continue to pull data from sheet 1. Cut & Paste works, but copy and paste will only let me either paste the formula (does not refer back to sheet 1) or paste the value. I need both sheets to update as data is input.

Type of formula that is being copied:
=SUMPRODUCT(($W$20:$W$97="n")*($T$20:$T$97="y")*($U$20:$U$97="n")*($V$20:$V$97="n"))

Many formulas involved.

Help please!

I have been tasked to find a macro that I can use to copy and paste data from one sheet to another by matching a reference from both sheets. Conditions that I need to work is that 1.) I need the data to be able to sort with associated data. 2.) The data is in Date format and must be kept so. 3.) Additional data may be entered and moved so I need to be able to do that for as far down the spread sheet as data exists.

A quick explanations of my task I have a big workbook but only going to be working with two sheets. The main sheet which is labeled Tracking will have alot of other columns labeled with data in it. The Sheet labeled Mail tracking is a smaller sheet with other data. My sample i excluded the unimportant items in both sheets. My goal it to move the ship date from the mail tracking sheet matching the package number to the Tracking sheet matching the same package number to column S on the tracking sheet. Keeping in mind the conditions noted above.

I know this may be really simple but this would be a very useful tool if i can also learn how to apply it to other different data sets. I have added an example attachment which will allow me to complete my task with out changing too much. I have limited excel experience but a general knowledge of how it works. As far as formulas and macros work very general. Any help is welcome and i will be trolling my thread till I can find a solution so ask any questions to clarify as needed.

Can someone please help tell me what is the correct syntax/ie vb code
to copy a entire row from one sheet to another?

Pretend I have two sheets and want to copy an entire row from sheet2
to sheet one , what is the code to do something like this?

I know I can do it manually, but for what I am doing I need
the macro code to do it automatically.

Thanks

--
bxc2739
------------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php...o&userid=32538
View this thread: http://www.excelforum.com/showthread...hreadid=545604

Can someone please help tell me what is the correct syntax/ie vb code
to copy a entire row from one sheet to another?

Pretend I have two sheets and want to copy an entire row from sheet2
to sheet one , what is the code to do something like this?

I know I can do it manually, but for what I am doing I need
the macro code to do it automatically.

Thanks

I need help in creating a macro to Copy rows from one sheet to another sheet in another workbook with all the groups and outline levels.
Thanks in advance.

Hey guys,

I have a workbook in which i have lots of different sheets with the same formatting, what i am trying to do is have a macro automatically copy and paste rows from these sheets to another sheet named "overdue payments" if they match a certain criteria.

The criteria is in column K with the option of "Yes" or "No". I want the sheets to copy and paste if the answer is "No"

also i would like this to automatically update when the answer is changed.

I have attached a sample workbook for you to understand what i am talking about.

I would really appreciate some help as i am now really stuck! i know it can be done but I'm just not sure how to write up the macro!!

Thanks in advance

Hi,
I'm looking for some VBA help moving entire rows from one sheet to another.

In the "Summary" tab of the attached sample worksheet starting row 10, if column AH is a "yes" then the entire row needs to be copied and moved to "Work in Progress" tab starting row 10. Then the rows that were moved from "Summary" tab should be deleted.

Also, from the "Work in Progress" tab, if column AH is "Yes" the entire row need to move to "Complete" tab and those rows removed from "Work in Progress" tab.

Truly thankful for any help I can get on this

Based on other criteria, from two cells, I would like to move entire rows
from one sheet to another. There will be more than 7 so I'm thinking I would
need to use a VLOOKUP as opposed to an IF.

Something like:
IF(AND(C1=1,AE5=1),MOVE ROW 5 TO SHEET5!ROW20.

Is this possible? If it is can you move multiple adjacent rows?

thanks for any help!

Hi All,

I have a Customer table with a 4 to 6 digit customer code which represents the invoice address for the customer.
For the delivery address(es) (they used the same table, doh!?) and added a 3 digit suffix to the customer code (eg 351063 becomes 351063001 for delivery address one, and 251063002 for the second delivery address etc).

I want to create 2 separate tables in a database with a one to many relationship (ie a company table containing the invoice address only, and a table with all the delivery addresses for that compay.

This involves testing to see if the code is an invoice address code or not. If it is, I want to copy that row into another spreadsheet and delete the row in the original worksheet. Then, moving from the bottom to the top of the worksheet, do the same for the next row. If that next row is a company code, then I want it copied under the row that was previously pasted and so on.

Finally, I need a column added in the new spreadsheet, which will contain all the invoice customer codes (acting as a foreign key in this table), and the column has to be populated with the invoice code (that is, if the del address code is 351063001, the invoice code is 351063).

It has been a while since writing VBA and I just cannot get this happening.
Can someone please help me with some elegant code to do the job? This is what I have done so far..

Thanks in advance.
Bon


	VB:
	
 CommandButton1_Click() 
    getInvoiceCode 
End Sub 
 
Function GetLastRow() As Double 
    Dim Z As Range 
     
    Set Z = Sheets("RawData").Cells(1, 1).EntireColumn.Find("*", SearchDirection:=xlPrevious) 
    If Not Z Is Nothing Then 
        GetLastRow = Z.Row 
    End If 
End Function 
 
Sub getInvoiceCode() 
    Dim length As Integer 
    Dim myString As String 
    Dim p As Integer 
     
    For p = GetLastRow To 1 Step -1 
        myString = Sheets("rawdata").Cells(p, 1).Value 
        length = Len(myString) 
        If length < 4 Then 
            Sheets("rawdata").Row(p).Select 
            Sheets("rawdata").Row(p).Copy 
             
             'If it is the first row copied,
             'copy the row from Sheets("rawdata")
             'into the second row in Sheets("newdata")
             'If it is not the first row copied,
             'copy the row from Sheets("rawdata")
             'into the next empty row
             'Delete the row from in Sheets("rawdata")
             'Finally, move to the next row
             
        End If 
         
    Next 
End Sub 

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


I download a spreadsheet everyday titled "All Records". I need to copy and
paste specific data into an existing macro from the "All Records" tab to
other tabs in the workbook. (by the way, this is my first attempt at working
with multi tab workbooks so I really appreciate your help). The column
headings I need to base my copy and paste on are titled, "Match" and
"OriginalTBL" (there are 4 other columns but I wont' be using those in the
sort criteria at all) on all my sheets.

My book has 12 tabs labeled "All Records", "Confirm", "GESV", "GESA" and so
on. I only want to copy and paste the rows from the "All Records" table to
the "Confirm" tab the meet the criteria of "No Match" in the "Match" column
and "Confirm" in the "OriginalTBL" column.

On a separate tab I want to copy and paste the rows from the "All Records"
tab to the "ConfirmALL" tab that meet the criteria of "No Match" and "4-$" in
the "Match" column and "Confirm" in the "OriginalTBL" column.

I have many other taps to code but I know if I get help with these two, I'll
be on my way. Thanks so much. Joyce

I need to copy data from one sheet to another in the same workbook. The data is never the same and can change without notice. Here is what I need to do:

The trigger will be in column A with the cells content being 1 through 85 (never all 85 numbers though, mostly 1 to 10 sheets need to be created). As I said it is not consistent and sometimes new ones are added or some are deleted.

For instance, if you find a 1 in column A1 and there is not a sheet named "1" then create it and copy all of the rows that have a 1 in column A. When column A changes to another number say 5, create a new sheet and redo the above until all is copied into separate sheets. My problem is that I don't know how to create a new sheet when the number in column A changes.

I hope that this makes sense and someone can help me with the code. Thanks.

I cannot copy past a formatted date cell from one sheet to another - excel
automatically adjust the date by 2 days and 2 years. Any info?

I need a formula to copy rows of cells from one sheet to another based on a
specific cell name value (North, Central, or South). I will have a master
sheet containing information from three teams, North, Central, and South, and
three other sheets individually named. I want to move the row data to it's
related sheet based on these names. The names will be in the same column, "M".

How can I copy data from one spreadsheet to another while keeping the
formats, column widths and row heights in tact? When I do a copy and paste,
the data doesn't look the same when it's pasted.

Hello - I'm trying to copy and paste values from one sheet to another based on a condition. I created some code using some ideas from around the forum but it's not working.

Column B has data points from rows 1 to 20. Columns A has the condition, which is "Frm". Both are in Sheet1. The macro should scan column A from 1 to 20, and if there is a "Frm" value in a cell, then the macro should copy the value from the adjacent cell in column B. This value should then be pasted to column C on Sheet2. The values copied to Sheet2 should be continuous with no gaps in between. (eg. if there are 10 "Frm" tags, then values should be pasted in rows 1 to 10 of column C sheet2, and not rows 1-20 with gaps in between). Following is the code I tried:


	VB:
	
 verify() 
     
    Dim i As Range, j As Range 
    Dim rn1 As Range, rn2 As Range 
     
    Set rn1 = Range("A1:A20") 
    Set rn2 = Worksheets("Sheet2").Range("C1:C20") 
     
    For Each i In rn1 
        For Each j In rn2 
            If i = "Frm" Then 
                i.Offset(, 1) = j 
            End If 
        Next j 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above code is only taking the last value in column B for which there is a "Frm" condition in column A and pasting that all throughout column C, rows 1-20. Could someone please help with this? Thanks.

I am looking to copy data from one sheet to another based on the value in a to columns. If column "N" contains "WDWO" and if "I" is less than "600" then I need to move three fields in that row to a different sheet. the fields that need to be moved are in columns a, e, j. I have attached a sample of the sheets before and after. My original sheet is sheet 1 and my filtered sheet is sheet 2. As always thanks ahead for the help and it is much appreciated! And I need this in a macro so I can automate this process because I have to do this with 8 different sheets.

How can I copy data from one spreadsheet to another while keeping the
formats, column widths and row heights in tact? When I do a copy and paste,
the data doesn't look the same when it's pasted.

hi there,

need some help guys on getting a macro or vba code for copying data from one worksheet to another. on the attached file, i have mentioned on dark grey filled with bold text the constant parameters i need to copy or transfer every day on the dBase sheet next to it. can you please help me setting up a code that will work for this situation? i find it really hard to construct a vba code that will pull up the date, description, vessel type, etc. etc and paste it on the next sheet.i would like to have it on a single button that after clicking it auto populates the data i need on the other sheet on the corresponding headings. also, i want to protect the data into it to make sure that nobody can mess around with it . would appreciate any assistance. thank you very much for helping out.

Dear helpers,

Please can you help me? I'm a teacher trying to create a report generator.

I have a spreadsheet (see attached) where I want Excel to automatically copy and PASTE data from one fixed cell to other cells based on the results of a dropdown menu list.

In Sheet 1 of the Dummy Report spreadsheet I have attached, cell A2 contains 3 names. In the cell B2, a teacher would type out a progress report (I know it's a small space but this is just for illustration/simplification purpose).

When the teacher has selected the student's name from the list, and typed the report, I want them to click a button. Then the computer would match the name from the dropdown menu with the correct student's name on Sheet 2, and then copy and PASTE the data from Sheet 1 B2 to the correct cell B2, B5 or B8 on Sheet 2.

Then the teacher would click on the Reset Report Text button on Sheet 1 and the report would be erased from Sheet 1 but the text would have to still exist in the correct place on Sheet 2.

I'm only a novice Excel user, but I don't think there is an Excel command I can use in a formula which will copy/paste something, so I am guessing it will need some VBA code.

Thank you for your help, I'm really very grateful.

I'm looking for an excel macro that can copy rows from one sheet to another within excel based upon having a specific word in the cell. I have one sheet called master and sheet two called feedback. Here is an example of the data:

A B C D mary bob 56 reply soon sam john 26 keep in touch sylvia pan 15 response needed

I'd like to copy the rows sheet "master" to sheet "feedback" if the data in column D contains either the word reply or the word response somewhere within the paragraph. Additionally I would like to be able to fill in columns E and F in sheet "feedback" once the data has been copied.

Thank you in advance for your help!
hrhr

Hi all, I Cant seem to figure out why my Macro will successfully copy and paste everything from one sheet to another sheet -except for the two CommandButtons. If I manually copy and paste while in design mode, everything copies fine, but I dont know how to activate the design mode from code..If thats indeed the problem. Any Ideas?? Thanks.

Hey guys,

I'm looking for a method (I'm assuming it's some type of macro here) to automatically move data from one sheet to another upon entry of data. What I want to happen is when certain conditions are met in an individual cell on the first sheet (we'll call it Sheet A), it'll automatically cut the entire row that cell is contained in from Sheet A and paste itself into a new row on a new sheet (we'll call it Sheet B). From what I've been able to gather from looking around online, it has something to do with VB, but I have absolutely no experience with VB, so I'm at an impass. If anyone has any solutions, please put it in as simple of terms as possible

I am trying to copy values from one sheet to another, to create an effective
small database of information.Thus someone will input values in Sheet1 and a
macro will then copy these to Sheet2.

I have the following code below which I am trying to tweak to do so. I first
wish to copy A18; C18:F18; H18:I18; K18; M18:R21 which are in Sheet1 to
Sheet2 in the columns D;E;F;G and H. My code below will do this except it
post them to A; C; H; K; and M. Secondly and its not in my code below, I
want the output values to start posting in the Row below the last value
entered in Sheet2 - otherwise I will just copy over existing data. And
finally I wish to copy values in E6; E9 and E12 to each of the rows that I
copy. So whatever is in E6; E9; E12 will be copied to the row in Sheet2
where the values relating to A18 etc are.

You will notice in my code that I start my copying on Sheet1 at Row 18 then
skip 5 lines to begin the next row of values to copy i.e. Row 23, but this
row 23 needs to be posted in Row 2 on Sheet2

Hope someone can help

Thanks

Sub Database_Post()

Application.ScreenUpdating = False

With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Sheets("Database").Select
Range("A1").Select

Dim I As Long, j As Long, k As Long, l As Long
Dim rng As Range, cell As Range
With Worksheets("Report")
Set rng = Union(.Range("A18"), .Range("C18:F18"), .Range("H18:I18"),
..Range("K18"), .Range("M18:R21"))

I = 0
j = 0
l = 0
For Each cell In rng
j = cell.Row
k = 1
l = l + 1
Do While Not IsEmpty(.Cells(j, cell.Column))
.Cells(j, cell.Column).Copy
Worksheets("Database") _
.Cells(k, l).PasteSpecial xlValues
k = k + 1
j = j + 5
Loop
Next
End With

Sheets("Database").Select

Columns("A:I").Select
Columns("A:I").EntireColumn.AutoFit
Range("A1").Select

Sheets("Report").Select
Range("A1").Select

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = True

End Sub

Hi,

I am looking for a way to easily extract data from one sheet to another. Is there a way to copy input to a new sheet by using conditions? Like when you have finances it copy's only the rows with the words grocery in it?

Help appreciated.

PS. Excuse me for not using the search engine. I am not quite new to Excel but I don't really know how to describe the problem.


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