Copy Specific Cell Content from SheetA to SheetB

[Knewell]

Hi,

On a regular basis I am creating a PO in an excel based template. I want to track this created PO's onto the next sheet.
The data's that I wanted to copy from the template sheet to the tracking sheet resides in specific cells (as this specific cells gets change every time I create a new PO).
What I am doing right now is time consuming, as I copy and paste and switch between sheets every now and then. I know this can be done programatically but I just do not know how. I hope this forum can help me.

The cells where are the data resides are as follows:

In PO Template
Cell Description
C3 = Country
C4 = Client Name
C5 = Date Order Received
C6 = PO Validity
C7 = PO Number
C12 = Product Description

At the Tracking Sheet

Cell Description
B2 = PO Template C3 cell content
C2 = PO Template C4 cell content
D2 = PO Template C5 cell content
E2 = PO Template C6 cell content
F2 = PO Template C7 cell content
G2 = PO Template C12 cell content
H2 = Date Created/Submitted for Approval ( normally Todays Date)
I2 = Status ( Pending for Approval )

Now, I want the tracking file to update simultaneously by row. Meaning, if I create a new PO, the new PO information will be added to the next empty row. Say in row2 PO 12345 information is already there, when I create a new PO, the next PO information should be at Row3 and so forth for the next PO's.

I will say thank you now in advance as I am certain that this is just simple to a lot of you here....

[dilipandey]

Hi Knewell,

Welcome to the forum.

I guess Linking would be an easy and quick fix here.. if you prefer not to delete the rows in PO template...
To link PO to Tracking sheet, just copy the PO range, Go to Tracking sheet -> Paste special -> Paste Link.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Knewell]

Hi Dilip,

Thank you for the quick reply and for welcoming me to the forum.

I did what you suggested, but I think this is not what I want to happen. Because whenever there is a change in the range at the PO template, the data's on the link at the tracking file also changes and is not saved. In this way the tracking sheet is not serving its purpose. Is there a way that whenever I make a new PO at the template, the new PO information gets added to the next empty row below?

Knewell

[Knewell]

I have found this VBA code in this forum that almost does what I want, but the only problem is I do not know how to revise this code to suite my requirement. Can somebody please assist?

Sub UpdateInvoiceSheet()
Dim ws1 As Worksheet: Dim ws2 As Worksheet
Dim rRpt As Range

Set ws1 = Worksheets("Receipts PYMT 2010")
Set ws2 = Worksheets("Invoice Cost Overview 2010")

y = ws2.UsedRange.Rows.Count + 1
For Each rRpt In ws1.Range("E2:E" & ws1.UsedRange.Rows.Count)
If rRpt = 0.7 Or rRpt = 1 Then
ws2.Cells(y, "B") = ws1.Cells(rRpt.Row, "I")
ws2.Cells(y, "C") = ws1.Cells(rRpt.Row, "A")
ws2.Cells(y, "D") = ws1.Cells(rRpt.Row, "G")
y = y + 1
End If
Next rRpt
End Sub

[jraj1106]

Hi

Upload excel file with sample data...

[Knewell]

Hi,

I have attached the file and thanks for guiding me on how to make attachments.

[jraj1106]

There's no data in these sheets... also the sheets are different frm the ones that you have in your specifications.. Pls explain properly with proper file and proper data..

[Knewell]

Hi,

I have re-attached the file. Just need to change some naming but it is the same template. I have also filled the information on the tracking sheet with the information's I want to be transferred to the tracking file. As the PO template is a standard form, every new PO raised gets changed in the template and every new PO raised is the one I want to record in the tracking sheet. I hope I have I am making sense.

[Knewell]

Hi,

just wondering if any luck for me here?

thanks..