Copying data across worksheets

[Maki]

Hello,

I've created a form very similar to the one on Contextures website
(http://www.contextures.com/xlForm02.html).

Only, mine is not as sophisticated as FormSheet.xls since it is a
registration form for a meeting and some fields can be left blank (e.g. there
are three address lines just in case but Address_2 and Address_3 doesn't need
to be filled in). I don't need automatic entering of "Entered (time/date)"
and "Entered By" columns either.

In my Entry Form wks (equivalent of Input wks), the fields are not ligned up
in one column as is the case with Input wks (all in Column D). Also in my
Database wks (equivalent of PartsData wks), there are fields (columns) that
are automatically filled in with VLOOKUP (e.g.
country codes for phone are automatically filled in according to the data in
"Country" field) and therefore a little manuevering is necessary to avoid
copying into cells that are automatically filled in.

How can I do this in an elegant way?

The cells to be copied from Entry Form wks are:
C8,C10,C12,C14,C16,C18,C19,C20,C22,C24,C26,C28,D30,D32,C34,C38,C40,C42,C44,C46,C48,C51
and they would go to columns in Database wks:
B,C,D,E,F,G,H,I,J,K,L,M,O,Q,R,T,U,V,W,X,Y,S

NB: Column A is reserved, N and P are filled automatically in accordance
with the data in M (comes originally from C28 of Entry Form wks) and S is
filled by data in C51.

Many thanks.

 

[Dave Peterson]

You may not need those date/time and entered by columns, but it is one way to
make sure that you can pick out a column that always has data in it. And that's
important so that you can find the nextrow that should get data.

I kept it in this code, but you can delete it and use a different column.

Option Explicit
Sub UpdateLogWorksheet()

Dim HistoryWks As Worksheet
Dim InputWks As Worksheet

Dim NextRow As Long
Dim iCtr As Long

Dim myRng As Range
Dim myCopy As Variant
Dim myCols As Variant

'cells to copy from Input sheet - some contain formulas
myCopy = Array("C8", "C10", "C12", "C14", "C16", _
"C18", "C19", "C20", "C22", "C24", _
"C26", "C28", "D30", "D32", "C34", _
"C38", "C40", "C42", "C44", "C46", _
"C48", "C51")
myCols = Array("B", "C", "D", "E", "F", "G", "H", _
"I", "J", "K", "L", "M", "O", "Q", _
"R", "T", "U", "V", "W", "X", "Y", _
"S")

If UBound(myCols) <> UBound(myCopy) Then
MsgBox "Design error!"
Exit Sub
End If

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

With HistoryWks
'how do you find the next row, I used column A.
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
'make sure something is in it!
'I added the date/time
With .Cells(NextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
For iCtr = LBound(myCopy) To UBound(myCopy)
HistoryWks.Cells(NextRow, myCols(iCtr)).Value _
= InputWks.Range(myCopy(iCtr)).Value
Next iCtr
End With

'clear input cells that contain constants
With InputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With

End Sub

 

[Maki]

Thank you, Dave. It works!