I have created a from which will be sent out to various people, and I am trying to create a macro which I can simply press to copy all the data from the various forms into a main sheet, with the same columns as the surveys and where the data from the various people will be on different rows. I created the "only filled cells" loop as each person will have more than one row of data but it will vary how many.
The code does not create any errors, but instead of copying the data from one form to the main sheet, it just changes the selected cell in the open form (which is called EICCGeSIDDtemplate.xlsm by the way)
Any help would be much appreciated! My goal is basically to achieve something like Google form.
Sub MoveData()
Workbooks("EICCGeSIDDtemplate.xlsm").Activate
Dim sourcerange As Range
ActiveCell.Range("B5").Select
'Active cell is in the supplier’s form
Do While IsEmpty(Active) = False
'Select Row, then copy
Rows(ActiveCell.Row).Select
Selection.Copy
'Remember active cell
Set sourcerange = ActiveCell
'Paste the row into the database file. Insert statement?
Workbooks("Destination.xlsm").Activate
Range("A3").Select
'Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Workbooks("EICCGeSIDDtemplate.xlsm").Activate
'Come back to active cell in smelter list
sourcerange.Select
'Move down one step
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Bookmarks