×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Code for selecting specific sheet names within specific workbook.

VBA Code for selecting specific sheet names within specific workbook.

VBA Code for selecting specific sheet names within specific workbook.

(OP)
I would like to modify the following code so I don’t need to step into and edit the sheet names every time I use it. Currently I step into the vba and change the sheet name to one of the 10 sheet names within the workbook(W-shape in 3 places as shown highlighted below for this example). Is there a code to have a dialogue box pop up and ask which sheet name to replace the 3 sheet names highlighted below in yellow? I did attach the file for clarity.

Sub Calc_All_Trials()
'
' Dim rngTrial As Range
Dim rngLoads As Range
Dim i As Integer
Set rngTrial = Sheets("Loads").Range("E3:L3")
Set rngLoads = Sheets("W-shape").Range("D13:D18")
Do Until Len(rngTrial(1)) = 0
' copy loads from sheet Loads to sheet Calculation
For i = 1 To 6
rngLoads(i).Value = rngTrial(1, i).Value
Next i
' copy results from Calculations to Loads
rngTrial(1, 7).Value = Sheets("W-shape").Range("G47").Value
rngTrial(1, 8).Value = Sheets("W-shape").Range("G48").Value
' move to the next line
Set rngTrial = rngTrial.Offset(1)
Loop
End Sub

RE: VBA Code for selecting specific sheet names within specific workbook.

Sure, just use an Input Box. I've added it to the beginning of your code:

CODE

Sub Calc_All_Trials()

Dim ws_name As String ws_name = InputBox("Type the worksheet's name: ") ' ' Dim rngTrial As Range Dim rngLoads As Range Dim i As Integer Set rngTrial = Sheets("Loads").Range("E3:L3") Set rngLoads = Sheets(ws_name).Range("D13:D18")
Do Until Len(rngTrial(1)) = 0 ' copy loads from sheet Loads to sheet Calculation For i = 1 To 6
rngLoads(i).Value = rngTrial(1, i).Value
Next i ' copy results from Calculations to Loads rngTrial(1, 7).Value = Sheets(ws_name).Range("G47").Value rngTrial(1, 8).Value = Sheets(ws_name).Range("G48").Value ' move to the next line Set rngTrial = rngTrial.Offset(1)
Loop
End Sub

RE: VBA Code for selecting specific sheet names within specific workbook.

(OP)
vcolella,

This works well thank you. Do you know if your able to also include a drop down list within the Input box versus typing? Within Cell C1 in the Loads worksheet I have a drop down list of all active worksheets and would like to try to take the input box one step further?

RE: VBA Code for selecting specific sheet names within specific workbook.

Hi,

On a separate sheet make a list of sheet names. Name the list range. My name would be SheetList.

In C1 in Loads, Data > Data Validation > List... and enter =SheetList

Name the cell C1 SelectedSheet

In your code...

CODE

Sub Calc_All_Trials()


Dim ws_name As String
ws_name = [SelectedSheet]
'
' Dim rngTrial As Range
   Dim rngLoads As Range
   Dim i As Integer
   Set rngTrial = Sheets("Loads").Range("E3:L3")
   Set rngLoads = Sheets(ws_name).Range("D13:D18")

   Do Until Len(rngTrial(1)) = 0
' copy loads from sheet Loads to sheet Calculation
      For i = 1 To 6
         rngLoads(i).Value = rngTrial(1, i).Value
      Next i 
' copy results from Calculations to Loads
      rngTrial(1, 7).Value = Sheets(ws_name).Range("G47").Value
      rngTrial(1, 8).Value = Sheets(ws_name).Range("G48").Value 
' move to the next line
      Set rngTrial = rngTrial.Offset(1)
   Loop
End Sub 

You could make it so that when you select a new sheet name in the data validation drop down, your Calc_All_Trials procedure will run.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VBA Code for selecting specific sheet names within specific workbook.

eit09, to add a dropdown list into the input box itself, you'll need to create a form with a ComboBox instead of using the input box as thats limited to you typing in text or pre-populating it with a default.

check here as it goes through the procedure, unfortunately once you are into forms, you need to program all of the behaviours. But it's pretty simple and this guide seems to go through all the examples of code you'll need.

https://www.contextures.com/xlUserForm02.html




Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close