+ Reply to Thread
Results 1 to 2 of 2

Scan range of rows and export to 2nd worksheet

  1. #1
    Adam
    Guest

    Scan range of rows and export to 2nd worksheet

    I created a spreadsheet as an entry form. There are 56 rows and 10 columns in
    each row.

    I want to create a command button that will scan each row and gather the
    data, and then it will copy it into another workshet. After completion, it
    will clear the first form, but it will not eliminate the formulas in the
    fields.

    Is this possible??

    Thanks!!

  2. #2
    GaryDK
    Guest

    Re: Scan range of rows and export to 2nd worksheet

    Hi Adam,

    This ought to get you started. Put this code into a module and assign
    it to the command button on your data entry sheet.

    Sub CopyRows()
    Dim lentryRow As Long
    Dim ldestRow As Long

    ' assumes data starts in column A for each row
    ' find last row with data in the active sheet
    lentryRow = Cells(Rows.Count, 1).End(xlUp).Row
    ' assumes data starts in row 2
    Range(Cells(2, 1), Cells(lentryRow, 10)).Copy

    ' find the first empty row on the destination sheet
    ldestRow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row +
    1
    ' append new data
    Worksheets("Sheet2").Cells(ldestRow, 1).PasteSpecial _
    Paste:=xlPasteValuesAndNumberFormats
    ' assumes that last 5 cells in data entry form have formulas
    ' so just clear the actual entry cells
    Range(Cells(2, 1), Cells(lentryRow, 5)).ClearContents
    End Sub

    I hope this helps,

    Gary


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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