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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.
 
Upvote 0
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.
 
Upvote 0
No worries Jaxs. Let us know how it works out.

Cheerio,
vcoolio.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top