Amend Excel VBA code

[steads1980]

I am trying to amend the attached spreadsheet so the code will copy more than 4 columns of data from the row from the data table onto the front form. I can't find the code I need to amend. Can anyone help?

[VBA]
Option Explicit

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("Input")
Set historyWks = Worksheets("PartsData")

'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")

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("Input")
Set historyWks = Worksheets("PartsData")

'cells to copy from Input sheet - some contain formulas
Set myCopy = inputWks.Range("OrderEntry")

lRec = inputWks.Range("CurrRec").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[/VBA]

[Bob Phillips]

It looks like it may be here

[vba]

Set myCopy = inputWks.Range("OrderEntry")
[/vba]

The worksheet name OrderEntry may need extending.

[steads1980]

Hi thanks for your reply but I extended that and it still didn't work

[Bob Phillips]

Aah, I see you are cribbing code from my good friend Debra!

Okay so you made me look at what it is doing, but what do you want it to do that it is not?

[steads1980]

Basically when you select from the drop down only 4 fileds are returned from the data table but I want more than 4 fields returned. Does this make sense??

[Bob Phillips]

No, because the only value beyond those 4 cells is the Sales Person, whereas you display table shows Price and Total. It does not compute Jim!

[steads1980]

Sorry I didn't change the front form it should say sales person as the fifth field. So I want to take all 5 and get them into the form. Does this make sense?

[Bob Phillips]

Yes, it does now.

[Bob Phillips]

In all the navigate procedures in the modViewData module, change

[vba]

historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 6)).Copy
[/vba]

to

[vba]

historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 7)).Copy
[/vba]

[steads1980]

Thank you so much James! thats working.