Results 1 to 1 of 1

Thread: Excel vba, find sheet between workbooks with variable condition

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Neophyte datdanigg's Avatar
    Join Date
    Jan 2022
    Excel Version

    Excel vba, find sheet between workbooks with variable condition

    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:
    HTML Code:
    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
    ' Close WB2 saving    
    WB2.Close SaveChanges:=True    '    
    Set WB1 = Nothing    
    Set WB2 = Nothing    
    End Sub
    Hopefully, you guys and give me a hand on it.
    Last edited by datdanigg; 2022-01-18 at 01:18 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts