Copy/Paste/Rename values between two workbooks if certain conditions are met

[PlutoX]

Hi guys,

first time poster here

I would need some help adjusting the value range of my VBA code.

My User Form looks like this: https://i.stack.imgur.com/eSUkP.png

I have a listbox with workbooks (wb1, wb2, wb3...) and a workbook template(wb_template).
I need to copy/paste values from wb1 to wb_template if certain conditions are met.


As you can see in the code below, the value range is fixed. What I would need is defining conditions, which should be met before initialising the copy/paste procedure.

So far I think that the Range.Find and Range.Offset Methods are the way to go, but I'm not sure how to achieve this, as I'm pretty new to the VBA coding...

I would need to define the following conditions:

• Condition 1: look for and copy certain keyword in wb1 and paste/rename it in wb_template(specific range - sheet/cell). Example: https://i.stack.imgur.com/bdv7d.png
• Condition 2: look for certain keyword in wb1, copy the value of the adjoining cell and paste it in wb_template(specific range - sheet/cell). Example: https://i.stack.imgur.com/JVpFZ.png

The loop should work like this:

1. Check sheet1 of wb1 according to the conditions
2. If conditions are met - transfer the values from wb1 to wb_template
3. Save wb_template as new workbook
4. Repeat procedure for sheet2, sheet3, sheet4 and so on...
5. Then repeat process for the next workbook in the listbox

So, in the end I should have new workbooks for every sheet of the workbooks(wb1, wb2, wb3...) of the listbox. Overview: https://i.stack.imgur.com/mmeWZ.png
I have everything set up so far - my code as of now loops through every sheet of my workbook and copy/pastes data according to a fixed range. The only thing I need are those conditions instead of the fixed range.


The VBA code so far:


Transfer-Button

PublicSub TransferFile(TemplateFile AsString, SourceFile AsString)
    Dim wbSource As Workbook
    Set wbSource = Workbooks.Open(SourceFile)'open source

    Dim wbTemplate As Workbook
    Dim NewWbName AsString

    Dim wsSource As Worksheet
    ForEach wsSource In wbSource.Worksheets 'loop through all worksheets in source workbook
        Set wbTemplate = Workbooks.Open(TemplateFile)'open new template

        '/* Definition of the value range */
        With wbTemplate.Worksheets("Sheet1")
            .Range("A2").Value = wsSource.Range("A2").Value
            .Range("A3").Value = wsSource.Range("A3").Value
            .Range("B2").Value = wsSource.Range("B2").Value
            .Range("B3").Value = wsSource.Range("B3").Value
        EndWith

        NewWbName = Left(wbSource.Name, InStr(wbSource.Name,".")-1)
        wbTemplate.SaveAs wbSource.Path & Application.PathSeparator & NewWbName &"_New.xlsx"
        wbTemplate.Close False'close template
    Next wsSource

    wbSource.Close False'close source
EndSub

Browse Files Button - I guess not relevant for my question

PrivateSub CommandButton1_Click()
    Dim fNames AsVariant

    WithMe
        fNames = Application.GetOpenFilename("Excel File(s) (*.xls*),*.xls*",,,,True)
        If IsArray(fNames)Then.ListBox1.List = fNames
    EndWith
EndSub

​
PrivateSub CommandButton2_Click()
    Dim i AsInteger

    '/* full path to the template file */
    Const mytemplate AsString="C:\Users\PlutoX\Desktop\Excel-Folder\wb_template.xlsx"

    WithMe
        With.ListBox1
            '/* iterate listbox items */
            For i =0To.ListCount -1
                '/* transfer the files using the generic procedure */
                Transferfile mytemplate,.List(i,0)
            Next
        EndWith
    EndWith
EndSub​

Thanks for the help!

[PlutoX]

anyone?

[PlutoX]

I basically just want to know how to define condition 1 and condition 2 in VBA form.

I'll try to clarify it:

• I need to search for for specific keywords in a sheet of wb1.
  
• I dont know the positions of those keywords
  
• In case a keyword is found - condition1 or condition2 will be applied, depending on the keyword:
  
• Condition 1: if keyword in wb1 = "House_1" then copy/paste keyword in wb2 (specific position -> Sheet2, A3) and rename it to "House Blue".Result would be: "House Blue" in A3 of Sheet2 in wb2.
  
• Condition 2: if keyword in wb1 = "Number" then copy the value of the adjoining cell to the right of it and paste in wb2 (specific position -> Sheet3, C5).Result would be: "4" in C5 of Sheet3 in wb2.

So what I want to do is to determine the relevant keywords - and which condition the respective keyword is triggering.