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

What error code / message do you get then?

--

Regards,
Nigel
(E-Mail Removed)



"salgud" <(E-Mail Removed)> wrote in message
news:xyltun0g71ag.9f2ucubnr3uy$.(E-Mail Removed)...
> 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

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.
--
HTH,
Barb Reinhardt



"salgud" wrote:

> 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
>

On Wed, 23 Apr 2008 11:50:01 -0700, Barb Reinhardt wrote:

> 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?

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.
--
HTH,
Barb Reinhardt



"salgud" wrote:

> On Wed, 23 Apr 2008 11:50:01 -0700, Barb Reinhardt wrote:
>
> > 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?
>

On Wed, 23 Apr 2008 15:46:03 -0700, Barb Reinhardt wrote:

> 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?

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
--
HTH,
Barb Reinhardt



"salgud" wrote:

> On Wed, 23 Apr 2008 15:46:03 -0700, Barb Reinhardt wrote:
>
> > 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?
>