VBA Help with changing tracker on different excel sheet

collin8579

New Member
Joined
Oct 31, 2017
Messages
19
Good Morning,
I have an excel sheet I'm building that does budgeting for specific types of projects. I have most of the VBA worked out for that. The part I'm having issues with is updating an exteral excel tracker.
This is the script to create the tracker if it doesn't exist:

Sub trackerTest()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set objWS = CreateObject("WScript.Shell")
strDesktopPath = objWS.SpecialFolders("Desktop")
Workbooks.Open Filename:=strDesktopPath & "\sites\Tracker.xlsx"
Windows("Tracker.xlsx").Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

The main sheet I'm working on will have a dynamic name based on properties within the sheet. IE Job Number, Site Name, etc
This is a snippet from that save code (no issues with this):
ActiveWorkbook.SaveCopyAs Filename:= _
strDesktopPath & "\sites\BU# " & BUN & " JOB# " & JDE & " " & STE & "\BU# " & BUN & " JOB# " & JDE & " " & STE & " Main.xlsm"


The Tracker will have a line for each Job/Contractor and will look similar to this (ignore the strikethrough)

BU
JDE #
ALT SITE ID
SCOPE
GEN CONTRACTOR
ORIG AMOUNT
NEW AMOUINT
DATE SOV REC'D
DATE POR SNT
<strike>111111</strike>
<strike>222222 </strike>
<strike>SX33333</strike>
<strike>RF MOD</strike>
<strike>YYYYY</strike>
<strike>$3,398.90</strike>
<strike>2-May</strike>
<strike>3-May</strike>
111111
222222
SX3333
RF MOD
XXXXX
$3,697.09
4-Aug
4-Aug

<tbody>
</tbody>


What I need is in my main sheet (which has the dynamic name above) able to edit the lines on the tracker. I don't need it to delete lines but I need it able to differentiate based on the Contractor/ Job number/ Amount ---
I have three buttons on my main sheet. Tracker POR Sent, Tracker - Budget Out, and Tracker - PO Sent
So when I click one it goes to the tracker sheet (That was created if it wasn't there already), and either update an existing tracker line based on its search or adds a new tracker line if it isn't there yet. Then updates a section of the tracker based on the button - a Date in the POR sent, Budget out, or PO Sent section

Below is the code I'm experimenting with to find the lines on the tracker - It works in theory but when I put variables in noted sections it doesn't work anymore

Sub linesearch()
Set objWS = CreateObject("WScript.Shell")
strDesktopPath = objWS.SpecialFolders("Desktop")
Workbooks.Open Filename:=strDesktopPath & "\sites\Tracker.xlsx"
Windows("Tracker.xlsx").Activate
Dim rngFound As Range
Dim strFirst As String
Dim Joba As String
Joba = ThisWorkbook.Sheets("Project Selection").Range("C5").Value
Dim amnta As String
amnta = ThisWorkbook.Sheets("Project Selection").Range("C11").Value
Set rngFound = Columns("A").Find(BU, Cells(Rows.Count, "A"), xlValues, xlWhole)
If Not rngFound Is Nothing Then
strFirst = rngFound.Address
Do
If LCase(Cells(rngFound.Row, "B").Text) = LCase(Joba) And _
LCase(Cells(rngFound.Row, "F").Text) = LCase(amnta) _ **********This Joba and amnta are names in the main worksheet to determine if tracker has viable line - This whole sub works when I have specific text in this section but not named items??*****
Then
'Found a match
MsgBox "Found a match at: " & rngFound.Row & Chr(10) & _
"Value in column C: " & Cells(rngFound.Row, "C").Text & Chr(10) & _
"Value in column D: " & Cells(rngFound.Row, "D").Text
End If
Set rngFound = Columns("A").Find(BU, rngFound, xlValues, xlWhole)
Loop While rngFound.Address <> strFirst
End If
Set rngFound = Nothing
End Sub


Now, This was pilfered from an online source so I get the basics of what it is doing but not the whole understanding.

My biggest issue is when using the main sheet (dynamic name) - Going into the tracker and finding the line I need or creating it - then coming back to the main sheet
I don't really know how to do that

So my questions:
When I switch the active sheet to tracker.xlsx how do I go back to the dynamic name sheet?
What is the best way to update the tracker lines? I'm fairly confident I can find the row number with the above script if I get it working with the named items (amnta, joba) which correspond with a cell on the main sheet.
I want it to be able to add lines to the tracker if they don't exist - and it has a variable number of lines (can add them in the beginning)

Any help would be appreciated!
<strike> </strike>
<strike></strike>
<strike></strike>
<strike></strike>
<strike></strike>

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,102,784
Messages
5,488,860
Members
407,658
Latest member
Arias610

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top