Userform to Export Certain Data

**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

**royUK** · 12-01-2007, 10:57 AM

You really should start a new Thread, but to load names like that you would use an array

Me.ComboBox1.List=Array("North","South","East","West")

**RecRic** · 12-01-2007, 02:56 PM

Thanks Very Much Rob

Have a good xmas :-)

Userform to Export Certain Data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions