PasteSpecial from other workbook generates chart type error

  • 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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!