Userform to Export Certain Data

**RecRic** · 11-30-2007, 03:34 PM

Hi All

Can anyone help?

Im new to userforms but i think this is the best way to approach this?....

I have a spreadsheet which has data dumped into it on a daily basis. The key feature of this spreadsheet is the column containing a week no (1-52)

Because of the amount of data being stored each week What I would like to do is create a user form where the user could select a Week No from a dropdown list, or type it into an input box and then click a command button which would send all the data for the required week into a seperate sheet.

Is this possible? If so could you possibly help point me in the right direction?

**Leith Ross** · 11-30-2007, 03:42 PM

Hello RicRec,

Will the data on the new sheet be in a single column also?

Thanks,
Leith Ross

**RecRic** · 11-30-2007, 03:54 PM

Hi Leith

Well the spreadsheet data is actually formed of 5 columns, i was hoping that the column containing the week no could be used in a way similar to a vlookup if that makes sense?

So by entering or selecting say week number "42" all the data with week no "42" in the relevant column would be sent across to the new sheet.

**royUK** · 11-30-2007, 04:09 PM

Place a combobox and a commandbutton onto a UserForm. Then use this code

Load the ComboBox with week numbers

Private Sub UserForm_Initialize()
    Dim i      As Integer
    For i = 1 To 52
        Me.ComboBox1.AddItem i
    Next i
End Sub

In the commandbutton this code will copy the weekly data, week numbers are in Column A

Private Sub CommandButton1_Click()
    On Error Resume Next
    Dim c      As Range
    Dim myRange As Range
    Dim rng    As Range
    Dim wk     As Integer
    Dim r      As Long

    Set rng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))

    wk = Me.ComboBox1.Value
    For Each c In rng
        If c = wk Then
            If myRange Is Nothing Then
                Set myRange = c
            Else
                Set myRange = Union(myRange, c)
            End If
        End If
    Next c
    myRange.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    On Error GoTo 0
End Sub

**Leith Ross** · 11-30-2007, 04:10 PM

Hello RecRic,

Could you post your workbook so I can better understand what data is where?

Thanks,
Leith Ross

**RecRic** · 11-30-2007, 06:01 PM

Thanks for that Roy

I will post the spreadsheet in the morning Leith as its on my other laptop at the moment

Roy i will let you know how i get on!

Thanks guys

**RecRic** · 12-01-2007, 10:32 AM

Hi Rob/Leith

Rob that code you provided worked a treat, the week number wasnt in Column A but i managed to change the code easily.

Just one quick follow up, if you dont mind, if i wanted have a list in the combobox of say names instead of a number range what would the code look like

i.e

For i = 1 To 52 (As it currently is) would change to something like For i = North,South,East,West ??

Thanks again chaps

Userform to Export Certain Data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Userform to Export Certain Data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions