none
Problem with a simple recording macro......(copying a table from one excel tab to another) RRS feed

  • Question

  • Hi i have a simple table that basically has values via a vlookup.


    sometimes the content needs to be edited, so i recorded a simple macro where i copied the table from 1 tab and pasted as values into another tab. (where the table is same layout)


    it was working fine but now i get run time error 1004.....application defined or object defined error.


    i am not good with macros - hence use recorded one. 


    the code is as follows: the arrow/error seems to be for the line in red. but i dont know what the exact issue is????


         ActiveSheet.Unprotect
        ActiveCell.Offset(4, -19).Range("A1:T16").Select
        Selection.Copy
        Sheets("Report Template (2)").Select
        ActiveCell.Offset(-9, -24).Range("A1:T16").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
        ActiveWindow.SmallScroll Down:=-24
        ActiveCell.Range("A1:T1").Select
        Sheets("Report Template").Select
        ActiveCell.Offset(6, 8).Range("A1:L1").Select
        ActiveWindow.SmallScroll Down:=-39
        Application.CutCopyMode = False
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        Sheets("Report Template (2)").Select
    End Sub

    Tuesday, September 11, 2018 1:07 PM

All replies

  • ActiveCell.Offset(-9, -24) is the cell 9 rows up and 24 columns to the left of the active cell. If the active cell is in or above row 9, or in or to the left of column X (the 24th column), this causes an error.

    Can you omit the part ActiveCell.Offset(-9, -24). or will that cause other problems?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, September 11, 2018 2:43 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.


    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.


    Wednesday, September 12, 2018 2:35 AM
  • Thanks Hans Vogelaar. @Gooner7, I think that Hans made that clear as well. You can omit the part to test it as Hans mentioned.

    Also, for the function, here is a link for your reference.

    ActiveCell.Offset

    Using Range.Offset in Excel VBA

    Hopefully it helps you.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, September 12, 2018 10:29 AM
    Moderator
  • hi sorry about posting in wrong area.

    i found answer in another thread:

     It is much better to control where the paste is performed by recording your macro in absolute mode - un-highlight "Use Relative References" when you record your macro:

    viola.

    Wednesday, September 12, 2018 11:29 AM
  • Thanks for you reply. Please remember to mark the replies as answers as you provide solution. Please help us close the thread.

    Thanks for you understanding.

    Best Regards,
    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Wednesday, September 12, 2018 12:06 PM
    Moderator
  • Hi Gooner7,

    Could you please help us to close the post if you have resolved your issue now. 

    Thanks for your understanding.

    Best Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Thursday, September 13, 2018 12:40 AM
    Moderator