CODE to select range based on User Input or Value of Input Field

[Sandi Gauthier]

ok...first things first..i'm new to all of this...
i need help!...looking for some code that would export certain info to a new
worksheet, based on USER INPUT...

my db is setup as so - i have a SOURCE WORKBOOK and a SUMMARY WORKBOOK...

SOURCE WORKBOOK
week / sales pro / revenue req'd / revenue achieved / account name /
product ....
1 j. doe 1000.00 2000.00 abc
company widgets
2 j. doe 2000.00 1000.00 xyz
company hammers
3
4
5
to 13

i've NAMED each ROW to reflect the week (ie...Week1, Week2, Week3, Week4,
Week5, Week6, etc...to Week13)

SUMMARY WORKBOOK
i have some code (that i'm going to use from a previous project i worked on)
that enables the user, at the click of a button to EXPORT the data range
that "I" defined into a new workbook...

now, however, i want the user to be able to export info for the WEEK
SPECIFIED BY THEM...for example export info for Week1 only onto new
workbook...

not quite sure how to go about this...

i've already NAMED the ranges so i was thinking perhaps of adding a "prompt"
that would ask the user "Which Week Do You Want to Export?"...then perhaps
have a dropdown list (containing the names of my predefined weekly ranges
ie..Week1, Week2, etc..)

then i'd have to write code to say something like: if answer is Week1 then
Range(Week1).Select, if Week2 then Range(Week2).Select

i THINK i'm on the right track?

appreciate your assistance!
sandi

 

[Dick Kusleika]

Sandi

You have some options. You can use an inputbox to get the week number like
this

Dim WeekNo As Variant

Do
WeekNo = Application.InputBox _
("Enter week number", , , , , , , 1)
If WeekNo = "False" Then
Exit Do
End If
Loop Until WeekNo >= 1 And WeekNo <= 13

If WeekNo <> "False" Then
Range("Week" & WeekNo).Copy _
Workbooks("Summary.xls").Sheets(1).Range("a1")
End If

This will loop until they enter a value from 1 to 13.

You can also use a userform with a listbox that lists all the weeks for the
user to select. Let me know if that's a better option for you and I'll walk
you through how to set it up.

 

[Sandi Gauthier]

Dick, thanks so much...could i bother u to walk me thru (explain) the
code?
if i understand it, this takes me to the point where the range is
selected, and copied?
now all i have to do is continue w/ my code to "paste" to the new
workbook..
re: the input box...i suppose i have to create an "input" box in the
source workbook or will the user be prompted to enter this in once the
macro runs?

would u mind terribly walking me thru how to set this up using a
dropdown list as u mentioned for option 2...

sorry for all the questions!
sandi

 

[Dick Kusleika]

Sandi

Here's what this code does

'Set up a variable to hold the users entry
Dim WeekNo As Variant

'Start a loop
Do

'Application.InputBox creates an input box at run time where the
'user will enter a week number between 1 and 13. Check help for
'the arguments to the InputBox method.
WeekNo = Application.InputBox _
("Enter week number", , , , , , , 1)

'If the user selects Cancel on the input box, this stops the loop
If WeekNo = "False" Then
Exit Do
End If

'the loop keeps going until the user selects a proper week number
Loop Until WeekNo >= 1 And WeekNo <= 13

'If the user doesn't click cancel
If WeekNo <> "False" Then

'Copy the range that's called Week? and paste to a Summary
'workbook on the first sheet cell A1
Range("Week" & WeekNo).Copy _
Workbooks("Summary.xls").Sheets(1).Range("a1")
End If

If you want to use a userform, the advantage would be that you provide a
list of week numbers from which the user would select. I don't think that's
much advantage because you can use the Prompt argument of the InputBox
method to explain to the user the acceptable values. But here's how you
would do it.

In the VBE, select your workbook and choose UserForm from the Insert menu.
From the control toolbox drag a listbox and a command button on to the
userform. Double click the userform to open the code pane and from the drop
down boxes at the top, select UserForm and Initialize. The Initialize event
runs when the userform is shown and might look like this

Private Sub UserForm_Initialize()

Dim i As Long

'Populate the listbox with Week1, Week2, etc.
For i = 1 To 13
Me.ListBox1.AddItem "Week" & i
Next i

End Sub

Then select CommandButton1 and Click from the drop downs to create the Click
event of the CommandButton. This will fire when the commandbutton is
clicked and might look like this

Private Sub CommandButton1_Click()

'Make sure something is selected
If Me.ListBox1.ListIndex <> -1 Then

'copy the range named Week? to the summary workbook
ThisWorkbook.Sheets(1).Range(Me.ListBox1.Value).Copy _
Workbooks("Summary.xls").Sheets(1).Range("a1")

'Close the userform
Unload Me
Else

'If nothing selected, display a message
MsgBox "Select a week from the listbox"
End If

End Sub

Finally, you'll need a sub in a standard module to show the userform

Sub ShowForm()
UserForm1.Show
End Sub

Let me know if you need more clarification.

 

[Sandi Gauthier]

Thank you so much for taking the time to explain the various functions
each line represents...!
Sounds like i have what i need!
Sandi