Form button to open closed workbook, find value in row and add data

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:
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:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I have used this basic code to open the closed workbook, add a value (cell value from the original worksheet) plus a date value in the next cell:

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")
    
        
    InputFile.Sheets("Test").Range("L16").Copy
    
    OutputFile.Sheets("HeatmapTracker").Activate
    Range("F13").Select
    
    Selection.PasteSpecial Paste:=xlValues
        
        
    Dim sheetdate1 As String
    sheetdate1 = Format(Date, "dd-mmm-yy")

    ActiveCell.Offset(0, 1).Value = sheetdate1

    OutputFile.Save
    OutputFile.Close


End Sub


I suppose the piece I am missing is to lookup that original value in column A and then paste the date/timestamp in the next cell.


Cheers,
Rich
 
Upvote 0
Apologies if I have caused confusion with my previous attempts at the code on this thread....perhaps best to ignore the above and just look at what I'm trying to do:


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'.

It sounds like something not too complex, I'm just stuck on the find/lookup function?


Cheers,
Rich
 
Upvote 0
I managed to get this to work as follows:


I think the main issue was being able to read the value from the open workbook in the closed workbook and then selecting a particular cell - my workaround was to paste the value into the closed workbook itself and then lookup the value within the workbook, which worked!

Code:
Sub CommandButton1_Click()

Dim sht As Worksheet
    
    Dim InputFile As Workbook
    Dim OutputFile As Workbook
    Dim Outputpath As String

    
    Set InputFile = ActiveWorkbook
    Set OutputFile = Workbooks.Open(Outputpath & "Tracker.xlsm")
    
    Outputpath = "C:\Users\my.name\Desktop\Logs\Workflow\Heatmap\"
    

    With Application
        .ScreenUpdating = False
        .Application.DisplayAlerts = False
    End With
    
    
    InputFile.Sheets("Test").Range("L16").Copy
    
    OutputFile.Sheets("HeatmapTracker").Activate
    
    Range("G10").PasteSpecial Paste:=xlValues
    

    Set sht = OutputFile.Sheets("HeatmapTracker")
    
    
    On Error GoTo ErrMsg
    
    sht.Range("A:A").Find(Range("G10").Value, , xlValues, xlWhole).Select
    
    
ErrMsg:
    
    InputFile.Sheets("Test").Activate
    
    MsgBox ("The Customer Name has not been found in the Heatmap Tracker." & _
        vbCrLf & _
        vbCrLf & "(Please ensure that the correct Customer Name has been used in both the Heatmap Tracker AND this Form.)"), , "Customer Account Form - Error Message"
    
    OutputFile.Close
    
    Exit Sub
    
    
    Dim sheetdate1 As String
    sheetdate1 = Format(Date, "dd-mmm-yy")

    ActiveCell.Offset(0, 1).Value = sheetdate1
    
    Range("G10").Clear
    
    
    OutputFile.Save
    OutputFile.Close


    InputFile.Sheets("Test").Activate


End Sub



Thanks for being a sounding-board!


Cheers,
Rich
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top