Find a specific worksheet and open it

[datdanigg]

Hi guys, I am trying to find a specific woksheet to open it and paste data.

Private Sub CommandButton1_Click()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Despesas")
    Dim last_row As Long
    last_row = Sheets("Despesas").Range("C" & Rows.Count).End(xlUp).Row
'Validation-----------------------------------------------------
If Me.ComboBox1.Value = "" Then
MsgBox "seleciona o Mes", vbCritical
Exit Sub
End If
If Me.ComboBox2.Value = "" Then
MsgBox "seleciona a Designação", vbCritical
Exit Sub
End If
If Me.TextBox1.Value = "" Then
MsgBox "Preenche a Data", vbCritical
Exit Sub
End If
If Me.TextBox5.Value = "" Then
MsgBox "Preenche o Valor", vbCritical
Exit Sub
End If
If Me.TextBox3.Value = "" Then
MsgBox "Preenche com o numero do Documento", vbCritical
Exit Sub
End If
If Me.TextBox4.Value = "" Then
MsgBox "Preenche com a Especificação", vbCritical
Exit Sub
End If
If Me.ComboBox3.Value = "" Then
MsgBox "Preenche com a Obra", vbCritical
Exit Sub
End If
'---------------------------------------------------------------
sh.Range("C" & last_row + 1).Value = Me.ComboBox1.Value
sh.Range("D" & last_row + 1).Value = Me.TextBox1.Value
sh.Range("E" & last_row + 1).Value = Me.TextBox5.Value
sh.Range("F" & last_row + 1).Value = Me.TextBox3.Value
sh.Range("G" & last_row + 1).Value = Me.ComboBox2.Value
sh.Range("H" & last_row + 1).Value = Me.TextBox4.Value
sh.Range("I" & last_row + 1).Value = Me.ComboBox3.Value
'---------------------------------------------------------------

[COLOR=rgb(235, 107, 86)]Workbooks.Open Filename:="C:\Users\Daniel\Desktop\Gestão\Gestão ScorpionPrestige\Obras"
    For Each Worksheet In ActiveWorkbook.Worksheets
        If Worksheets.Name = ComboBox3.Value Then
         Worksheet.Open
         Else
    Next Worksheet
         End If[/COLOR]

'---------------------------------------------------------------
Me.ComboBox1 = ""
Me.TextBox1 = ""
Me.TextBox5 = ""
Me.TextBox3 = ""
Me.ComboBox2 = ""
Me.TextBox4 = ""
Me.ComboBox3 = ""
'----------
  
End Sub

I want to find the worksheet that is = to the combobox value so I can paste the data there, I was trying trought loop and if the loop matches the combobox then the sheet should open and following that I could start coding so data pastes there to.

Sorry for my bad English

 

[bbrnx19]

Welcome

Workbooks.Open Filename:="C:\Users\Daniel\Desktop\Gestão\Gestão ScorpionPrestige\Obras"
For Each Worksheet In ActiveWorkbook.Worksheets
 If Worksheets.Name = ComboBox3.Value Then
Worksheet.Open
Else
Next Worksheet
End If

For ... next and If ... Else ... End if . should be inside one or the other
eg. For ... If ... Else ... End if ... Next or If ... For .... Next ... Else ... For .... Next ... End if
filename requires fullpath & filename & extension
using the set method then enable to access either workbooks objects using WB1 & WB2
see below code

Sub OpenWB()
' Place Code with in Module
Dim WB1, WB2 As Workbook
Dim Sh2 As Worksheet

' Current Workbwork
Set WB1 = ActiveWorkbook
' Opened Worbook fullpath and filename and extension
Set WB2 = Workbooks.Open("C:\Temp\Filename.xlsx")

' Compare WB1 ComboBox3 to WB2 Sheet Name , true jump to DoThisCode
For Each Sh2 In WB2.Worksheets
    Debug.Print Sh2.Name
    ' Method for Combobox in WB1 first worksheet ie index = 1
    If Sh2.Name = WB1.Sheets(1).ComboBox3.Value Then GoTo DoThisCode1:
Next

MsgBox prompt:="No Sheet of that name found in this File"
GoTo Finish1:

DoThisCode1:
    MsgBox prompt:="Sheet Found"
    ' <-- Code here -->
   
   
   
    ' <-- Code here -->
Finish1:
    ' Close WB2 without saving
    WB2.Close SaveChanges:=False
    '
    WB1.Activate
    Set WB1 = Nothing
    Set WB2 = Nothing
End Sub

 

[datdanigg]

I tried to tweak it but It is giving me error on Line 17,
It says object doesn't support this property

Sub Obras_Despesas()

' Place Code with in Module
Dim WB1, WB2 As Workbook
Dim Sh2 As Worksheet
Dim last_row As Long
last_row = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
' Current Workbwork
Set WB1 = ActiveWorkbook
' Opened Worbook fullpath and filename and extension
Set WB2 = Workbooks.Open("C:\Users\Daniel\Desktop\Gestão\Gestão ScorpionPrestige\Obras")

' Compare WB1 ComboBox3 to WB2 Sheet Name , true jump to DoThisCode
For Each Sh2 In WB2.Worksheets
    Debug.Print Sh2.Name
    ' Method for Combobox in WB1 first worksheet ie index = 1
    If Sh2.Name = WB1.Sheets(1).ComboBox3.Value Then GoTo DoThisCode1:
Next

MsgBox prompt:="No Sheet of that name found in this File"
GoTo Finish1:

DoThisCode1:
    MsgBox prompt:="Sheet Found"
'---------------------------------------------------------------
ActiveSheet.Range("C" & last_row + 1).Value = ComboBox1.Value
ActiveSheet.Range("D" & last_row + 1).Value = TextBox1.Value
ActiveSheet.Range("E" & last_row + 1).Value = TextBox5.Value
ActiveSheet.Range("F" & last_row + 1).Value = TextBox3.Value
ActiveSheet.Range("G" & last_row + 1).Value = ComboBox2.Value
ActiveSheet.Range("H" & last_row + 1).Value = TextBox4.Value
ActiveSheet.Range("I" & last_row + 1).Value = ComboBox3.Value
'---------------------------------------------------------------
Finish1:
    ' Close WB2 without saving
    WB2.Close SaveChanges:=False
    '
    WB1.Activate
    Set WB1 = Nothing
    Set WB2 = Nothing
End Sub