Macro to get a cell copied from one worksheet to another, this is an odd situation

[sspatriots]

Hi,

I'm not quite sure how to explain this, but I have a workbook with several worksheets (all tables of imported data when the file opens). The first worksheet of the workbook has a column called "Job #". Is it possible to have a macro to first ask me to select the target cell that I want a value I find on another worksheet copied to this target cell? In another words, perhaps have the macro instruct the user to select the target cell on the main worksheet, then have it automatically open the other worksheet where I will locate the "Job #" I want, then instruct me to find the cell who's contents I want copied back to the target cell by double-clicking that cell to copy its contents back to the original target cell on the main worksheet?


Any ideas greatly appreciated. Thanks, SS

 

[OaklandJim]

I try to assist. I need some clarification...

1. User selects a cell in worksheet named Main that contains a Job #. Where are those data located in the Main worksheet?

2. "Automatically open the other sheet" which is a source worksheet containing the Job # sought. Which worksheet is the one to open?

3. User is instructed to find (double click on?) the cell whose contents is to be copied back to the target cell selected in step 1.

So the data copied to the target cell essentially overwrites the JOB # in the target cell? Or is the target cell some other data in the same row as the Job #?

If the latter then the macro has to determine which Job # corresponds to the target cell? For example, for Job # 3, user selects the customer name cell in the same row as the Job # then some "source" worksheet (i.e., containing data sought) opens. User then double clicks on a cell in the source worksheet.

How many worksheets with data? You said "several worksheets."

Does macro have to hunt through the "various worksheets" containing data to locate the Job # sought? If yes then I'd need a workbook to work with that contains realistic data.

 

[sspatriots]

I try to assist. I need some clarification...

1. User selects a cell in worksheet named Main that contains a Job #. Where are those data located in the Main worksheet?

2. "Automatically open the other sheet" which is a source worksheet containing the Job # sought. Which worksheet is the one to open?

3. User is instructed to find (double click on?) the cell whose contents is to be copied back to the target cell selected in step 1.

So the data copied to the target cell essentially overwrites the JOB # in the target cell? Or is the target cell some other data in the same row as the Job #?

If the latter then the macro has to determine which Job # corresponds to the target cell? For example, for Job # 3, user selects the customer name cell in the same row as the Job # then some "source" worksheet (i.e., containing data sought) opens. User then double clicks on a cell in the source worksheet.

How many worksheets with data? You said "several worksheets."

Does macro have to hunt through the "various worksheets" containing data to locate the Job # sought? If yes then I'd need a workbook to work with that contains realistic data.

I managed to figure this out just now. I'll post what I came up with on here next week. Sorry about not explaining it that well.

 

[sspatriots]

Here goes what I ended up doing:

First I created a button on my worksheet that launches the UserForm that will give me options to select which spreadsheet I want to import the Job # from.

The code behind the "Import Job #" button:

Sub Launch_Import_JN()

    Import_JN.Show vbModeless
  
End Sub

The "Click" code for one of the buttons on the Userform that calls the code to go retrieve the Job #:

Private Sub lblHYDRO_Click()

    RetrieveHYDROJN

End Sub

The code that is called by the "Click" code above that gives the cell that is selected after the message box prompt a named range:

Sub RetrieveHYDROJN()

Dim myReply As Range
Dim iName As String

On Error Resume Next
Set myReply = Application.InputBox(prompt:="Please select the blank HYDRO Job Number cell where you want to import the applicable Job Number.", Type:=8)
If myReply Is Nothing Then Exit Sub
myReply.Activate

ActiveCell.Select

HYDROJNRange

MsgBox prompt:="After locating your HYDRO Job Number on the next worksheet that appears, 'Double-Click' the HYDRO Job Number to copy it back to your Job line on the 'Jobs' Worksheet.", Buttons:=vbOKOnly, Title:="MsgBox"

Sheets("HYDRO - In Production").Select

End Sub

The code that cleans up the source worksheet after copying the Job # to the clipboard and putting it into the named range:

Sub ReturnToJobsWSAfterCopyingHYDROJN()

Clear_All_Filters_Table_HYDROJN

Sheets("Jobs").Select
Range("HYDROJNRange").Select

    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

End Sub

The code that turns off any filters in the source worksheet that were used to locate the Job # and copying it to the clipboard and putting it into the named range:

Sub Clear_All_Filters_Table_HYDROJN()

Dim lo As ListObject
  
    'Set reference to the first Table on the sheet
    
    Set lo = Sheets("HYDRO - In Production").ListObjects(1)
    
    'Clear All Filters for entire Table
    lo.AutoFilter.ShowAllData
    
    Range("A2").Select
    
End Sub

The code that assigns a named range to the cell on the destination worksheet where you want to place the Job # you are looking up on the other worksheet source:

Sub HYDROJNRange()

    Dim iName As String
    iName = "HYDROJNRange"
    ActiveSheet.Names.Add Name:=iName, RefersTo:=Selection

End Sub

The code that closes the Userform when done importing your Job #:

Private Sub lblClose_Click()

    Unload Import_JN
    
    Sheets("Jobs").Select

End Sub

The Userform activation code that places the Userform on the same monitor where I want it to appear (which is off to the right of my screen and out of the way so I can easily find and select the Job # when I get to the source worksheet:

Private Sub UserForm_Activate()
     
    Me.StartUpPosition = 0
    Me.Top = Application.Top + 25
    Me.Left = Application.Left + Application.Width - Me.Width - 25

End Sub

I have also tailored everything above so that I can accomplish the same thing by launching an "Import Job #" button on a Userform that has all the records for a single job. This allows the user to use a Userform to make all their entries for a job, rather than have to close their Userform and import the Job # on the worksheet itself. If anyone needs that, please let me know and I will share. Hopefully, I've covered everything here.


Regards, SS