Excel vba, find sheet between workbooks with variable condition

[datdanigg]

Hi Guys,

I'm running into an issue here!

I'm not experienced with excel, but I have a soft understanding of it.

Most of the steps are already done (I Think ) but the major issue is finding the sheet between workbook1 and 2 depending on the ComboBox value. Then I want to paste the information on the target sheet.
So my logic is..

.Set the workbook1 and 2 directories
.If the WB2 sheet is = to the WB1 Combobox value, then the found sheet should open. (here is the main issue)
.Paste the data onto the specific range in that sheet
.Save and close WB2 and return to WB1

The code that I already have:

>>> use code - tags <<<

Sub Obras_Despesas()

Dim last_row2 As Long
last_row2 = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
Dim WB1, WB2 As Workbook
Dim Sh2 As Worksheet
' Current Workbwork
Set WB1 = Workbooks.Open("C:\Users\Daniel\Desktop\Gestão\Gestão ScorpionPrestige\Documento oficial de gestão  ScorpionPrestige")
' 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(3).ComboBox3.Value Then GoTo DoThisCode1:

   
Next Sh2

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

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

Hopefully, you guys and give me a hand on it.

 

[Marc L]

Hi,​

​

without any attachment neither a crystal clear technical explanation that's just a guessing challenge but as guessing can't be coding …​

As you are confusing workbook and worksheet so just check where are located those combo boxes and text boxes​

then amend the code accordingly.​

​