Moving last info in column of worksheet to another in same workboo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi -
I am sure this is easy, but I am not savvy with macros and programming in
excel to figure this one out.
What I need to do is to take the last date entered in a column of one
worksheet and "copy" it to into another worksheet of the same workbook. I
also need to do that with the last amount (currency formatted) of a column.
There are blank cells in the column depending on weather or not there is
information entered in that particular cell or not.

Any help would be appreciated. Thanks
 
The following macro will copy the last entry in column B of Sheet1 to cell A1
in Sheet2:

Sub moveitover()
Sheets("Sheet1").Activate
n = Cells(Rows.Count, "B").End(xlUp).Row
Cells(n, "B").Copy Sheets("Sheet2").Range("A1")
End Sub

you can get a similar result without VBA if you are willing to use a linking
formula rather than copy/paste.
 
Thanks! I didn't make myself clear in the 1st question. I have over 60
sheets in this workbook. Each sheet is a client. The 1st sheet in the
workbook is a listing of all the clients with their name and balance owed,
date payment due, and date of last payment and amount of last payment. date
of last payment and amount of last payment is what I want copied from the
individual clients sheet (I have a column for that in their sheet). I have
figured out how to make a formula to bring the balance due to the 1st sheet,
so now all I have to do is have it copy the last payment amount and date over
for each customer so I only have to enter it once. And that is the last item
entered in that column The 1st sheet is kind of like the report sheet for my
boss.

Thanks
 
Thanks. However, after re-reading my question I realize that I did not make
myself clear.
I have a workbook with about 100 sheets. The 1st one being what I will call
the balance owed sheet. The other sheets are the individual customer sheets.
With a date, invoice #, check #, inv. Amount, payment amt, and balance.
The 1st sheet list each client the balance owed, date payment due (never
changes), the date of last payment, last payment amount.
What I want to do is move the DATE of last payment (which is in the date
column of the client sheet) to the 1st sheet on the clients row in the column
for date of last payment. I also want to move the Payment Amount from the
clients sheet to the 1st sheet on the clients rose in the column for last
payment amount. I have already figured out how to move the balance (via a
formula). Can this be done with the same formula you gave me or is there
something else I have to do?

Thanks
 
Hi

The following code assumes your 1st sheet is called Summary.
It clears all data from row 2 to 200, then loops through each sheet in turn,
extracting the Client name (from the sheet name), the Date of last payment,
the value of last payment and the current balance (including any invoices
added after the date of last payment.
It writes this information to successive rows in columns A:D of Summary
sheet.

Try this on a COPY of your data first, as it will wipe out any formulae that
you have already set up on your Summary sheet.

Dim wsd As Worksheet, ws As Worksheet
Dim lr As Long, i As Long

Application.ScreenUpdating = False
Set wsd = Sheets("Summary")
wsd.Rows("2:200").EntireRow.Delete
i = 2
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Activate
lr = Cells(Rows.Count, "E").End(xlUp).Row
wsd.Cells(i, "A") = ActiveSheet.Name
wsd.Cells(i, "B") = ActiveSheet.Cells(lr, "A").Value
wsd.Cells(i, "C") = ActiveSheet.Cells(lr, "E").Value
lr = Cells(Rows.Count, "F").End(xlUp).Row
wsd.Cells(i, "D") = ActiveSheet.Cells(lr, "F").Value
i = i + 1
End If
Next
Application.ScreenUpdating = True
Sheets("Summary").Activate
End Sub

To copy the macro into your workbook, press Alt+F11 to bring up the Visual
Basic Editor.
Choose Insert>Module>and copy the code into the white pane that appears.
Press Alt+F11 to return to your Spreadsheet.
To run the macro, press Alt+F8, highlight the macro name and choose Run

I have used letters for the columns rather than numbers, so you can easi;y
see which values to alter if you want the information placed in different
positions on your Summary sheet.
 
Sorry, copying the code I missed the first line

It should be

Sub CopyDatafromSheets()

Dim wsd As Worksheet, ws As Worksheet
Dim lr As Long, i As Long

Application.ScreenUpdating = False
Set wsd = Sheets("Summary")

wsd.Rows("2:200").EntireRow.Delete

i = 2
For Each ws In Worksheets
If ws.Name <> "Summary" Then
ws.Activate
lr = Cells(Rows.Count, "E").End(xlUp).Row
wsd.Cells(i, 1) = ActiveSheet.Name
wsd.Cells(i, 2) = ActiveSheet.Cells(lr, 1).Value
wsd.Cells(i, 3) = ActiveSheet.Cells(lr, 5).Value
lr = Cells(Rows.Count, "F").End(xlUp).Row
wsd.Cells(i, 4) = ActiveSheet.Cells(lr, 6).Value
i = i + 1
End If
Next

Application.ScreenUpdating = True
Sheets("Summary").Activate
End Sub
 
Thanks so very much! This worked and will save me hours upon hours to time.
It is much appreciated. Happy Holiday Season!
 
Back
Top