I have two work books.

One workbook has 3 sheets, each a different company name.

The 2nd workbook is one sheet only. I have it set up so that the macro
selects a cell (example J5)

This is where I need the help..
The next step I want is to go back to the first workbook, select a specific
sheet, and then lookup the value in the last cell completed in row A and put
it in cell J5 from the 2nd workbook/sheet1.



--
Thanks for your help!

-Danielle

For demo purposes only. Data will overwrite in WBw.Sh1."J5"
Should give you the general idea.

Sub cpynpst()
lr1 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'last Row Col A
lr2 = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row 'for each sheet
lr2 = Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Row
With Workbooks(1)
.Worksheets(1).Range("A" & lr1).Copy Workbooks(2).Sheets(1).Range("J5")
.Worksheets(2).Range("A" & lr2).Copy Workbooks(2).Sheets(1).Range("J5")
.Worksheets(3).Range("A" & lr3).Copy Workbooks(2).Sheets(1).Range("J5")
End With
End Sub

"DanielleD" wrote:

I have two work books.

One workbook has 3 sheets, each a different company name.

The 2nd workbook is one sheet only. I have it set up so that the macro
selects a cell (example J5)

This is where I need the help..
The next step I want is to go back to the first workbook, select a specific
sheet, and then lookup the value in the last cell completed in row A and put
it in cell J5 from the 2nd workbook/sheet1.



--
Thanks for your help!

-Danielle

This corrects typo:

Sub cpynpst()
lr1 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'last Row Col A
lr2 = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row 'for each sheet
lr3 = Worksheets(3).Cells(Rows.Count, 1).End(xlUp).Row
With Workbooks(1)
.Worksheets(1).Range("A" & lr1).Copy Workbooks(2).Sheets(1).Range("J5")
.Worksheets(2).Range("A" & lr2).Copy Workbooks(2).Sheets(1).Range("J5")
.Worksheets(3).Range("A" & lr3).Copy Workbooks(2).Sheets(1).Range("J5")
End With
End Sub

Also, you should use the names for workbooks(1) and (2)
and have both workbooks open when you run the code.
You should end up with the value of the last cell in sheet3
of WB(1) in WB(2).Sh2.Rng("J5") with this demo code.
You can modify it to apply to different sheets, cells etc.


"DanielleD" wrote:

I have two work books.

One workbook has 3 sheets, each a different company name.

The 2nd workbook is one sheet only. I have it set up so that the macro
selects a cell (example J5)

This is where I need the help..
The next step I want is to go back to the first workbook, select a specific
sheet, and then lookup the value in the last cell completed in row A and put
it in cell J5 from the 2nd workbook/sheet1.



--
Thanks for your help!

-Danielle