VB Maco Assistance - Writing data from 1 sheet to another

[0255AM]

**EDIT: assumed I would be able to upload example book, but I suppose not..hopefully the narrative with script will be enough**

Not new to VB but it has been a while - so any help would be much appreciated.


My current book deals with a sheet that serves as a User Form; pull-down lets the user pull information from records on the 2nd sheet.

My concern lies with the macro.. it needs to take the 3 cells defined as "Notes" and write them across the appropriate line of data within the 2nd tab based on the ID number in the pull-down menu at the top of sheet one. So, if ID 123456 is selected - then any data I put in the 2 "notes section is writed to the 2 "notes" cells on that line of data in the next sheet...

Unfortunately, it is not behaving as intended and is writing the data down the same colum, however the timestamp / username write are working perfectly.

Sub UpdateLogWorksheet()
    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim nextRow As Long
    Dim oCol As Long
    Dim myCopy As Range
    Dim myTest As Range
    Set inputWks = Worksheets("LoanDetail")
    Set historyWks = Worksheets("TestSampleResults")
 
 
    Set myCopy = inputWks.Range("Notes")
    With historyWks
        nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
    End With
    With inputWks
        Set myTest = myCopy.Offset(0, 2)
        If Application.Count(myTest) > 0 Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With
    With historyWks
        With .Cells(nextRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(nextRow, "B").Value = Application.UserName
        oCol = 3
        myCopy.Copy
        .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
    End With
 
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With myCopy.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub
Sub UpdateLogRecord()
    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim lRec As Long
    Dim oCol As Long
    Dim lRecRow As Long
    Dim myCopy As Range
    Dim myTest As Range
 
    Set inputWks = Worksheets("LoanDetail")
    Set historyWks = Worksheets("TestSampleResults")
 
    'cells to copy from Input sheet - some contain formulas
    Set myCopy = inputWks.Range("Notes")
    lRec = inputWks.Range("CurrLoan").Value
    lRecRow = lRec + 1
    With inputWks
        Set myTest = myCopy.Offset(0, 2)
        If Application.Count(myTest) > 0 Then
            MsgBox "Please fill in all the cells!"
            Exit Sub
        End If
    End With
    With historyWks
        With .Cells(lRecRow, "A")
            .Value = Now
            .NumberFormat = "mm/dd/yyyy hh:mm:ss"
        End With
        .Cells(lRecRow, "B").Value = Application.UserName
        oCol = 3
 
        myCopy.Copy
        .Cells(lRecRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        Application.CutCopyMode = False
    End With
 
    'clear input cells that contain constants
    With inputWks
      On Error Resume Next
         With myCopy.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
End Sub

 

[AlphaFrog]

It's hard to tell exactly what's going on without knowing the address of "Notes"

When you copy "Notes", you do have transpose equal to True which will paste columns-to-rows or rows-to-columns. Is that what you want?

.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, [COLOR="Red"]Transpose:=True[/COLOR]

 

[0255AM]

It's hard to tell exactly what's going on without knowing the address of "Notes"

When you copy "Notes", you do have transpose equal to True which will paste columns-to-rows or rows-to-columns. Is that what you want?

.Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, [COLOR=red]Transpose:=True[/COLOR]

Originally, yes. Sorry - I wrote my post under the assumption I would be attaching a document.

Sheet1. B3, I7, and I20 was ideally to be copied to the corresponding row in Sheet2 based on the record line of the value in B3.

Due to some odd writing behavior, I placed 'hidden' reference cells in a row B3, E3, F3 hoping this would write more appropriatley...unfortunately, it down writes the timestamp, username, and data in B3 correctly BUT then drops the remaining pieces of data E3 and F3 down below.

 

[0255AM]

I updated to FALSE with my reference cells and it is writing appropriately. This should serve the purposes that I need - thank you for catching that.


I suppose one last bit of question I have is:

Is it possible to pull off this action when the data entry cells are merged? A few of the points will require large input, and I'd like a clean looking form..

 

[AlphaFrog]

I think you could do it if the source and destination cells are merged the same.

I would suggest you use the "Center Accross Selection" atribute in the horizontal text alignment tab on the Cell Format dialog if suitable. Merged cells can be problematic for several reasons.