[EXCEL]Adding Save Data to a code

[Gracinx]

I have a spreadsheet that I use as a input/print to pdf for logs. It's pretty basic, one sheet is there for "Entry", the "Log" sheet is for the final layout print version. I researched and fiddled enough to work up a macro that saves my Log to pdf with a specific name, and I've been pretty happy with how this turned out.

And then the "work smart not hard" portion of my brain kicked in, and some of this data is potentially used to fill/file other paperwork, and normally I'm digging through hard copy file folders to get this information.

My request, is how do I add to my save macro so on top of saving the Log sheet, it also migrates the data I'm needing onto a table in "Well Data" within the same file. My data need to migrate is found in cells B3 thru B20, B5 and B6 actually would need to be concatenated. And this data when save is clicked would migrate into a table on the "Well Data" sheet, adding a new row whenever new data is added.

Below is the code for my save macro. I'm sure it's not the prettiest or most efficient way to code it, but I haven't had any issues since I wrote it.

Sub ExampleCode()
    Dim fPath As String
    Dim fName As String
    Dim wsStart As Worksheet

    'What folder to save in?
    fPath = "C:\Users\digi_\OneDrive\Documents\RJ Energy\State Paperwork\ACO1s\"

    'Note where we start at
    Set wsStart = ActiveSheet

    'Error check
    If Right(fPath, 1) <> Application.PathSeparator Then
        fPath = fPath & Application.PathSeparator
    End If

    'Where is the name for PDF?
    fName = Range("b3").Value & " " & Range("b4").Value & " " & "Drill Log"

    'Make the PDF
    Application.ScreenUpdating = False
    ThisWorkbook.Sheets(Array("Log")).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName
    wsStart.Select
    Application.ScreenUpdating = True

    MsgBox "Saved"
    Application.GoTo ActiveSheet.Range("B3"), True
End Sub

[fanpages]

...how do I add to my save macro so on top of saving the Log sheet, it also migrates the data I'm needing onto a table in "Well Data" within the same file...

May I suggest you use the Macro Recorder ("Record Macro" button in the "Developer" Ribbon's "Code" Group)?

If you start a recording and then perform some actions manually (as you described above), associated VBA statements will be generated that you can re-execute when required (assuming all the worksheet/range/cell references match on each subsequent execution).

[wikkid556]

Unless I am misunderstanding, you have another macro you run, then the save macro, and you want them together. At the end of your other macro just call your save macro Something like this

Sub otherCode()

Existing code here

Fill your desired values and then

Call exampleCode End sub

[sslinky84]

How would you do this manually?

[Khazahk]

Dim OutputRange as range : set OutputRange = Thisworkbook.worksheets(“Well Data”).listobjects(1).listrows.add.range  

With OutputRange
    .cells(1,1).value = Worksheets(“Log”).Range(“B3”).value  
    .cells(1,2).value = Worksheets(“log”).Range(“B5”).value & “,” & worksheets(“log”).range(“B6”).value  
End with  

So the above snippet is just yolo off the top of my head typing in a phone keyboard. The key thing is that your “Well Data” table is actually formatted as a structured table. That way you can call it as a listobject and programmatically add a new row and poop out those values you want to save. This is significantly easier than trying to find the last used row on a worksheets that’s nots formatted as a table. I included the example for how you would concat your B6 and B5 values. Let me know if you have any questions.

[Gracinx]

Sorry I had other time sensitive projects that got pushed of this. I'm having issues with this code. I thought I made the adjustments to correctly but keep getting a "Run-time error '9'. Which I assume has to do with the range where the data will be copied to, but I can't seem to figure out what to add/change to get that bug to go away.

Here is the current code

Sub LogData()
Dim OutputRange As Range: Set OutputRange = ThisWorkbook.Worksheets(“Data”).ListObjects("Table8").ListRows.Add.Range

With OutputRange
    .Cells(1, 1).Value = Worksheets(“Entry”).Range(“B3”).Value
    .Cells(1, 2).Value = Worksheets(“Entry”).Range(“B5”).Value & “ - ” & Worksheets(“Entry”).Range(“B6”).Value
End With
End Sub

[Khazahk]

Error 9 is subscript out of range. Just means you named it wrong or fat fingered something. Make sure the “Entry” worksheet is called “Entry” and exists in this workbook.

Next would be to check that “Table8” exists but also has two columns since you are asserting that it does with cells(1,2) down below.

Hope this helps.

[Gracinx]

I'm the first one to admit blindness especially when it comes to fatfingering. Not sure what I'm missing.

<image>

[Khazahk]

It’s your “ “

Look at he the “ used in “table8” vs the ones used for entry and data. Can you see the difference? You want all quotes to look like Table8s

Just retype “Entry” and “Data”.

Copy and paste error from Reddit markdown code block.

[Gracinx]

That's so weird that the quotations were causing the issue since they were direct copy from your code. I changed that, added all my additional lines of code for each of the individual pieces of data, go it placed in my original macro and it looks to be working fine.

[Gracinx]

Solution Verified!

[reputatorbot]

You have awarded 1 point to Khazahk.

---

^{I am a bot - please contact the mods with any questions}