I have a VBA-enabled workbook used as an estimating template. I often copy from previous versions to new templates, and have finally identified an error I've seen but am unable to figure out how to fix it. To re-create the error, both workbooks are identical with the exception of the source workbook/tab name (columns, names, cell types, etc. are all identical). The script (I borrowed from something I found online a few years ago) checks when a paste happens and does an undo - pastespecial with the goal of not messing up formatting (if for example, copying text from a website that has different font style, or cell borders from a price list provided in Excel). Paste from website seems to work fine, this error only comes up when copying from another Excel workbook and copying a range. Example range: (B15:N54) with a mix of cell formats including general, number, and text.
The produced error is Run-time error '1004': The specified dimension is not valid for the current chart type.
If I click "End" on the error popup, the data pastes to a different tab (where it isn't supposed to go), then if I try to paste again in the desired location it works.
Research so far points to maybe mismatch default chart type between workbooks, but I'm not trying to use charts.
I have tried changing the "general" formatted cells to a number or text to be more specific, same result.
I have tried ensuring the same size of range is selected on the target sheet as the copied data (and same cell formats per column), same result.
This borrowed code is a bit past my skill level, so help in what direction to chase would be appreciated.
'manage paste command to maintain formatting If Sheets("Job Info").Range("A6").Value = 0 Then Exit Sub Else If ActiveSheet.Range("A1") = ("MaintainFormatting") Then Dim UndoList As String Application.ScreenUpdating = False Application.EnableEvents = False On Error GoTo Whoa '~~> Get the undo List to capture the last action performed by user UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1) '~~> Check if the last action was not a paste nor an autofill If Left(UndoList, 5) <> "Paste" And UndoList <> "Auto Fill" _ Then GoTo LetsContinue '~~> Undo the paste that the user did but we are not clearing '~~> the clipboard so the copied data is still in memory Application.Undo If UndoList = "Auto Fill" Then Selection.Copy '~~> Do a pastespecial to preserve formats On Error Resume Next '~~> Handle text data copied from a website Target.Select ActiveSheet.PasteSpecial Format:="Text", _ DisplayAsIcon:=False Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False On Error GoTo 0 '~~> Retain selection of the pasted data Union(Target, Selection).Select Target.EntireRow.AutoFit LetsContinue: Application.ScreenUpdating = True Application.EnableEvents = True Exit Sub Whoa: 'MsgBox Err.Description Resume LetsContinue End If End If