Excel vba, find sheet between workbooks with variable condition

datdanigg

New member
Joined
Jan 18, 2022
Messages
1
Reaction score
0
Points
0
Excel Version(s)
2019
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 :confused: ) 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:
HTML:
Sub Obras_Despesas()

Dim last_row2 As Longlast_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.
 
Last edited:
Back
Top