Copy and Paste help on Automated Tag Generator

Bcox25

New Member
Joined
Aug 13, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello,
I am working on a project that uses a user form to populate cells for criteria of data retrieval. I have the macro completed all the way to opening the book and switching to the correct page based on the user form data inputted into the cells. I now need to figure out how to copy select cell data from the opened workbook back to select cells of master workbook. Also, it needs to copy select cells based on cell data in master workbook. Hopefully this makes since. Any help is greatly appreciated.

Capture3.PNGUser form for data input sheet
Capture.PNGData Input Sheet
Capture2.PNGData Selection and Copy Sheet, Cells highlighted need copied
Capture1.PNGData Pasted in correct location

Code of User Form

VBA Code:
Option Explicit
 
Private Sub CommandButton1_Click()
    Range("B3").Value = Me.ComboBox1.Value
    Range("C4").Value = Me.ComboBox2.Value
    Unload Me
    
    Application.Wait (Now() + TimeValue("00:00:01"))
    
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim path As String, filenm As String
    Dim fndRng As Range
    
    
    Set wb1 = ThisWorkbook
    Set ws1 = ActiveSheet
    path = ws1.Range("B2").Value
    filenm = ws1.Range("B3").Value
    
    If Right(path, 1) <> "\" And Left(filenm, 1) <> "\" Then path = path & "\"
    
    If WkbkNotOpen(filenm) Then
        Set wb2 = Workbooks.Open(path & filenm)
    Else
        Set wb2 = Workbooks(filenm)
    End If
    
    'Chooses Sheet
    Set ws2 = wb2.Sheets(ws1.Range("B4").Value)
    
    With wb2
        .Sheets(ws2.Name).Activate
        Set fndRng = ws2.UsedRange.Find(ws1.Range("B5").Value)
        If Not fndRng Is Nothing Then fndRng.Select

    End With

End Sub

Public Function WkbkNotOpen(WkbkName As String) As Boolean
    Dim WB As Workbook
    
    WkbkNotOpen = True
    
    For Each WB In Workbooks
        If WB.Name = WkbkName Then
            WkbkNotOpen = False
            Exit Function
        End If
    Next WB

    
End Function
 
Private Sub CommandButton2_Click()
    Stopped = True
    Unload Me
End Sub

Private Sub UserForm_Initialize()
 Dim myfso As FileSystemObject, myfolder As Object, myfile As Object
 
 Me.ComboBox1.Clear
 
 Set myfso = New Scripting.FileSystemObject
 Set myfolder = myfso.GetFolder("C:\Users\bcox\Desktop")
 
 For Each myfile In myfolder.Files
    If Right(myfile.Name, 4) = "xlsm" Then
    
    Me.ComboBox1.AddItem myfile.Name
    
End If

Next myfile
    
    Me.ComboBox2.List = Sheets("Sheet2").Range("B2:B31").Value
       
End Sub

I would also like to wrap everything up by printing the sheet pasted to, reverting template back to beginning and closing the opened workbook automatically.
Thank you I hope for a quick response.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Forum statistics

Threads
1,214,585
Messages
6,120,394
Members
448,957
Latest member
Hat4Life

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top