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
- 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
- If UndoList = "Auto Fill" Then Selection.Copy
- '~~> Do a pastespecial to preserve formats
- On Error Resume Next
- '~~> Handle text data copied from a website
- ActiveSheet.PasteSpecial Format:="Text", _
- Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
- SkipBlanks:=False, Transpose:=False
- On Error GoTo 0
- '~~> Retain selection of the pasted data
- Union(Target, Selection).Select
- Application.ScreenUpdating = True
- Application.EnableEvents = True
- Exit Sub
- 'MsgBox Err.Description
- Resume LetsContinue
- End If
- End If