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.
User form for data input sheet
Data Input Sheet
Data Selection and Copy Sheet, Cells highlighted need copied
Data Pasted in correct location
Code of User Form
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.
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.
User form for data input sheet
Data Input Sheet
Data Selection and Copy Sheet, Cells highlighted need copied
Data 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.