Can't paste today

[salgud]

Done many copy/pastes before, but today, XL doesn't like it!
Sub AllocbyCty()
Dim wbCty As Workbook
Dim sNew As String
Dim lCurCol As Long
Dim wsSource As Worksheet
Dim wsTranspose As Worksheet
Dim sCty As String
Dim lStrDif As Long

Set wsSource = ActiveSheet

lCurCol = 2

wsSource.Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Selection.Copy
Sheets.Add.Activate
Set wsTranspose = ActiveSheet
wsTranspose.Name = "Transpose"

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

Do Until wsTranspose.Cells(1, lCurCol) = ""

sCty = wsTranspose.Cells(1, lCurCol)
lStrDif = Len(sCty) - 5
sCty = Right(sCty, Len(sCty) - lStrDif)
Range("A1:A4").Select
Selection.Copy
Workbooks.Add.Activate
Set wbCty = ActiveWorkbook
wbCty.SaveAs Filename:=ThisWorkbook.Path & "\" & sCty
Sheets("Sheet1").Range("A1").PasteSpecial Paste:=xlValues,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Any suggestions?
Thanks

 

[Nigel]

What error code / message do you get then?

 

[Barb Reinhardt]

It appears to me that you're trying to transpose 65526 rows x 256 columns (in
Excel 2003) to 65526 Columns x 256 Rows and it won't do that. Put in a
bunch of debug.print statements to figure out where it's going haywire. I'm
guessing it's in the lines with XLToRight and XLDown.

 

[salgud]

It appears to me that you're trying to transpose 65526 rows x 256 columns (in
Excel 2003) to 65526 Columns x 256 Rows and it won't do that. Put in a
bunch of debug.print statements to figure out where it's going haywire. I'm
guessing it's in the lines with XLToRight and XLDown.

Thanks for your reply. I should have been clearer. It's not the paste after
the "transpose" that's giving me the problems, it's the one further down
where I'm pasting from cells A1 to A4 into the new workbook.
Sorry about my ignorance, but I don't know what a "debug.print" statement
is or how to use it. What is it? What does it do? How would I use it here?

 

[Barb Reinhardt]

If you put

Debug.Print Selection.Address

In your code, it will print the Selection.Address in the Immediate window.
You can access the Immediate window with CTRL G. You can also step through
your code line by line with F8 or put a breakpoint in with F9 and run to the
breakpoint. It helps with debugging the code.

 

[salgud]

If you put

Debug.Print Selection.Address

In your code, it will print the Selection.Address in the Immediate window.
You can access the Immediate window with CTRL G. You can also step through
your code line by line with F8 or put a breakpoint in with F9 and run to the
breakpoint. It helps with debugging the code.

Thanks for your reply. I've figured out the step thru and the breakpoint,
there are icons for those so they're pretty obvious. But debug.print isn't
so obvious. But I checked it out, and I've just been using MsgBox to do the
same thing. I've never figured out what, if anything, the Immediate Window
does. I've tested code in it a few times, but rarely does it work, or give
me any useful information. Now I know it displays the result of a
"debug.print". What else can I "debug.print" besides the selection address?
Are there useful things that debug.print can show? Anything that I can't do
with a MsgBox?

 

[Barb Reinhardt]

You can use Debug.print for just about anything you'd put in a MsgBox for
debugging your code. Debug.print is a whole lot easier to remove when you're
done. I often use MsgBox for other functionality (besides debugging), so I
wouldn't want to remove all MsgBox's