Log in

View Full Version : Amend Excel VBA code



steads1980
02-20-2011, 02:52 AM
:banghead: :helpI 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?


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

Bob Phillips
02-20-2011, 03:27 AM
It looks like it may be here



Set myCopy = inputWks.Range("OrderEntry")


The worksheet name OrderEntry may need extending.

steads1980
02-20-2011, 03:29 AM
Hi thanks for your reply but I extended that and it still didn't work

Bob Phillips
02-20-2011, 03:44 AM
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
02-20-2011, 04:05 AM
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
02-20-2011, 04:18 AM
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
02-20-2011, 04:21 AM
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
02-20-2011, 04:39 AM
Yes, it does now.

Bob Phillips
02-20-2011, 04:41 AM
In all the navigate procedures in the modViewData module, change



historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 6)).Copy


to



historyWks.Range(historyWks.Cells(lRecRow, 3), historyWks.Cells(lRecRow, 7)).Copy

steads1980
02-20-2011, 05:36 AM
Thank you so much James! thats working.