Newport Count
Active Member
- Joined
- Oct 25, 2009
- Messages
- 328
Hi,
I'm trying to create some code in a workbook (Test.xlsm) that enables the user to click a command button (CommandButton1) which will open a closed workbook (Tracker.xlsm) in a different folder, look for a value from the open workbook in the closed workbook (recently opened) and then add some data (current date/timestamp) to a specific cell in that row. Then the closed workbook (recently opened) should be saved and closed, returning a 'entry successful' message.
If the closed workbook is open, a message should display 'already open - please try again' or something similar. Likewise, if the value is not found in the closed workbook, then a similar message needs to warn the user that 'no entry exists'.
I currently already have spreadsheets that open closed workbooks and add data, and others (via a userform) that look up data in a different tab and then overwrite that particular row if a match is found - I just cannot combine the two!
I have tried the following code to attempt a basic version of what I require, which should copy the value in cell L16, open the closed workbook, find the next blank line in column A and then paste the value and close that workbook after saving:
However, I receive an 1004 run-time error (PasteSpecial method of Range class failed) with the following bit of code highlighted (the requested workbook does open):
It seems as though the code is not correct for finding the next blank row in that file? Perhaps because I have cobbled the code together from a different spreadsheet?
I also tried this bit of code (again to try to perform a more basic function and to then build on the code....this attempted to do find the value in cell:L16 of the current workbook in the closed workbook and then add today's date in the next cell)
However, I receive an 438 run-time error (Object doesn't support this property or method) with the following bit of code highlighted (the requested workbook does open):
I have no idea on this error!
I've had an attempt to open the closed workbook, and some basic data but I'm doing something wrong in both instances - probably from cobbling together code from other projects.
Perhaps it isn't best to solve the problems with my attempted code above (but I'd still like to learn what went wrong), and focus on what I actually require in my original description?
Any help would be very much appreciated!
Cheers,
Rich
I'm trying to create some code in a workbook (Test.xlsm) that enables the user to click a command button (CommandButton1) which will open a closed workbook (Tracker.xlsm) in a different folder, look for a value from the open workbook in the closed workbook (recently opened) and then add some data (current date/timestamp) to a specific cell in that row. Then the closed workbook (recently opened) should be saved and closed, returning a 'entry successful' message.
If the closed workbook is open, a message should display 'already open - please try again' or something similar. Likewise, if the value is not found in the closed workbook, then a similar message needs to warn the user that 'no entry exists'.
I currently already have spreadsheets that open closed workbooks and add data, and others (via a userform) that look up data in a different tab and then overwrite that particular row if a match is found - I just cannot combine the two!
I have tried the following code to attempt a basic version of what I require, which should copy the value in cell L16, open the closed workbook, find the next blank line in column A and then paste the value and close that workbook after saving:
Code:
Sub CommandButton1_Click()
Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Outputpath As String
Outputpath = "C:\Users\my.name\Desktop\Logs\Workflow\Heatmap\"
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open(Outputpath & "Tracker.xlsm")
With OutputFile
InputFile.Sheets("Test").Range("L16").Copy
OutputFile.Sheets("HeatmapTracker").Activate
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues
Dim sheetdate1 As String
sheetdate1 = Format(Date, "dd-mmm-yy")
ActiveCell.Offset(0, 1).Value = sheetdate1
End With
OutputFile.Save
OutputFile.Close
End Sub
However, I receive an 1004 run-time error (PasteSpecial method of Range class failed) with the following bit of code highlighted (the requested workbook does open):
Code:
Range("A" & lMaxRows + 1).Select
It seems as though the code is not correct for finding the next blank row in that file? Perhaps because I have cobbled the code together from a different spreadsheet?
I also tried this bit of code (again to try to perform a more basic function and to then build on the code....this attempted to do find the value in cell:L16 of the current workbook in the closed workbook and then add today's date in the next cell)
Code:
Sub CommandButton1_Click()
Dim LastRow1 As Long
Dim ABnum1 As Double
Dim ABrng1 As Range
Dim WriteRow1 As Long
Dim InputFile As Workbook
Dim OutputFile As Workbook
Dim Outputpath As String
Outputpath = "C:\Users\my.name\Desktop\Logs\Workflow\Heatmap\"
Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open(Outputpath & "Tracker.xlsm")
With OutputFile
LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
Set ABrng1 = .Range("A1:A" & LastRow1)
ABnum1 = Sheets("Test").Range("L16")
WriteRow1 = Application.Match(ABnum1, ABrng1, 0)
Cells(WriteRow1, 1).Select
Dim sheetdate1 As String
sheetdate1 = Format(Date, "dd-mmm-yy")
ActiveCell.Offset(0, 1).Value = sheetdate1
End With
OutputFile.Save
OutputFile.Close
End Sub
However, I receive an 438 run-time error (Object doesn't support this property or method) with the following bit of code highlighted (the requested workbook does open):
Code:
LastRow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
I have no idea on this error!
I've had an attempt to open the closed workbook, and some basic data but I'm doing something wrong in both instances - probably from cobbling together code from other projects.
Perhaps it isn't best to solve the problems with my attempted code above (but I'd still like to learn what went wrong), and focus on what I actually require in my original description?
Any help would be very much appreciated!
Cheers,
Rich
Last edited: