Run Time Error 1004 when copying data

Hi All

I have written the following macro in Excel 2010 to copy data and then transpose the copy using paste special. It runs fine the first time but as soon as you delete the result and run it again it gives the Run time Error 1004 - Application Defines or object Defined error.

If you select end on the run time error window and run it again it works fine. The error occurs at the line in red.

Sub Sample()
i = Application.WorksheetFunction.CountA(Sheet10.Range("A:A"))
j = Application.WorksheetFunction.CountA(Sheet1.Range("A:A"))


Sheet1.Range("H1", Sheet1.Cells(j, 8)).Copy
Sheet10.Range("A1").PasteSpecial xlPasteValues
Sheet10.Range("A:A").RemoveDuplicates (1)

Sheet10.Activate
Sheet10.Range("A1").Select
Sheet10.Range(Sheet10.Cells(2, 1), Sheet10.Cells(i, 1)).Copy

Sheet11.Activate
Sheet11.Range("A1").Select
Sheet11.Range("A1").PasteSpecial xlPasteAll, , , True



End Sub

Any assistance as to the cause of this error would be appreciated.

Regards

Sharksfan88

Maybe a sample workbook ...

Though, thinking about it, if you clear the results, won't j = 0 next time you run it?

OK, so where does i come from?

By the way, to comply with forum rules, you should add Code Tags to your code excerpt in post #1.

Regards, TMS

Code Tags are [Code ]Please Login or Register to view this content.[/Code]

Thanks for the rep. You need to look at the forum rules for information about Code Tags (Rule 3, to be specific)