Copy between worksheets

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

Guest

Here's the situation, my excel amigos.
I have a sales reporting workbook. Each client has their own sheet, linked to a Master Sheet. Within each individual client sheet, there is a range of cells, 3 columns, app. 15 rows, for Date, Type, and Action. I would like data entered here in each sheet to also copy to an action report. So for example if on 12-12-03, I phone a client, I would like the info from these cells to "post", if you will, to the next available row on the action report sheet. That data can then be sorted by date. Any suggestions?
 
Tom,

This is actually a question for the programming newsgroup,
I don't see how it could be accomplished by means of
worksheet functions. Sorry to disappoint you, if this was
wishful thinking! Cheer up, though, it's fairly easy
through a few lines of code... The code should look
something like this:

Sub copy_last_entry()
sht = ActiveSheet.Name
ActiveCell.Offset(0, -ActiveCell.Column + 1).Select
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
Selection.Copy
Sheets("Master").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending,
Header:=xlYes
ActiveCell.Select
Sheets(sht).Select
Application.CutCopyMode = False
ActiveCell.Select
End Sub

You will need to change "Master" above to the name of your
Master sheet, if different. Other than that, it will copy
the current row entry from any sheet.
Assumption: date in column B (otherwise change the "B2"
part of the sort statement accordingly).
To run the macro, I would think the most convenient way is
to add a custom button to your toolbar, as I understand
this is something you do quite often... alternatively, you
could add a button on each sheet of the workbook, but that
becomes tedious rather fast as the number of sheets
increases, plus it blows up the size of the workbook
unnecessarily. If you want a more "pro" solution, you
might create a custom toolbal for it and add a couple of
lines to the on open event of the workbook to load it each
time it opens.

HTH,
Nikos
-----Original Message-----
Here's the situation, my excel amigos.
I have a sales reporting workbook. Each client has their
Click to expand...
own sheet, linked to a Master Sheet. Within each
individual client sheet, there is a range of cells, 3
columns, app. 15 rows, for Date, Type, and Action. I
would like data entered here in each sheet to also copy to
an action report. So for example if on 12-12-03, I phone
a client, I would like the info from these cells
to "post", if you will, to the next available row on the
action report sheet. That data can then be sorted by
date. Any suggestions?
 
Back
Top