Automatically exporting data to a second sheet

Hi again all,

there seem to be plenty of posts regarding this topic around here, but
I can't find exactly what I need, if its possible at all.

What we have is a template that gets filled out each time, then not
normally saved afterward though we could probably change that part of
the routine. What I need, is some information like client's names etc
to be saved in a second sheet after the template has been used.
Normally that template is used over and over again and each time
cleared, then the information manually written out again in the second
report sheet.

Is there a possible way that after the template has been used, to get
the second sheet to take a record of this information, the template
cleared again and that information still kept in the report?

Thanks for any help

Bob,

A simple macro can be used. For example, if you have a worksheet named Record, and one named
Template.....

Sub RecordAndClear()
Dim myRow As Long

'Find the row to store the data
myRow = Worksheets("Record").Cells(65536,1).End(xlUp).Row + 1

'Get data for column 1
Worksheets("Record").Cells(myRow,1).Value = Worksheets("Template").Range("B3").Value
Worksheets("Template").Range("B3").ClearContents

'Get data for column 2
Worksheets("Record").Cells(myRow,2).Value = Worksheets("Template").Range("H4").Value
Worksheets("Template").Range("H4").ClearContents

'Get data for column 3
Worksheets("Record").Cells(myRow,3).Value = Worksheets("Template").Range("G7").Value
Worksheets("Template").Range("G7").ClearContents

'Get data for column 4......
'And so on for other cells.....

End Sub

I hope you can see the pattern....

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
> Hi again all,
>
> there seem to be plenty of posts regarding this topic around here, but
> I can't find exactly what I need, if its possible at all.
>
> What we have is a template that gets filled out each time, then not
> normally saved afterward though we could probably change that part of
> the routine. What I need, is some information like client's names etc
> to be saved in a second sheet after the template has been used.
> Normally that template is used over and over again and each time
> cleared, then the information manually written out again in the second
> report sheet.
>
> Is there a possible way that after the template has been used, to get
> the second sheet to take a record of this information, the template
> cleared again and that information still kept in the report?
>
> Thanks for any help
>