I need some help with a macro in an excel file (active X) to send data to a log file. I have a starting point that worked for a previous version of the file I’m using now. This is a macro I use to update a log that captures specific information from a form I have. It will open the log find the last row or duplicate row if being updated and paste the new or updated information in the appropriate row on the log and then close the log. I'm now being asked to add to my form which will require different headers in the log. I would like to use the same log and add a different tab to dump this new data in on specific rows. How can I modify the code provided to dump the information in sheet 1 of the log if my choice is A, B or C and then dump information to sheet 2 if my choice is D. The information that is currently being dumped to sheet 1 is located in row A65:K65 and is named logdata. I would create a separate row for the choice D data to be dumped in sheet 2 of the log - lets say that will be A70:K70 and I would name this row of data "safetylog".
Here is the macro I have:
Sub GDLOGUPDATE()
Dim MainWkbk As Workbook 'one containing the form
Dim CCform As Worksheet
Set MainWkbk = ActiveWorkbook 'file containing the form that is already open
Set CCform = MainWkbk.Sheets("GENERAL DETAIL")
Application.ScreenUpdating = False
Range("A65").Select
RowID = ActiveCell.Value
Range("logdata").Select
Selection.copy
Workbooks.Open FileName:="XXXXXXXXXXXX\XXXX\log.xls"
Set NextWkbk = ActiveWorkbook 'log workbook
Set ccLog = NextWkbk.Sheets("Production Log")
'get the last row in the log if we need to add a new log
Dim LastRow As Long
LastRow = ccLog.UsedRange.Rows.Count
'Set variables to find existing log number
Dim rng As Range
Dim FindString As String
'Set the "FindString" variable to the current log number and search for it in column A
FindString = RowID
'find the current logID if it is present
Set rng = Cells.Find(What:=RowID, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
'If it finds the log, paste and replace the data
If Not rng Is Nothing Then
rng.Offset(0, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Transpose:=False
Application.CutCopyMode = False
Else
' If it does not find the log, add a row to the bottom
Sheets("Production Log").Range("A" & LastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'****************
'save and close the Log file
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
'set active cell in form back to c8
Range("c8").Select
End Sub
I hope this makes sense.
I also noticed that there is some formulas in a separate tab (sheet 2) in my current log with the following information:
<tbody>
</tbody>
They are listed in cells A1, A2 and A3 respectively. I think these are required for the log to look for the last row in the sheet to paste new information.
Also, if there is a better code to do what I'm looking to do, then I'm open. I don't favor the one provided, this is what I inherited and it worked so I never questioned it.
Ultimately, this is how I would like this macro to work. Press the macro button - macro determines selection in cell B9 (Quality, Standard, Change or Safety) - copies data listed starting in A65:K65 (named “logdata”) if Quality, Standard or Change are selected - opens log file - selects sheet 1 - finds last row starting with column A - paste data - save and close form and return to a specified cell in my active open form. If Safety is selected - copies data listed in A70:K70 (named “safetylog”) - opens log file - selects sheet 2 - finds last row starting with column A - paste data - save and close form and return to a specified cell in my active open form. If this is an update to a line item on the log - sheet 1 or 2, I would want the macro to match the number in the log (will be listed in column A) with the active file open and replace the updated contents on the same line and not paste a new line of information - save and close file to return to a specified cell in the active open form.
If I can eliminate the need for the formulas posted above, even better! If they are needed, I would add them to separate sheet(s).
I appreciate the feedback and help.
Thanks,
Brian
Here is the macro I have:
Sub GDLOGUPDATE()
Dim MainWkbk As Workbook 'one containing the form
Dim CCform As Worksheet
Set MainWkbk = ActiveWorkbook 'file containing the form that is already open
Set CCform = MainWkbk.Sheets("GENERAL DETAIL")
Application.ScreenUpdating = False
Range("A65").Select
RowID = ActiveCell.Value
Range("logdata").Select
Selection.copy
Workbooks.Open FileName:="XXXXXXXXXXXX\XXXX\log.xls"
Set NextWkbk = ActiveWorkbook 'log workbook
Set ccLog = NextWkbk.Sheets("Production Log")
'get the last row in the log if we need to add a new log
Dim LastRow As Long
LastRow = ccLog.UsedRange.Rows.Count
'Set variables to find existing log number
Dim rng As Range
Dim FindString As String
'Set the "FindString" variable to the current log number and search for it in column A
FindString = RowID
'find the current logID if it is present
Set rng = Cells.Find(What:=RowID, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
'If it finds the log, paste and replace the data
If Not rng Is Nothing Then
rng.Offset(0, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Transpose:=False
Application.CutCopyMode = False
Else
' If it does not find the log, add a row to the bottom
Sheets("Production Log").Range("A" & LastRow + 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End If
'****************
'save and close the Log file
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
'set active cell in form back to c8
Range("c8").Select
End Sub
I hope this makes sense.
I also noticed that there is some formulas in a separate tab (sheet 2) in my current log with the following information:
| =lastincolumn('Production LOG'!A:A) |
| =TEXT(RIGHT(A1),"000") |
| =LEFT(A1,7) |
<tbody>
</tbody>
They are listed in cells A1, A2 and A3 respectively. I think these are required for the log to look for the last row in the sheet to paste new information.
Also, if there is a better code to do what I'm looking to do, then I'm open. I don't favor the one provided, this is what I inherited and it worked so I never questioned it.
Ultimately, this is how I would like this macro to work. Press the macro button - macro determines selection in cell B9 (Quality, Standard, Change or Safety) - copies data listed starting in A65:K65 (named “logdata”) if Quality, Standard or Change are selected - opens log file - selects sheet 1 - finds last row starting with column A - paste data - save and close form and return to a specified cell in my active open form. If Safety is selected - copies data listed in A70:K70 (named “safetylog”) - opens log file - selects sheet 2 - finds last row starting with column A - paste data - save and close form and return to a specified cell in my active open form. If this is an update to a line item on the log - sheet 1 or 2, I would want the macro to match the number in the log (will be listed in column A) with the active file open and replace the updated contents on the same line and not paste a new line of information - save and close file to return to a specified cell in the active open form.
If I can eliminate the need for the formulas posted above, even better! If they are needed, I would add them to separate sheet(s).
I appreciate the feedback and help.
Thanks,
Brian
