Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Inputs (2003)

    In the attached workbook I have two worksheets--one is labeled Issue Workbook and the other is Issue Submission Template (they would actually be separate workbooks in my process saved to the same folder location). I was reviewing an answer (714,919) to one of my previous posts and wanted to try something similar, wherein multiple users would provide Issue inputs on their Issue Submission Template (row 7) and email that workbook to me. The users are on multiple networks. Upon receipt of the emailed attachment, I would open my Issue Workbook and click macros button (placed somewhere in rows 2-4) that would copy the row 7 inputs from the Issue Submission Template and paste it in the next open row in the Issue Workbook and give it a new number (one up numbering). I would also want to maintain the data validation and/or formulas that the Issue Workbook has. Just an automation routine that would add a cool twist to the workbook. Is this the most efficient way of doing this? Do you have an example that comes close to that already? Any thoughts or help would be appreciated.

    Thanks in advance.
    Amy
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Inputs (2003)

    See if this does help:

    Sub AddData()
    Dim rngS As Range
    Dim wshS As Worksheet
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Dim lngT As Long
    Set wbkT = ThisWorkbook
    Set wshT = wbkT.Worksheets("Issue Workbook")
    On Error Resume Next
    Set rngS = Application.InputBox(Prompt:="Click in the source worksheet", Type:=8)
    If rngS Is Nothing Then
    MsgBox "Canceled!", vbExclamation
    Exit Sub
    End If
    On Error GoTo 0
    Set wshS = rngS.Parent
    lngT = wshT.Range("B" & wshT.Rows.Count).End(xlUp).Row + 1
    wshT.Range("B" & lngT) = Val(wshT.Range("B" & (lngT - 1))) + 1
    wshS.Range("C7:K7").Copy Destination:=wshT.Range("C" & lngT)
    Application.CutCopyMode = False
    End Sub

    The code should go into a module in the target (issue) workbook.
    You'll have to substitute the actual name of the target worksheet.
    When the macro is run, you'll be prompted to click somewhere in the worksheet with the data that you want to transfer.

    Warning: in your sample workbook, there is some text in rows 1007 to 1010!

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Inputs (2003)

    Hans,
    Thanks. This worked well. Please take a look at the attachment and the other attachment forthcoming. Why is it necessary to have a step:

    "Set rngS = Application.InputBox(Prompt:="Click in the source worksheet", Type:=8)"

    Since the cell range of the source worksheet is constant, can the macros of the target workbook (Modules 1 & 2) be activated by merely clicking on the picture in the top left of the source worksheet?

    Also, as opposed to simply doing a one up numbering schema for subsequent log entries, can the Issue Log entries be numbered with an "I" preceding the one up numbering schema (e.g., I1, I2, I3 and so forth). I would apply the same code to the Risk Register with an "R" preceding the one up numbering schema (e.g., R1, R2, R3, R4). How would that most efficiently be done? Please advise.

    Thanks for your help with this.

    Amy
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Inputs (2003)

    Here is the other attachment.

    Amy
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Inputs (2003)

    That line was intended to let the user identify the source workbook when the macro is run from the target workbook.
    If you run the macros from the source workbook, that isn't necessary. You could use these versions:

    Sub AddIssueRecord()
    Dim wshS As Worksheet
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Dim lngT As Long
    Set wbkT = ThisWorkbook
    Set wshT = wbkT.Worksheets("Issues Management Log")
    Set wshS = ActiveSheet
    lngT = wshT.Range("B" & wshT.Rows.Count).End(xlUp).Row + 1
    ' Work around merged cells problem
    If lngT < 7 Then lngT = 7
    wshT.Range("B" & lngT) = "I" & Val(Mid(wshT.Range("B" & (lngT - 1)), 2)) + 1
    wshS.Range("B7:I7").Copy Destination:=wshT.Range("C" & lngT)
    Application.CutCopyMode = False
    End Sub

    Sub AddRiskRecord()
    Dim wshS As Worksheet
    Dim wbkT As Workbook
    Dim wshT As Worksheet
    Dim lngT As Long
    Set wbkT = ThisWorkbook
    Set wshT = wbkT.Worksheets("Risk Register")
    Set wshS = ActiveSheet
    lngT = wshT.Range("B" & wshT.Rows.Count).End(xlUp).Row + 1
    ' Work around merged cells problem
    If lngT < 7 Then lngT = 7
    wshT.Range("B" & lngT) = "R" & Val(Mid(wshT.Range("B" & (lngT - 1)), 2)) + 1
    wshS.Range("B7:L7").Copy Destination:=wshT.Range("C" & lngT)
    Application.CutCopyMode = False
    End Sub

    Note: if you assign the macros to the pictures at the top of the sheets, Excel will hard-code the path of the target workbook that contains the macros, so if you rename or move the target workbook, you'll have to reassign the macros.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Inputs (2003)

    Hans,
    This works well; however, I would like to modify it such that Sub AddRiskRecord() copies B7:E7, G7:L7 from the source worksheet (instead of the current B7:L7) and pastes it in the next open row in the target worksheet. But instead of pasting B7:E7 and G7:L7 next to one another starting in column C, I would like it to paste starting in column C in the following configuration C:F and H:M so column G of the target worksheet is not touched since I have a formula there. Thoughts.

    Thanks
    Amy

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Inputs (2003)

    Change the line

    wshS.Range("B7:L7").Copy Destination:=wshT.Range("C" & lngT)

    to the following two lines:

    wshS.Range("B7:E7").Copy Destination:=wshT.Range("C" & lngT)
    wshS.Range("G7:L7").Copy Destination:=wshT.Range("H" & lngT)

Posting Permissions

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