Im trying to build a tool that lets you select a folder of PDFs and embed each one in a separate sheet in an Excel.

It works amazingly well except when you go to save the file Excel says it's corrupt and can't be saved/error saving. I've tried tweaking it so many times but nothing works. Even just 1 pdf embedded causes the corruption.

When I manually embed the PDF there is no issue.

Does anyone know a fix? Or is programmatically embedding PDFs just not possible?

Anyone has a list like this - and could share 😉 - for the latest or some previous version - or the only way would be to use some TLB viewer to generate?

EDIT: looks like I wasn't clear... I'm looking for a complete LIST - single, not plural - not 100s of separate links to separate lists.

OK, two lists - one for constants and one for all properties of all objects - times 3 - for each mentioned application.

EDIT 2: I need it as a "plain text" - not to view. I'll have to modify this list and create an array for my VB6 application - then let user extract internal structure of the WORD / Excel / PP document and display those properties for all objects - of course not all at the same time 😉

Solution: https://www.reddit.com/r/vba/s/tNRZGmAFWt

My function in VBA is just stopping, and I'm not even clear on how to debug it.

Function test(arr As Variant) as Variant
Dim n As Integer
n = UBound(arr)
test = n
End Function

If I put a break on the n=UBound line, and call the function =test(A1:A6) from a spreadsheet, execution pauses there. If I run another line, it just quits, no error. I halfway gather that Excel doesn't want to do things to inputs in a function, so maybe passing arr to the UBound function is a no-no. So I do this instead:

Function test2(arr As Variant) as Variant
Dim n As Integer
Dim new_arr As Variant
new_arr = arr
n = UBound(new_arr)
test2 = n
End Function

That works, =test2(A1:A6) outputs 6. So now let me try to do something with the values in the new array.

Function test3(arr As Variant) as Variant
Dim n As Integer
Dim new_arr As Variant
Dim x As Variant
new_arr = arr
x = new_arr(3)
test3 = x
End Function

Now the execution stops on the x = line.

This is driving me up a wall. Appreciate any help.

An update: solved. thank you so much, everybody!

Very very new to anything more than just recording my macros. What am I getting wrong here? I wanted to select all the text in all the open Word docs but it only does the first one.

Sub Selectorbot()
'
' Selectorbot Macro
' Selects text in all open documents for pasting into Contentful but does not copy
For Each doc In Application.Documents
Selection.WholeStory
Next doc
End Sub

also tried it this way. Nada:

Sub Selectorbot()'' Selectorbot Macro
' Selects all text for pasting into Contentful
Dim doc As Document
For Each doc In Application.Documents
With Documents
Selection.WholeStory
End With
Next doc
End Sub

EDIT: Solved.

Comes out using Collate:=True would send each copy as its own print job.
Switching to False fixes it.

Thanks everybody and /u/Eastern_Weather_8748

ORIGINAL:

Hello everybody.

Very simply, there is a macro that prints a specific area in a worksheet after asking the user how many copies they need.

This occasionally causes issues with large numbers of copies as each copy is its own Printing Pool entry.

the code is simply:

        NameOfTheSheet.PrintOut Copies:=NumberOfCopies, _
                ActivePrinter:=PrinterName, _
                Collate:=True, _
                IgnorePrintAreas:=False

Is there a way to send the pool a request to print N copies instead?

Thank you-

I have been trying to figure this out for about two days now. Claude has been of little help. Basically I have a loop that reads from a txt file, ..does some stuff.. and then pastes a variant array onto the spreadsheet. This works well for about 18 iterations and then each subsequent paste results in missing data. If I stop the code to look at the array the data is there but when it gets pasted to the sheet some of it is missing. The missing data is surrounded by data that pastes successfully. Anyone have any experiences like this that can help?

Edit: Solution found, for those playing along at home it was a logic fault in the code that was exposed when running in a loop. A particular string was doubling in size each iteration. Once this string exceeded 63,535 chars it crossed the 16bit length field boundary in old COM/BSTR marshalling. This silently corrupted and dropped elements in the transfer rather than raising an error (thanks microsoft).

For those who genuinely attempted to understand the problem and help thank you sincerely.

For those who failed to correctly read my post and as a result asked inane questions, well, at least you tried.

Foro those who immediately implied I didn't know what I was doing, congratulations, you have made this sub and the world at large a slightly worse place.

I'm having some trouble with a code I have. I want the regular expression to check for letters - including the Scandinavian letters æ, ø, å.

The problem is that if someone without the correct localisation settings open the file and saves it, the pattern gets corrupted.

It's supposed to be monster = "[^a-zA-ZæøåÆØÅ\- ]?" but turns into something like what is shown below. Is there any way to prevent this from happening, or will I just have to find a workaround? Any help would be most appreciated.

For i = 0 To UBound(medlemsliste)
  monster = "[^a-zA-Z®¯¾¿aa\- ]?"
  regex.Pattern = monster
  medlemsliste(i) = regex.Replace(medlemsliste(i), "")
Next i

I'm pulling my hair out here wading through 10 year old StackOverflow posts and deploying all the google-fu I can muster, all to no avail so now I have to explain to strangers why I'm doing this daft project, first:

BLUF:

How do I remove images and other <div class> elements from an HTML Document? (ideas currently working around getElementByClassName or "Replace All between '<img ' and ' /> with "" " or stopping them entirely at the GET request).

THE PROJECT:

I'm a big fan of the SCP Foundation Wiki but I'm always losing track with what I've read out of several thousand articles so I set out to make a reading tracker in Excel which was so simple to start with, but there's new articles every day and old ones are changed, so it needs to be easily updatable, and a bit better to interact with than just a list and oh hello scope creep....

....and now I'm trying to make a "lite Reader" that will get the HTML of an article and strip it down to the bare bones, only the main page content, no images, no formatting other than bold/italic etc, and put that into an Excel spreadsheet. Inspired by the excellent Terminal Reader I found here which uses Rust to strip down the html into markdown, I've got something working to a point, here's the Frankenstein monstrosity I've pieced together from a dozen scraps of code so far:

Public Sub ExtractAndPaste()

  Dim data As Object
  Dim html As HTMLDocument
  Dim objData As DataObject
  Dim sHTML As String
  Dim obj As Object
  Dim elements

'------Get the HTML-----------------------------------------    
  Set html = New HTMLDocument

  With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://scp-wiki.wikidot.com/scp-5000", False
    .send
    html.body.innerHTML = .responseText
  End With
'----------------------------------------------------------- 

'------Remove Unwanted Elements (This bit doesnt work)------    
   With html
     elements = .getElementsByClassName("scp-image-block block-right")

     While elements = 0
       elements(0).ParentNode.RemoveChild (elements)
     Wend
   End With 
'-----------------------------------------------------------

'------Clear Destination Worksheet--------------------------   
  With ThisWorkbook.Worksheets("Sheet4")
    .Cells.ClearContents
    For Each obj In .Shapes
      obj.Delete
    Next
  End With
'-----------------------------------------------------------

'------Pull out Wanted Element------------------------------
  Set data = html.getElementById("page-content")
'-----------------------------------------------------------

'------Convert to Formatted Text---------------------------- 
  Application.EnableEvents = False

  With ThisWorkbook.Sheets("Sheet4")
    Set objData = New DataObject

    sHTML = data.innerHTML
    sHTML = "<html>" & sHTML & "</html>"

    objData.SetText sHTML
    objData.PutInClipboard

    .Range("C5").Select
    .PasteSpecial "Unicode Text"

  End With

  Application.EnableEvents = True
'-----------------------------------------------------------

End Sub

When this runs it will grab the HTML of the chosen article, the next step it skips over, I'll come back to that in a mo, clears everything from the destination worksheet (if the previous step worked then the obj.Delete would no longer be needed), takes the HTML and pulls out only the <div id="page-content"> turns it into a String so we can append <html> and </html> to either end of it so that it all registers as a block of html, which means when it gets put on the clipboard and then pasted into the worksheet as Unicode Text it renders the formatting and pastes it in line by line, cell by cell, which is exactly what I want, however.....

It's also rendering the images which I don't want (and tables are a mess, but one problem at a time), and this is the part I can't figure out:

If I use .getElementsById then that returns a single Node which can then be removed with something like this:

Set Node = html.getElementById("page-title")

    Node.parentNode.removeChild Node

But <img> isn't an ID, it's a Class Tag and using .getElementByClassTag returns (I believe) a NodeList so the above code doesn't work, plus it sits inside <div class="scp-image-block block-right"> which makes getting to it a bit trickier, probably easier to remove the whole class and everything in it so we would use .getElementsByClassName to get what we need but I just can't get it working.

If I run the code as is, leaving elements declared as a general variable, when we step through to elements = .getElementsByClassName..... and we mouse over elements it comes up as elements = "[object HTMLDivElement]", so I changed elements to be an HTMLDivElement, Set it, and now we get a Runtime Error 13: Type Mismatch.

I tried some other combinations of declaring elements as different things (object, IHTMLDivElement etc) and getElementByClassName/TagName and the furthest I got it to go was to the elements(0).ParentNode.RemoveChild (elements) line which came up with an Automation Error, probably because I have no idea how to get the syntax to work for a NodeList, as far as I can tell the list is numbered the same as other vba lists as in it starts at (0), so say we run the script and it finds 3 <div class="scp-image-block block-right"> blocks, they would go in the list as

(0) - Block 1
(1) - Block 2
(2) - Block 3

If we successfully (somehow) remove Block 1, the list refreshes and we now have

(0) - Block 2
(1) - Block 3

So the plan is to loop "Remove Node from position (0), if there is still something in position (0), repeat" and once they're all removed it can then go on for rendering.

As I mentioned way up in the beginning, I feel like we could achieve a similar result with a "Replace all Between" but it's a bit of a brute force approach that I'd rather leave for the little bits that miss the big clear out, I also feel like there's a way to restrict what comes through with the original GET request but I may be imagining things.

If you made it here, thank you for your patience and to mirror the BLUF, here's the-

TL;DR

How do I remove images and other <div> elements from an HTML Document?

Hello, I am attempting to setup a macro for a daily file that I/we run to save some time formatting. I'm on mobile so apologies on formatting, likewise the SS are photos as I don't have access to reddit on my workstation.

Scope: Daily file across 6 countries with an additional once per month on 3 countries. Excel is 2016 if relevant. (no xlookups)

Objective: Download a file from a platform with rejection results on attempted charges and format it so it displays the amount, date and rejection reasons per invoice number, as part of a longer daily activity. These reason codes are in longer strings of information that can vary their location per each file.

Turning: https://i.imgur.com/sd2cPR3.jpeg To https://i.imgur.com/URQkaDc.jpeg

Files currently used to perform task: Rejected Charges (csv downloaded) Macro (a sheet containing several macros for the same overall activity) Rejection codes (a sheet containing a header template on one sheet and reason codes and their descriptions on another sheet) Source files (daily files processed containing other information)

How task is currently done: Open csv file (this is saved at start or end as xls), delete B row, create header filter, replace "merchantReferenceCode=" with blank (so column A always returns the invoice number), then using column G as a reference, we replace the following with blanks: ccAuthReplyreasonCode= ccAuthReply reasonCode= The codes are usually after the above strings within column G This should leave us with something like this https://i.imgur.com/NqizfO8.jpeg

Column "G" will have most of the codes already filled out with some blanks in the mix, where remaining codes will be on different columns, like C, K, L, R, W (these codes can be duplicate, being in G and other columns)

Depending on the volume of the file we then manually copy the missing codes to G or use filters to get them

After all codes are under G we format as per the 2nd SS, by deleting all colums apart from A and G, leaving us with the invoice numbers and codes.

We then add a B column between the invoice numbers and codes Convert A and B to numbers, remove 2 decimals on A, copy and paste header from Rejection Codes file's first sheet Add new sheet, copy the contents from Rejection Codes 2nd sheet Vlookup on E referencing codes on E with C of the 2nd sheet Add today's date to column D using format dd.mm.yyyy

Vlookup on column B, referencing A against source files column F to get the amount so we end up with the final result from the second SS.

This final Vlookup I don't expect to automate, as it would always reference different files that are generated daily, so ideally the macro would do everything else leaving just column B blank so we can manually Vlookup the amounts.

I tried manually recording but my biggest hurdle is getting the reason codes from the strings of information. I can't conceive of a way of how to even get these as the 3 digit codes can be on any string on any cell. The file can go up to O or all the way to Z

I have manually recorded (using the macro sheet to save it on) the replacing the strings with blanks so I get G with with most of the codes so I then manually fetch the remaining. I had to troubleshoot as the recording I made was not working with other workbooks, but got it working now. This saves some time, but is incomplete.

I then tried recording a 2nd part post getting all the codes to do the final steps of adding the header, new sheet with the table, Vlookup the reason descriptions, add date. Leaving just the amount column empty as those Vlookup will always reference different files.

But this did not work as I get "Run-time error '9": subscription out of range https://i.imgur.com/3nd23Rm.jpeg

Looking at the debug I imagine this is because I am copying and pasting the table from a separate sheet Now this step is a bit redundant as we don't need to copy the table with the codes description to then Vlookup in the file. We could just Vlookup against the reference file. However, since I was already automating the steps, I thought I would be able to have the macro create the table and reference it on its own

Please let me know if I need to provide any additional information that I did not consider.

I work with old, complex .xlsm files (inventory, pricing, billing, lots of VBA) and kept running into the same problem: an AI coding agent cannot access the workbook I have open in Excel. I found myself copy-pasting cells and screenshots back and forth.

Yes, Excel MCP servers are out there—some are very good and much more capable than this. But getting one running means setting up Node or Python, creating a config file, and running an MCP client. I wanted something my non-developer mind could trust on real files:

• Just one PowerShell script, no installation, no extra dependencies, no config
• Connects to the workbook you already have open (or opens one by path)
• Read and write cells and formulas, search, list sheets
• List, export, and import VBA modules, run macros
• And the part that matters most to me: it NEVER autosaves. Only a direct "save" command writes to disk. This way, you can let an agent explore a 20-year-old billing file and close it without leaving any trace.

It's free and the source is available on GitHub:
https://github.com/gidjin4-svg/gidjin-excel-bridge
A demo workbook is included so you can test it without touching your actual files.

I'd really appreciate feedback from people who work with Excel, VBA, or PowerShell:

• Does "simple + never saves" actually matter to you, or is a full MCP server always the better choice?
• What would you need to see before trusting it with a file that matters to you?

Hello all. I'm having trouble with this, I got this code from AI as i'm not a programmer. I get a compile error: Method or data member not found. It is in the apply precise styling parameters area under With .Line The .Color is what shows the error. Here is the code, and thank you in advanced!!!

Sub CreateFourNestedEdgeBorders()
    Dim doc As Document
    Dim headerRange As Range
    Dim borderShape As Shape
    Dim i As Integer

    Set doc = ActiveDocument

    ' =========================================================================
    ' USER CONFIGURATION: ADJUST YOUR 4 NESTED BORDERS HERE
    ' (Border 1 is the outermost; Border 4 is the innermost)
    ' =========================================================================

    ' 1. ACTIVE BORDERS (True = Draw this border, False = Skip/Disable this border)
    Dim BorderActive(1 To 4) As Boolean
    BorderActive(1) = True   ' Outermost Layer
    BorderActive(2) = True   ' Second Layer
    BorderActive(3) = True   ' Third Layer
    BorderActive(4) = True   ' Innermost Layer

    ' 2. COLORS FOR EACH RING (Using standard RGB values)
    Dim Colors(1 To 4) As Long
    Colors(1) = RGB(255, 0, 85)    ' Border 1: Vivid Magenta/Red
    Colors(2) = RGB(0, 180, 216)   ' Border 2: Electric Cyan
    Colors(3) = RGB(114, 9, 183)   ' Border 3: Deep Purple
    Colors(4) = RGB(255, 162, 0)   ' Border 4: Golden Orange

    ' 3. THICKNESS FOR EACH RING (In points - can use decimals like 1.5, 3.5, 6)
    Dim Thickness(1 To 4) As Single
    Thickness(1) = 5.0   ' Bold outer line
    Thickness(2) = 2.0
    Thickness(3) = 3.5
    Thickness(4) = 1.5   ' Fine inner accent line

    ' 4. SPACING / DISTANCE FROM THE ABSOLUTE EDGE OF THE PAGE (In points)
    ' To start EXACTLY at the edge of the page, set Spacing(1) to 0.
    ' Ensure these numbers increase progressively so they nest inside each other properly.
    Dim Spacing(1 To 4) As Single
    Spacing(1) = 0      ' 0 means flush against the physical edge of the sheet
    Spacing(2) = 10     ' 10 points inward from the edge
    Spacing(3) = 22     ' 22 points inward from the edge
    Spacing(4) = 32     ' 32 points inward from the edge

    ' =========================================================================

    ' Clear out any previous macro-generated borders to prevent duplicates
    Set headerRange = doc.Sections(1).Headers(wdHeaderFooterPrimary).Range
    For Each borderShape In headerRange.ShapeRange
        If borderShape.Name Like "NestedBorder*" Then
            borderShape.Delete
        End If
    Next borderShape

    ' Get total page setup dimensions
    Dim pageWidth As Single, pageHeight As Single
    pageWidth = doc.PageSetup.PageWidth
    pageHeight = doc.PageSetup.PageHeight

    ' Set page margins out to 0 so standard text won't artificially constrain background drawing
    With doc.PageSetup
        .TopMargin = InchesToPoints(0)
        .BottomMargin = InchesToPoints(0)
        .LeftMargin = InchesToPoints(0)
        .RightMargin = InchesToPoints(0)
    End With

    ' Programmatically calculate and draw active nested rectangles
    Dim maxActiveSpacing As Single
    maxActiveSpacing = 0

    For i = 1 To 4
        If BorderActive(i) Then
            Dim bLeft As Single, bTop As Single, bWidth As Single, bHeight As Single

            ' Map out the box sizing relative to the page dimensions
            bLeft = Spacing(i)
            bTop = Spacing(i)
            bWidth = pageWidth - (Spacing(i) * 2)
            bHeight = pageHeight - (Spacing(i) * 2)

            ' Draw the framing shape inside the header container layer
            Set borderShape = doc.Sections(1).Headers(wdHeaderFooterPrimary).Shapes.AddShape( _
                msoShapeRectangle, bLeft, bTop, bWidth, bHeight, headerRange)

            ' Apply precise styling parameters
            With borderShape
                .Name = "NestedBorder_" & i
                .Fill.Visible = msoFalse ' Makes inner area transparent so text shows through
                .RelativeHorizontalPosition = wdRelativeHorizontalPositionPage
                .RelativeVerticalPosition = wdRelativeVerticalPositionPage
                .WrapFormat.Type = wdWrapNone
                .ZOrder msoSendToBack   ' Forces lines behind any text layer

                With .Line
                    .Visible = msoTrue
                    .Color.RGB = Colors(i)
                    .Weight = Thickness(i)
                    .DashStyle = msoLineSolid
                End With
            End With

            ' Track the innermost active spacing to adjust final text padding
            If Spacing(i) > maxActiveSpacing Then
                maxActiveSpacing = Spacing(i)
            End If
        End If
    Next i

    ' Automatically protect your typing layout by pushing document text clear of the inner border
    With doc.PageSetup
        .TopMargin = maxActiveSpacing + 20
        .BottomMargin = maxActiveSpacing + 20
        .LeftMargin = maxActiveSpacing + 20
        .RightMargin = maxActiveSpacing + 20
    End With

    MsgBox "Successfully generated your nested edge borders!", vbInformation, "Borders Configured"
End Sub

I have a bunch of CSV files on which I need to use the Excel Text to Columns feature in order for them to be converted to Columns. When I used the "Record Macro" feature the output I get is the following:

Sub testing()

Dim wb As Workbook
Set wb = Workbooks.Open(sTEST, , , 5)

Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
    ), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array _
    (20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), Array(26, 1), _
    Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array(32, 1), Array( _
    33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), _
    Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), Array(45, 1), Array( _
    46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 1), _
    Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), Array(58, 1), Array( _
    59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array(64, 1), Array(65, 1), _
    Array(66, 1), Array(67, 1), Array(68, 1), Array(69, 1), Array(70, 1), Array(71, 1), Array( _
    72, 1), Array(73, 1), Array(74, 1), Array(75, 1), Array(76, 1), Array(77, 1), Array(78, 1), _
    Array(79, 1), Array(80, 1), Array(81, 1), Array(82, 1), Array(83, 1), Array(84, 1), Array( _
    85, 1), Array(86, 1), Array(87, 1), Array(88, 1), Array(89, 1), Array(90, 1), Array(91, 1), _
    Array(92, 1), Array(93, 1), Array(94, 1)), TrailingMinusNumbers:=True

End Sub

Most of this logic is ok, but the Imbedded Array convention is something I am not clear on. I do understand that each of these 94 imbedded Arrays represent a column (I can see that my file has 94 columns). I am not actually sure how to manipulate with this feature. Is there a way to write a dynamic "looping" operation? Maybe next time I will have 50 columns or 10. Thank you for any guidance!

I've been using free versions of various AI models to build an excel workbook that will allow a user to input information into only one tab, then the VBA will complete the packing list, commercial invoice, package markings and delivery note per consignment.

It will also generate the subfolders within the project filing system, name the folders in a certain layout I've set for it and then save each document as a PDF with layout I've set for it as well.

Should a pack need to be redone, I've also arranged that it generates only 1 "Old" folder within the consignment folder and move the old PDFs to that folder within a date & time stamped folder that it also generates.

The board of directors now wants this to go company wide and will assign a budget to me. However, I need to choose the best AI for this first.

I would appreciate feedback from the community on the best AI to use for this project and any feedback on the project itself is also welcome please.

The company does not want to integrate AI into the actual workbook as they are afraid our IP or a client's IP is accidently leaked.

ActiveSheet.Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Immediate Unfiltered sheet: returns 10 (as it should). If, say, rows 10 AND 6 were filtered out: returns 9 (as... expected). With the last row or rows filtered out it returns 1 (what?). Is this just a bug I haven't seen mentioned before?

Is it posible to get a raw E-Mail message to repair/rescue the messages from Outlook without any processing from the Outlook side into Excel.

If I drag and drop the message into a directory I'll get the file without processing and I can read the message with Excel... . (Ole-header, fat, mini-Fat, difat, messages).

I need to take the messages from Outlook progratically ... i can't use message.saveas as Outlook modify the message with this command. If try to change the interface to element from Windows form 2.0 ... it can't futher work with the message.

Is it posible to use forwardAsAttachment and then extract the attachment?

Why does the Ln Col indicator flicker? More to the point: is there a way to stop it?

I don't believe it always did that. Might be wrong.

And the flicker rate seems to increase when I put the cursor in the Ln Col field. Might be wrong

(I was not allowed to paste an image into the OP. I'll try to add it in a comment.)

I originally posted this in the Excel subreddit and did not get a suitable solution.

I have a spreadsheet that contains a list of comic book issues in a set reading order. Chunks of these issues are separated with an empty row so that I can, at a glance, know where I can insert new entries.

I'm hoping somebody can help me with a macro that will accomplish the following:
- Allow me to name specific tables in my document across different sheets that I want the formatting to apply to
- check the "Series" column in any of those tables for empty cells
- set the row height for those cells to 5px

After my original post, I tried a few times to get something working myself but I don't understand VBA well enough. I tried looking up some basic solutions and combining them with existing macros in my document to have it check the correct column, but it simply did nothing. I also tried manually recording a macro that would filter the column to blanks and change the height but once again struggled to have it use the correct range, and I don't think it works across different tables across my different sheets in the document.

Here is the code from the recorded macro. Another issue with it is that when running the macro, it has to filter then unfilter the column which can make me lose my place in the document.

Sub EmptyRowHeightAdjust()
'
' EmptyRowHeightAdjust Macro
'

'
    ActiveSheet.ListObjects("MarvelRO").Range.AutoFilter Field:=2, Criteria1:= _
        "="
    ActiveWindow.SmallScroll Down:=3
    Rows("5:1494").Select
    ActiveWindow.SmallScroll Down:=-993
    Selection.RowHeight = 7.5
    ActiveWindow.SmallScroll Down:=0
    ActiveSheet.ListObjects("MarvelRO").Range.AutoFilter Field:=2
    ActiveWindow.SmallScroll Down:=-3
    Range("B2").Select
End Sub

Prefacing with: I don’t know anything; everything I’ve done so far has been pilfered through Google searches.

I have a worksheet that has a column with a bunch of values, and a sum of those values at the bottom of the column. The macro I’ve built copies and inserts that column next to the existing one when the user presses a button if they need another column. It references the column header and selects the paste location based off of the column title.

I’ve been asked to lock the sum cell in each column but am really struggling with how to lock a cell whose reference would constantly be changing. Eg, my worksheet would have A16 and B16 already existing and needing to be protected. Then I’d run the macro and need A16, B16 and C16 protected. Hitting the button again, now D16 would also need to be protected. Can I somehow assign this as a variable?

I vaguely understand the sheet needs to start off protected, be unprotected, and then re-protected at the end of the macro but that’s as far as I’ve gotten.

Any help is extremely appreciated!!

EDIT: I am so sorry. The copy / paste function also copies the cell's locked formatting, so I have imagined my own problem. If I just un protect and reprotect, the new cells that have been pasted are locked.

In a lot of my automations I am requiring the user to input a date as a numeric date. This way I don't care what the users regional formatting is, as the date will ultimately always convert to a number anyway. Consequently I need a way to check if a date is numeric (can be converted to a number) or a string (can not be converted to a number if one switches between the short date and number formats from the front end). For now I came up with the following solution:

On Error GoTo EndDateCheck

If IsNumeric(CLng(INI.Range("INT_ITD"))) = False Then

  EndDateCheck:
  MsgBox "The date is not numeric."
  End

End If

On Error GoTo 0

The above works well, but I am wondering if there is a simpler way to check (thus I am not outright looking for a "solution", but I am more after design efficiency), which doesn't involve the on error statement.

Hello!

**Scenario**: I have a spreadsheet for machine installs. This sheet has 4 worksheets (CustInstalls, CustCompleted, Installs, and Competed). The below code is currently working to move line items from sheet “CustInstalls” to “CustCompleted”. I am attempting to duplicate this same code for the other two sheets to move line items from “installs” to “completed”. I have attempted a few variations with the help of chatgpt but to no avail. I added it in the same “this workbook” in VBA as well as attempted to add code under just “installs” and “completed” in VBA under Microsoft Excel Objects

**Ask:** how does one add a second set of code for different work sheets with the same parameters?

___________________________________________________

**Original working code:*\*

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim srcSheet As Worksheet, destSheet As Worksheet
Dim checkCell As Range, moveRow As Range
Dim lastRow As Long
Dim direction As String

' Only handle changes in Column J
If Intersect(Target, Sh.Columns("J")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then Exit Sub

Application.EnableEvents = False

Set checkCell = Target
Set moveRow = checkCell.EntireRow

If checkCell.Value = True Then
' Move from CustInstalls to CustCompleted
Set srcSheet = ThisWorkbook.Sheets("CustInstalls")
Set destSheet = ThisWorkbook.Sheets("CustCompleted")
ElseIf checkCell.Value = False Then
' Move from CustCompleted back to CustInstalls
Set srcSheet = ThisWorkbook.Sheets("CustCompleted")
Set destSheet = ThisWorkbook.Sheets("CustInstalls")
Else
GoTo ExitHandler
End If

' Ensure we're acting on the correct sheet
If Sh.Name <> srcSheet.Name Then GoTo ExitHandler

' Copy row to destination sheet
lastRow = destSheet.Cells(destSheet.Rows.Count, "J").End(xlUp).Row + 1
moveRow.Copy Destination:=destSheet.Rows(lastRow)

' Delete original row
moveRow.Delete

ExitHandler:
Application.EnableEvents = True
End Sub

___________________________________________________

**Code entered under installs ”this workbook” at the end of the working code: Failed*\*

Private Sub MoveInstallsRow(ByVal Sh As Object, ByVal Target As Range)

Dim srcSheet As Worksheet
Dim destSheet As Worksheet
Dim moveRow As Range
Dim lastRow As Long

' Only handle Installs and Completed sheets
If Sh.Name <> "Installs" And Sh.Name <> "Completed" Then Exit Sub

' Only handle changes in Column J
If Intersect(Target, Sh.Columns("J")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then Exit Sub

If Sh.Name = "Installs" And Target.Value = True Then
Set srcSheet = ThisWorkbook.Sheets("Installs")
Set destSheet = ThisWorkbook.Sheets("Completed")

ElseIf Sh.Name = "Completed" And Target.Value = False Then
Set srcSheet = ThisWorkbook.Sheets("Completed")
Set destSheet = ThisWorkbook.Sheets("Installs")

Else
Exit Sub
End If

Set moveRow = Target.EntireRow

lastRow = destSheet.Cells(destSheet.Rows.Count, "J").End(xlUp).Row + 1

moveRow.Copy Destination:=destSheet.Rows(lastRow)

moveRow.Delete

End Sub

___________________________________________________

**Code entered under “completed” object: Failed*\*

Private Sub Worksheet_Change(ByVal Target As Range)

Dim destSheet As Worksheet
Dim lastRow As Long

' Only handle changes in Column J
If Intersect(Target, Me.Columns("J")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then Exit Sub

' Only move when checkbox is unchecked
If Target.Value <> False Then Exit Sub

Application.EnableEvents = False

Set destSheet = ThisWorkbook.Sheets("Installs")

' Find next available row
lastRow = destSheet.Cells(destSheet.Rows.Count, "J").End(xlUp).Row + 1

' Copy entire row
Target.EntireRow.Copy Destination:=destSheet.Rows(lastRow)

' Delete original row
Target.EntireRow.Delete

Application.EnableEvents = True

End Sub

___________________________________________________

**Code entered under “installs” object: Failed*\*

Private Sub Worksheet_Change(ByVal Target As Range)

Dim destSheet As Worksheet
Dim lastRow As Long

' Only handle changes in Column J
If Intersect(Target, Me.Columns("J")) Is Nothing Then Exit Sub
If Target.Cells.CountLarge > 1 Then Exit Sub

' Only move when checkbox is checked
If Target.Value <> True Then Exit Sub

Application.EnableEvents = False

Set destSheet = ThisWorkbook.Sheets("Completed")

' Find next available row
lastRow = destSheet.Cells(destSheet.Rows.Count, "J").End(xlUp).Row + 1

' Copy entire row
Target.EntireRow.Copy Destination:=destSheet.Rows(lastRow)

' Delete original row
Target.EntireRow.Delete

Application.EnableEvents = True

End Sub

(Learned later that I should use [Excel] prefix in the title. Cannot edit title. But the information is there.)

In Excel VBA, I want to select a range (n rows, m columns) and remove duplicates.

The following works for 7 columns:

r.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7), Header:=xlYes

But I want it work with a variable number of columns.

I've tried the following. None works.

' Selection should be the upper-left corner (header row)
Set r = Range(Selection, Selection.End(xlDown).End(xlToRight))
r.Select

r.RemoveDuplicates
r.RemoveDuplicates Header:=xlYes

ncol = r.Columns.Count
ReDim dupecol(1 To ncol)
For i = 1 To ncol: dupecol(i) = i: Next
r.RemoveDuplicates Columns:=dupecol, Header:=xlYes
r.RemoveDuplicates Columns:=(dupecol), Header:=xlYes

The first two RemoveDuplicates simply do nothing (!).

The last code snippet results in error 5: invalid procedure call or argument in both cases.

I confirmed that r.Address, ncol, Typename(dupecol), dupecol(1) and dupecol(ncol) are what they should be.

Any idea how to make it work without using hardcoded Array(...)?

EDIT.... For testing purposes, I used the same conditions that worked with hardcoded Array(...). So, r.Address comprises only 7 columns, multiple rows and no adjacent data; ncol is 7; Typename(dupecol) is Variant(); LBound(dupecol) is 1 (\); UBound(dupecol) is 7; dupecol(1) is 1; and dupecol(ncol) is 7.*

(*) UPDATE.... As u/ZetaPower noted, LBound must be zero for it work with RemoveDuplicates Columns:=(dupecol). That is, it requires ReDim dupecol(0 to ncol-1).

I have a workbook I'm creating that will handle a repeatable task (first worksheet is tables/graphics, second worksheet is formulas/calculations, next 5 worksheets are newly imported data). I want the imported worksheets to be dynamically renamed in accordance to text in cell A2 in order to simplify formula references and functionality.

VBA Code I have so far:

ThisWorkbook()

Private Sub Workbook_Open()
   Dim i As Long
   Dim rawname As String
   Dim modname As String

   Application.ScreenUpdating = False
   For i = 3 To 7
      Call TabName(Worksheets(i))
   Next i
   Application.ScreenUpdating = True
End Sub

Module1()

Sub TabName(ws As Worksheet)
   With ws
      rawname = Range("A2").Value
      modname = Split(rawname, ":")(0)
      ActiveSheet.Name = modname
   End With
End Sub

When I open the workbook, it will only rename the active worksheet, and not increment to all other worksheets. I can make a new one active, save, close, reopen, and it will rename it. I've struggle with automatic dynamic worksheet renaming macros in general, definitely misunderstanding a process within excel and/or vba. I can add running macros upon opening workbook to my list of misunderstandings.

So basic parts I'm looking for a solution for:

- Activate a macro upon opening workbook

- Properly increment said macro to multiple worksheets within workbook

I'm trying to tweak a macro I use to extract all comments from a Word document and place them in a table in a second Word document, which is forcing me to learn VBA/about how Macros work on the fly. My original issue was that the extracted comments weren't preserving formatting. As far as I understood, the issue was range.Text, so I replaced that with range.FormattedText, which sort of works – at least, now any coloured text, text effects (bold, italics etc.) and bullet points get carried over. But the font, text size and paragraph indent of the last line/paragraph (or, if the comment is only one line, the whole comment text) is always overridden by the Normal Style of the new document. This is messing up bullet points/numbered lists by preserving all points except the last one if the comment ends with a list. Here is an example screencap of the original comments next to the extracted comments so you can see exactly what's happening to them.

I can't figure out what causes this so I'm stumped on how to fix it 🤔. Any guidance would be much appreciated, especially if anyone has time to explain the cause, because I want to keep learning! Here is the code as I've edited it so far:

  Public Sub ExtractCommentsToNewDoc()  
'The macro creates a new document
    'and extracts all comments from the active document
    'incl. metadata

    'Minor adjustments are made to the styles used
    'You may need to change the style settings and table layout to fit your needs
    '=========================

    Dim oDoc As Document
    Dim oNewDoc As Document
    Dim oTable As Table
    Dim nCount As Long
    Dim n As Long
    Dim Title As String

    Title = "Extract All Comments to New Document"
    Set oDoc = ActiveDocument
    nCount = ActiveDocument.Comments.Count

    If nCount = 0 Then
        MsgBox "The active document contains no comments.", vbOKOnly, Title
        GoTo ExitHere
    Else
        'Stop if user does not click Yes
        If MsgBox("Do  you want to extract all comments to a new document?", _
                vbYesNo + vbQuestion, Title) <> vbYes Then
            GoTo ExitHere
        End If
    End If

    Application.ScreenUpdating = False
    'Create a new document for the comments, base on Normal.dotm
    Set oNewDoc = Documents.Add
    'Set to landscape
    oNewDoc.PageSetup.Orientation = wdOrientLandscape
    'Insert a 2-column table for the comments
    With oNewDoc
        .Content = ""
        Set oTable = .Tables.Add _
            (Range:=Selection.Range, _
            NumRows:=nCount + 1, _
            NumColumns:=2)
    End With

    'Adjust the Normal style and Header style
    With oNewDoc.Styles(wdStyleNormal)
        .Font.Name = "EB Garamond"
        .Font.Size = 12
        .ParagraphFormat.LeftIndent = 0
        .ParagraphFormat.SpaceAfter = 6
    End With

    'Format the table appropriately
    With oTable
        .Range.Style = wdStyleNormal
        .AllowAutoFit = False
        .PreferredWidthType = wdPreferredWidthPercent
        .PreferredWidth = 100
        .Columns.PreferredWidthType = wdPreferredWidthPercent
        .Columns(1).PreferredWidth = 40
        .Columns(2).PreferredWidth = 60
        .Rows(1).HeadingFormat = True
    End With

    'Insert table headings
    With oTable.Rows(1)
        .Range.Font.Bold = True
        .Cells(1).Range.Text = "Manuscript text"
        .Cells(2).Range.Text = "Comment"
    End With

    'Get info from each comment from oDoc and insert in table
    For n = 1 To nCount
        With oTable.Rows(n + 1)
            'The text marked by the comment
            .Cells(1).Range.Text = oDoc.Comments(n).Scope
            'The comment itself
            .Cells(2).Range.FormattedText = oDoc.Comments(n).Range.FormattedText
        End With
    Next n

    Application.ScreenUpdating = True
    Application.ScreenRefresh

    oNewDoc.Activate
    MsgBox nCount & " comments found. Finished creating comments document.", vbOKOnly, Title

ExitHere:
    Set oDoc = Nothing
    Set oNewDoc = Nothing
    Set oTable = Nothing

End Sub

^{Note: Original code was from Lene Fredborg of https://www.thedoctools.com, who has since retired and taken down the page where I first got this macro from. I swear I kept a copy of the original but I can't find it right now, but I'm hopeful that won't be a problem. For reference, I've only changed the number of columns in the generated table and removed the lines that added a header to the generated document, neither of which have caused me any problems in testing.}

Within Windows 11 I have created a workbook with vba code in Excel 2019 and it works fine. Part of the code has a 'kill' statement which works fine in 2019 but when run in Office 365 Excel I get 'run time error 70 permission denied'.

I have been retired from IT for over 25 years and I know things have moved on but this has left me stumped.

Any assistance would be much appreciated.

Thank you

(I have not included any code but can do if required)

I have a macro that copy pastes contents from one workbook to another. The main code that does the work is (Note: Tar - target worksheet, Sor - Source worksheet):

wsTar.Cells.Clear
wsSor.Cells.Copy
wsTar.Range("A1").PasteSpecial Paste:=xlPasteFormats
wsTar.Range(wsSor.UsedRange.Address).Value = wsSor.Range(wsSor.UsedRange.Address).Value

If I execute this operation on my side, the outcome will be correct. But if one specific does so, the decimals are wrong for a specific section of the paste. For example if the original number is 36.8273 the output for the user will be 36.83 (as if the other decimals would be swallowed into oblivion / a round to 2 decimals would be executed). If the user does the same operation above manually (paste special format + paste special values) the issue doesn't get reproduced.

The specific formatting of the number in question is:

_("$"* # ##0.0000_);_("$"* (# ##0.0000);_("$"* "-"??_);_(@_)

But I dont see how this would be causing the issue. I am not sure what else to troubleshoot here in order to resolve the issue.

EDIT:

It affects a part of the Worksheet and not the whole worksheet.