VBA Code for selecting specific sheet names within specific workbook. 1

[eit09]

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("[highlight #FCE94F]W-shape[/highlight]").Range("D1318")
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("[highlight #FCE94F]W-shape[/highlight]").Range("G47").Value
rngTrial(1, 8).Value = Sheets("[highlight #FCE94F]W-shape[/highlight]").Range("G48").Value
' move to the next line
Set rngTrial = rngTrial.Offset(1)
Loop
End Sub

 

[vcolella]

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

Sub Calc_All_Trials()

[indent]
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")
[indent]
Do Until Len(rngTrial(1)) = 0
' copy loads from sheet Loads to sheet Calculation
For i = 1 To 6
[indent]rngLoads(i).Value = rngTrial(1, i).Value[/indent]
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)
[/indent]
Loop
[/indent]
End Sub

 

[eit09]

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?

 

[SkipVought]

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

Sub Calc_All_Trials()


Dim ws_name As String
ws_name = [[b]SelectedSheet[/b]]
'
' 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Agent666]

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