Excel 2007 Copy paste data from one wrksheet to another wrksheet

jaxs2009

Board Regular
Joined
Nov 28, 2010
Messages
200
Windows 2007 excel

Worksheet “Project Tracking” - cell AK7 when the cell has the text word “Awarded” I would like the data from row 7 column “C” through column “I” to be copied and pasted to worksheet “BD – Estimating Schedule” find last row and paste from column “C” through Column “I”

In Worksheet “Project Tracking” cell AK8 when the cell text says “Awarded” again find the last row on worksheet “BD – Estimating Schedule” and past. And so on down the AK column.

I want to be able to control this function with each row individually when column AK (whatever row) cell has the text “Awarded” in it.

I tried a command button but could not get it to work.
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hello Jaxs2009,

Try the following code placed into the "Project Tracking" sheet module:-


Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("AK")) Is Nothing Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

Dim ws As Worksheet: Set ws = Sheets("BD - Estimating Schedule")

If Target.Value = "Awarded" Then
      Range(Cells(Target.Row, "C"), Cells(Target.Row, "I")).Copy ws.Range("C" & Rows.Count).End(3)(2)
End If

End Sub


This will allow you to treat each row individually and transfer the data from C:I to the "BD - Estimating Schedule" sheet starting in Column C once "Awarded" is placed in any cell in Column AK of the "Project Tracking" sheet.

To implement this code:-

- Right click on the "Project Tracking" sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.
 

jaxs2009

Board Regular
Joined
Nov 28, 2010
Messages
200
Oh man I am so sorry, I got the two wrksheets reversed the cell AK7 is found in the wrksheet "BD - Estimating" not the wrksheet "Project tracking" that I orignally referenced. I will try tomorrow Sunday to read the code you where so kind to provide me and reverse/switch the sheet reference in the code.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
No worries Jaxs. Let us know how it works out.

Cheerio,
vcoolio.
 

jaxs2009

Board Regular
Joined
Nov 28, 2010
Messages
200
It worked out great, thank you. But the coolest thing is that after studying the code you provided I fully understand it and can see it.
Again thank you.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,001
Hi Jaxs,

You're welcome. First time lucky! I'm glad that I was able to help out.

Yes, the code is pretty simple to understand and follow and its good to know that you are able to comprehend its workings.

Cheerio Jaxs,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,625
Messages
5,523,970
Members
409,548
Latest member
Buildingbysea

This Week's Hot Topics

Top