Excel 2007: Object variable or With block variable not set ERROR 1

[canasta]

Am getting an 'object variable or With block variable not set' error on below part of my code to transpose a column of data in Excel. The error is happening on this line:
.Range("D1").Select

Any ideas what I'm doing wrong? Thank you.

With xSheet
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
.Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With

 

[SkipVought]

Why are not ALL ranges referencing a sheet object? What sheet is the xSheet object? It appears that xSheet is not the active sheet.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[canasta]

Thank you - xSheet is dimmed As Worksheet. I am not sure how to answer, so sorry. I've gotten this far with a mix of recording macros and help menus and googling to find code. In this case I used the macro recorder to see what would happen if I copied a column from the top of the data to bottom, then moved a couple cells to the right and pasted special to transpose. Somewhere along the way it stopped working - due to me 'refining' it a little too much, and now I am trying to start over and just get one piece at a time straight. This piece I simply want to go to the top of column A, scroll down to make sure I've gotten all the text, copy it, paste it on column D in a transposed state. Thanks.

 

[SkipVought]

You must have a statement that assigns a specific sheet object to your xSheet object variable, prior to using it in your code, like

Set xSheet = Worksheets("Some Sheet Name")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[canasta]

Thank you - getting closer, that helped, and now it is stopping on below line. It's copied the column, moved to the cell I need, but not pasting. What have I done wrong? Thanks again for your help.

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

 

[SkipVought]

Please post ALL your code, not just this fragment.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[canasta]

Thanks, I've modified it and here is the entire code. The error message is the one about copy/paste area not being same size and shape, to click on a single cell then paste. I tested it on a smaller dataset and realized I believe the rows I am copying to transpose will exceed the number of columns, even in 2007. So sorry, I should have seen that. I will have to rethink this and approach from another angle. Thanks for your help, turns out I've got more issues to deal with and am heading back to the drawing board.

Sub PasteSpecial()

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

End Sub

 

[SkipVought]

Here is a better way to code your procedure. Please post VBA (macro) questions in Forum707 in the future.

Sub PasteSpecial()

    Range(Range("A1"), Range("A1").End(xlDown)).Copy

    Range("D1").PasteSpecial _
      Paste:=xlPasteAll, _
      Operation:=xlNone, _
      SkipBlanks:= False, _
      Transpose:=True

End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[canasta]

Thank you for the streamlining of the code and advice on proper place to post, I will do that in the future. Thanks.