Dynamic Range Transpose Loop

[freybe06]

Hello Everyone,

I apologize if this has already been asked - I've been looking for an answer and can't find anything out there.

I'm writing a macro that does the following:

1. Opens a file and copies all the worksheets in to a new workbook.
2. Transposes all of the data in each worksheet except the last one.
3. Formats the columns via autofit, alignment, etc.
4. Moves all the worksheets to a new workbook (that doesn't have a macro).
5. Saves the document.

Here is my code as of right now (I only gotten to halfway through step 3):


Sub TransposeMacro()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Set wb1 = Workbooks("File1")
ChDir "C:\Folder1\Folder2"
Workbooks.Open Filename:="C:\Folder1\Folder2\File2.xls"
Set wb2 = Workbooks("File2")
For Each ws In wb2.Worksheets
ws.Move after:=wb1.Sheets(wb1.Sheets.Count)
Next ws
Dim i As Long
Sheet1.Select
For i = 2 To ThisWorkbook.Sheets.Count
If Sheets(i).Name <> "LastWorksheet" Then Sheets(i).Select Replace:=False
Next i
For Each Worksheet In Selection
Rows("1:256").Select
Selection.Copy
Rows("257:513").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Rows("1:256").Select
Selection.Delete Shift:=xlUp
Cells.Select
Range("A1").Activate
Cells.EntireColumn.AutoFit
Next Worksheet
End Sub

My first issue is that I need to try to make this macro as compatible as possible with Excel 03 and 10. I’d like to try to use the transpose tool instead of using “rows 1:256”. My problem is that the transpose tool needs a perfect square (amount of rows = amount of columns). I’d like to be able to transpose dynamic ranges but I don’t know how to get there.

My second issue is that I’m not very good with looping. Especially looping dynamic ranges in a dynamic amount of worksheets. When I run this macro I end up getting one of two problems – either the code goes in to an endless loop and freezes my Excel or it finishes and doesn’t autofit anything.

If you could take a look at this and let me know how to loop dynamic ranges in a dynamic amount of worksheets, I would really appreciate it. Also, if you have any tips regarding my current code and anything that might make it faster, that would be awesome too.

Please let me know if you need any other details. Thank you so much in advance!

[Bob Phillips]

I am confused by this line

For Each Worksheet In Selection

There is no variable Worksheet, but the name suggests a sheet, but Selection is a range, these are different objects. What should be happening here?

[Bob Phillips]

I am also confused by all the workbooks. Okay, wb2 is the workbook that you open and grab data from, but is wb1 the same workbook as ThisWorkbook, and where is the worksheet LastWorksheet.

[Bob Phillips]

And why do you copy to rows 257 on, then delete rows 1:256, hy not just copy to row 1?