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
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
Talk To Other Members
RE: VBA Code for selecting specific sheet names within specific workbook.
CODE
RE: VBA Code for selecting specific sheet names within specific workbook.
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.
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 SubYou 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,
Just traded in my OLD subtlety...
for a NUance!
RE: VBA Code for selecting specific sheet names within specific workbook.
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