Macro Error 1004

[JB63]

When I try a "copy paste transposed" from one sheet to another workbook I
want to paste into a new blank row at the end of the sheet, I have tried
using the code
[ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select] and other variants
suggested in article Q291308 but continue to get error 1004. any help is
greatly appreciated. below is the complete code for my macro

Range("AA7:AA8,AA11:AA14,AA17:AA26,AA29:AA39,AA42:AA48,AA51:AA52").Select
Range("AA51").Activate
Selection.Copy

Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:="U:\My Documents\Testing.xls"

ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True

Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

Columns("AA:AA").Select
Selection.ClearContents
Range("Z1").Select

 

[Jim Thomlinson]

In the line
ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select
you have
x1Down

That should be
xlDown

Note XL not X1

 

[JB63]

Thank you Jim I am trying not to admit I need some reading glasses, your help
is much appreciated.

In the line
ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select
you have
x1Down

That should be
xlDown

Note XL not X1
--
HTH...

Jim Thomlinson

When I try a "copy paste transposed" from one sheet to another workbook I
want to paste into a new blank row at the end of the sheet, I have tried
using the code
[ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select] and other variants
suggested in article Q291308 but continue to get error 1004. any help is
greatly appreciated. below is the complete code for my macro

Range("AA7:AA8,AA11:AA14,AA17:AA26,AA29:AA39,AA42:AA48,AA51:AA52").Select
Range("AA51").Activate
Selection.Copy

Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:="U:\My Documents\Testing.xls"

ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True

Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

Columns("AA:AA").Select
Selection.ClearContents
Range("Z1").Select

 

[Jim Thomlinson]

Note that that line will still die if the a column is empty... I would be
more inclined to try

activesheet.cells(rows.count, "A").End(xlUp).offset(1,0).select

Coming up from the bottom is lees prone to crash.
--
HTH...

Jim Thomlinson

Thank you Jim I am trying not to admit I need some reading glasses, your help
is much appreciated.

In the line
ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select
you have
x1Down

That should be
xlDown

Note XL not X1
--
HTH...

Jim Thomlinson

When I try a "copy paste transposed" from one sheet to another workbook I
want to paste into a new blank row at the end of the sheet, I have tried
using the code
[ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select] and other variants
suggested in article Q291308 but continue to get error 1004. any help is
greatly appreciated. below is the complete code for my macro

Range("AA7:AA8,AA11:AA14,AA17:AA26,AA29:AA39,AA42:AA48,AA51:AA52").Select
Range("AA51").Activate
Selection.Copy

Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:="U:\My Documents\Testing.xls"

ActiveSheet.Range("a1").end(x1Down).Offset(1,0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True

Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close

Columns("AA:AA").Select
Selection.ClearContents
Range("Z1").Select