Copy/Paste loop

[PRINCE21]

I have a form on one sheet in excel, on the next sheet i have mad
different coloums with different headings. I want the data from th
form to be copyed to each heading e.g. name in form is copyed to unde
the coloum with title name.

So each time a user fills in there details he/she clicks submit an
there data is copyed to the next page, like a small database.

Is there a macro that can do this as i keep getting different error
each time. I want this to continue in a loop

Please help!!:confused

 

[Dave Peterson]

This might get you started:

Option Explicit
Option Base 0
Sub testme01()

Dim historyWks As Worksheet
Dim curWks As Worksheet
Dim destRow As Long
Dim iCtr As Long

Dim myAddresses As Variant

myAddresses = Array("A1", "B1", "D1", "F1", "H1")

Set curWks = Worksheets("Input")
Set historyWks = Worksheets("HistoryLog")

With historyWks
destRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With curWks
For iCtr = LBound(myAddresses) To UBound(myAddresses)
historyWks.Cells(destRow, 1 + iCtr).Value _
= .Range(myAddresses(iCtr)).Value
.Range(myAddresses(iCtr)).ClearContents
Next iCtr
End With

End Sub


Change the worksheet names and fix the addresses on the Input sheet.

 

[PRINCE21]

The information did not help, all you posted me is some code, that i d
not understand. ANyone else can you help me

 

[Dave Peterson]

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

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there. But change the cell addresses to save and the names of
the worksheets.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (testme01--rename it to something nicer)
and then click run.