Transfer and save data from one worksheet to another.

A

arepemko

I have a master worksheet (sheet1) that I use for several companies. What I
need to do is when I enter data into the sheet1 it will automatically be
transfered into sheet2, but i need it to save the data so that I can clear
sheet1 and still have all the data on sheet2.
 
D

Dave Peterson

Saved from a previous post:

You'll have to change your addresses and sheet names...

Option Explicit
Sub testme01()

Dim HistoryWks As Worksheet
Dim InputWks As Worksheet

Dim NextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myAddresses As String
Dim myCell As Range

myAddresses = "A1,F9,A2,B1"

Set InputWks = Worksheets("Input")
Set HistoryWks = Worksheets("Summary")

With InputWks
Set myRng = .Range(myAddresses)
End With

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If

With HistoryWks
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
With .Cells(NextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(NextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
.Cells(NextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clean up data in form??
myRng.ClearContents

End Sub


I check to see if all the cells have something in them (maybe not required???).

I also add the date/time to column A of the summary sheet and the username
(taken from Tools|options|General tab) to column B.

Then Column C to xxxx go in the same order as the addresses you've specified in
this line:

myAddresses = "A1,F9,A2,B1"

(Change that to match your input worksheet.

(myRng.clearcontents may not be necessary, too.)


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
A

arepemko via OfficeKB.com

Thank you very much.

Dave said:
Saved from a previous post:

You'll have to change your addresses and sheet names...

Option Explicit
Sub testme01()

Dim HistoryWks As Worksheet
Dim InputWks As Worksheet

Dim NextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myAddresses As String
Dim myCell As Range

myAddresses = "A1,F9,A2,B1"

Set InputWks = Worksheets("Input")
Set HistoryWks = Worksheets("Summary")

With InputWks
Set myRng = .Range(myAddresses)
End With

If Application.CountA(myRng) <> myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If

With HistoryWks
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
With .Cells(NextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(NextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
.Cells(NextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End With

'clean up data in form??
myRng.ClearContents

End Sub

I check to see if all the cells have something in them (maybe not required???).

I also add the date/time to column A of the summary sheet and the username
(taken from Tools|options|General tab) to column B.

Then Column C to xxxx go in the same order as the addresses you've specified in
this line:

myAddresses = "A1,F9,A2,B1"

(Change that to match your input worksheet.

(myRng.clearcontents may not be necessary, too.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Click to expand...
 
Top