+ Reply to Thread
Results 1 to 3 of 3

Form to Answer workbooks problem.

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    3

    Post Form to Answer workbooks problem.

    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
    Last edited by DavidLinder; 06-19-2013 at 05:24 PM.

  2. #2
    Registered User
    Join Date
    06-19-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Form to Answer workbooks problem.

    sorry the "'Rows(ActiveCell.Row).Select" is not supposed to be a comment

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Form to Answer workbooks problem.

    Update:
    Changed it to this, but gives me a 1004 error
    Sub MoveData()

    'Select Sheet
    Workbooks("EICCGeSIDDtemplate.xlsm").Activate
    Sheets("Smelter List").Activate

    Dim sourcerange As Range

    Range("B5").Select

    'Active cell is in the supplier’s form
    Do Until ActiveCell = ""

    '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("B3").Select

    'Paste

    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False


    'Select Sheet
    Workbooks("EICCGeSIDDtemplate.xlsm").Activate
    Sheets("Smelter List").Activate

    'Come back to active cell in smelter list
    sourcerange.Select

    'Move down one step
    ActiveCell.Offset(1, 0).Range("A1").Select

    Loop

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1