複数シートを順番に範囲指定してソートしたい

質問

以下のコードでアクセスからデータをエクスポート後に
複数（50くらい）シート名を変更し、シートを並び替えし、
列幅を整えています。
次にデータをソートしたいのですが、うまく範囲指定が
できないで２日くらいすぎてしまいました。
以下の**でかこった部分で定義ができません。
どなたかお助けしていただけないでしょうか？
よろしくお願いします。
うまく改行できないですいません・・。
****************************************************
h = 1
o = 29
ws.Columns(h & ":" & o).Select
範囲指定したいのはA列からAC列までのデータの入った
行数までです。
With ws.Range(.Cells(1), .Cells(ws.Columns.Count).End(xlToUP))

****************************************************
Private Sub CommandButton1_Click()
Dim path$, wb As Workbook, wbName$
Dim ws As Worksheet, i&
Dim intLoopA As Integer
Dim intLoopB As Integer
Dim h As Long
Dim o As Long

path = ThisWorkbook.path & "\"

wbName = Dir(path & "*.xls")
Do Until wbName = ""
If wbName <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(path & wbName)
i = 2
For Each ws In wb.Worksheets
If Trim(ws.Range("A2")) <> "" Then
On Error Resume Next
ws.Name = ws.Range("A2")
If Err.Number <> 0 Then
ws.Name = ws.Range("A2") & " (" & i & ")"
i = i + 1

End If

On Error GoTo 0
End If

For intLoopA = 1 To Sheets.Count
For intLoopB = 1 To Sheets.Count - 1
If Sheets(intLoopB).Name > Sheets(intLoopB + 1).Name Then
Sheets(intLoopB).Move after:=Sheets(intLoopB + 1)
End If
Next intLoopB
Next intLoopA

With ws.Rows(1)
With ws.Range(.Cells(1), .Cells(ws.Columns.Count).End(xlToLeft))
.Interior.ColorIndex = 15
.EntireColumn.AutoFit

End With
End With

h = 1
o = 29

ws.Columns(h & ":" & o).Select

With ws.Range(.Cells(1), .Cells(ws.Columns.Count).End(xlToUP))
.Sort _
Key1:=Range("G1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal

End With

End With

DoEvents
Next
wb.Save

End If
wbName = Dir
Loop
Set wb = Nothing
Set ws = Nothing

MsgBox "処理が完了しました。", vbInformation, "処理確認"

End Sub

回答No.4

rivoisu

いやいやそうではありません。
h,oを文字にするなら従来のあなたの記述
Columns(h & ":" & o).Selectでいいのです。
そうすればh,oの代入結果が
range("A:AC").select
となり正解でいいのです。
もうひとつの表記は列範囲を整数で示す方法です。
あなたの元のコードがなぜか直接range("A:AC").selectと書かずにh,oなどの変数を使っているので、ならば範囲が変わることもあるのだろうかと考え、それならば整数で列範囲を示す方法もありますよということで
range(columns(ｈ),columns(o))という表記を併記したのです。
つまりh,oを整数にしておきたい場合の表記です。

お礼

回答ありがとうございます。
修正して下のコードで動きました。
しかし、ソートがうまく行われないようで、
処理がスルーしてしまいます。
エクセルVBAではソートはうまくできないのかな？
初心者なので難しすぎて意味がわかりませんでしたが、
勉強になりました。

ありがとうございます。

Dim path$, wb As Workbook, wbName$
Dim ws As Worksheet, i&
Dim intLoopA As Integer
Dim intLoopB As Integer
Dim h As String
Dim o As String

path = ThisWorkbook.path & "\"

wbName = Dir(path & "*.xls")
Do Until wbName = ""
If wbName <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(path & wbName)
i = 2
For Each ws In wb.Worksheets
If Trim(ws.Range("A2")) <> "" Then
On Error Resume Next
ws.Name = ws.Range("A2")
If Err.Number <> 0 Then
ws.Name = ws.Range("A2") & " (" & i & ")"

i = i + 1

h = "A"
o = "AC"

ActiveSheet.Range(Columns(h), Columns(o)).Sort _
Key1:=Range("D2"), order1:=xlDescending, _
Key2:=Range("E2"), order2:=xlAscending, _
Key3:=Range("H2"), order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal

End If

With ws.Range(ws.Cells(1, 1), ws.Cells(1, ws.Columns.Count).End(xlToLeft))

.Interior.ColorIndex = 15
.EntireColumn.AutoFit

End With

For intLoopA = 1 To Sheets.Count
For intLoopB = 1 To Sheets.Count - 1
If Sheets(intLoopB).Name > Sheets(intLoopB + 1).Name Then
Sheets(intLoopB).Move after:=Sheets(intLoopB + 1)
End If
Next intLoopB
Next intLoopA

On Error GoTo 0
End If

DoEvents
Next
wb.Save

End If
wbName = Dir
Loop
Set wb = Nothing
Set ws = Nothing

MsgBox "処理が完了しました。", vbInformation, "処理確認"

End Sub

回答No.3

rivoisu

With ws.Range(ws.Cells(1, 1), ws.Cells(1, ws.Columns.Count).End(xlToLeft))
に修正してください。
私は通常こういう場合アクティヴにしてから処理するので気づきませんでした。
後でselectを使うのですからws.は無意味です。アクティブにしてから処理すべきです。

ws.Columns(h & ":" & o).Select
このセンテンスは1つのエラーと無意味な記述がひとつあります。
まずエラー
columnｓの指定は列番号ではなく列の文字列で指定
hとoの宣言を文字列にして
h="A"
o="AC"
としなければならないようです。
もうひとつの表記としては
range(columns(ｈ),columns(o))
こっちのほうが全体の統一が取れている感じです。
無意味な記述はws. です。
アクティブなシートにしかselectは使えませんので
ws.activate
としてからこのシートに対する記述を行います。
そしてアクティブシートにはシート名による修飾は省略できます。
つまり冒頭の修正も不要になります。

とここまで書いて気がついたのですが
With ws.Range(.Cells(1), .Cells(ws.Columns.Count).End(xlToUP))
とソート範囲を記述しているのに問題のselectはいるのですか？
2重に範囲記述があります。むしろつまりこのwithは不要で
selection.sort _
としてしまえるのじゃないですか

お礼

下のコードでエラーは出なくなりました。
が・・・ソートしてないようなんですが・・・。
コードの書き方が悪いんでしょうか？

h = "A"
o = "AC"

Range(Columns(h), Columns(o)).Activate
Selection.Sort _
Key1:=Range("D1"), order1:=xlDescending, _
Key2:=Range("E1"), order2:=xlAscending, _
Key3:=Range("H1"), order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal

この部分はちゃんと動きました。
With ws.Range(ws.Cells(1, 1), ws.Cells(1, ws.Columns.Count).End(xlToLeft))

.Interior.ColorIndex = 15
.EntireColumn.AutoFit

End With

補足

ありがとうございます。

ということは

h="A"
o="AC"
ws.activate
range(columns(ｈ),columns(o))
selection.sort _
Key1:=Range("G1"), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
SortMethod:=xlPinYin, DataOption1:=xlSortNormal
でいいのでしょうか？

コードを変更したら、

range(columns(ｈ),columns(o))

でコンパイルエラーが出るんですが・・・・。

回答No.2

rivoisu

なんか全体的にまどろっこしい
たとえばシート名による並べ替えが終わったあとの
With ws.Rows(1)
With ws.Range(.Cells(1), .Cells(ws.Columns.Count).End(xlToLeft))
.Interior.ColorIndex = 15
.EntireColumn.AutoFit
End With
End With
外側のWith End　Withは要らないのじゃないか
with ws.range(cells(1,1),cells(1,ws.Columns.Count).End(xlToLeft))
一行でやりましょう。

sortの前のWithが一個、後のEnd　With が2個　数が会わない

ws.Columns(h & ":" & o).Select
エラーになりませんか？

wb.save
セーブした後closeしないのでどんどんbookが開いていくのじゃありませんか。

プログラムの流れで一番おかしいのは
For Each ws In wb.Worksheets
から最後のほうのnextの中で1枚ずつシートを処理するつもりでしょうが、このなかにシートの並べ替えがあることです。

for nextで各シートの名前をつける
並べ替えをする
for　nextで各シートごとの処理をする。
とすべきです。

デバッグの仕方も勉強したほうがいいと思います。
ブレークポイント設定してそこまで実行して意図した結果になっているか確認する。
ステップ実行をしながら結果をみる。イミディエイトウインドウで変数の値を確認するなどなど

そうすれば質問も絞ることができます。
ワークシートの並べ替えはうまくいってますか
それならここにそのコードを削って掲載する。というように問題を絞り込んでコードを掲載すれば回答者も余計なコードを読まずに済みます。
変数宣言も楽せずにきちんと書きましょう。

お礼

すいません。
>with ws.range(cells(1,1),cells(1,ws.Columns.Count).End(xlToLeft))

とおりに変更したら、エラーが出ました。

なので元に戻しました。

rivoisuさん信用して大丈夫ですか？

補足

早々にありがとうございます。
素人なので質問の仕方が悪く申し訳ありません。

>外側のWith End　Withは要らないのじゃないか
>with ws.range(cells(1,1),cells(1,ws.Columns.Count).End(xlToLeft))
>一行でやりましょう。
勉強になります。

>sortの前のWithが一個、後のEnd　With が2個　数が会わない

>ws.Columns(h & ":" & o).Select
>エラーになりませんか？

エラーになり、範囲の指定の仕方がわからないので質問してます。
>wb.save
>セーブした後closeしないのでどんどんbookが開いていくのじゃありま>せんか。

ブックは少ないので大丈夫です。
シート数が多いだけです。

>プログラムの流れで一番おかしいのは
>For Each ws In wb.Worksheets
>から最後のほうのnextの中で1枚ずつシートを処理するつもりでしょう>が、このなかにシートの並べ替えがあることです。

別々のコードを入れただけなのでわかりませんでした。
そのように致します。
>ワークシートの並べ替えはうまくいってますか

うまく行ってます。

質問は最初に質問に書いたとおり、
****************************************************
h = 1
o = 29
ws.Columns(h & ":" & o).Select
範囲指定したいのはA列からAC列までのデータの入った
行数までです。
With ws.Range(.Cells(1), .Cells(ws.Columns.Count).End(xlToUP))

****************************************************

の範囲指定です。
わかりずらくてすいませんでした。
よろしくお願いします。

回答No.1

cistronezk

よく見ていませんが、Withがネストされているのが気になります。実際にも、どっちの省略かを混同するケースがよくあります。
Withを使わなくても、期待通りの動作をするかどうかを確認してはいかがでしょう。