run-time error '1004'

%3CLINGO-SUB%20id%3D%22lingo-sub-2152583%22%20slang%3D%22en-US%22%3Erun-time%20error%20'1004'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152583%22%20slang%3D%22en-US%22%3E%3CP%3Eany%20body%20can%20help%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22run-time%20error'1004'.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256355i9B2052DA619CEF2D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22run-time%20error'1004'.JPG%22%20alt%3D%22run-time%20error'1004'.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20Customer()%0A'%0A'%20Customer%20Macro%0A'%0A%0A'%0A%20%20%20%20Range(%22D3%3AD8%22).Select%0A%20%20%20%20Selection.Copy%0A%20%20%20%20ActiveWindow.ScrollWorkbookTabs%20Sheets%3A%3D1%0A%20%20%20%20Sheet10.Select%0A%20%20%20%20Range(%22B2%3AB3%22).Select%0A%20%20%20%20Selection.End(xlDown).Select%0A%20%20%20%20Range(%22B4%22).Select%0A%20%20%20%20Selection.End(xlDown).Select%0A%20%20%20%20ActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%0A%20%20%20%20Selection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%0A%20%20%20%20%20%20%20%20%3A%3DFalse%2C%20Transpose%3A%3DTrue%0A%20%20%20%20Range(%22B12%22).Select%0A%20%20%20%20Sheet9.Select%0A%20%20%20%20Application.CutCopyMode%20%3D%20False%0A%20%20%20%20Selection.ClearContents%0A%20%20%20%20Range(%22D3%22).Select%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2152583%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152630%22%20slang%3D%22en-US%22%3ERe%3A%20run-time%20error%20'1004'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152630%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696739%22%20target%3D%22_blank%22%3E%40AHMED_KARRAM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20isn't%20really%20necessary%20to%20select%20ranges%20in%20a%20macro.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Customer()%0A'%0A'%20Customer%20Macro%0A'%0A%0A'%0A%20%20%20%20Sheet9.Range(%22D3%3AD8%22).Copy%0A%20%20%20%20Sheet10.Range(%22B4%22).End(xlDown).Offset(1%2C%200).PasteSpecial%20_%0A%20%20%20%20%20%20%20%20Paste%3A%3DxlPasteValues%2C%20SkipBlanks%3A%3DFalse%2C%20Transpose%3A%3DTrue%0A%20%20%20%20Sheet9.Range(%22D3%3AD8%22).ClearContents%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152736%22%20slang%3D%22en-US%22%3ERe%3A%20run-time%20error%20'1004'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F696739%22%20target%3D%22_blank%22%3E%40AHMED_KARRAM%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20data%20below%20B4%3F%20When%20you%20select%20B4%20and%20hit%20Ctrl%2BDown%20Arrow%2C%20does%20it%20take%20you%20to%20the%20last%20row%20of%20the%20spreadsheet%3F%20If%20you've%20already%20selected%20the%20last%20physical%20row%20of%20the%20worksheet%2C%20then%20Offset(1%2C0).Select%20will%20fail%20with%20a%201004%20error%20code.%3C%2FP%3E%3CPRE%3ERange(%22B4%22).Select%0A%20%20%20%20Selection.End(xlDown).Select%0A%20%20%20%20ActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

any body can help

run-time error'1004'.JPG

 

Sub Customer()
'
' Customer Macro
'

'
    Range("D3:D8").Select
    Selection.Copy
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheet10.Select
    Range("B2:B3").Select
    Selection.End(xlDown).Select
    Range("B4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("B12").Select
    Sheet9.Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D3").Select
End Sub
2 Replies

@AHMED_KARRAM 

It isn't really necessary to select ranges in a macro.

Sub Customer()
'
' Customer Macro
'

'
    Sheet9.Range("D3:D8").Copy
    Sheet10.Range("B4").End(xlDown).Offset(1, 0).PasteSpecial _
        Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=True
    Sheet9.Range("D3:D8").ClearContents
End Sub

@AHMED_KARRAM 

 

Is there any data below B4? When you select B4 and hit Ctrl+Down Arrow, does it take you to the last row of the spreadsheet? If you've already selected the last physical row of the worksheet, then Offset(1,0).Select will fail with a 1004 error code.

Range("B4").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select