VBAでSheet2をアクティブセルにしたい

Question

前提・実現したいこと

sheet1の元データを複製させた、sheet2(変換結果)のデータをいじりたいが、sheet1が反応してしまう。
※Sub test3()でアクティブなセルをsheet2(変換結果)にしたつもりだが、Sub Test4()以降の作業を行うとsheet1で作業が行われる。

発生している問題・エラーメッセージ

エラー表示はないが、アクティブなセルをsheet2(変換結果)にしたつもりだが、sheet1で作業が行われる。

### 該当のソースコード
Sub Test1()
    Worksheets("Sheet1").Copy After:=Worksheets(Worksheets.Count)
End Sub

Sub test2()
    Worksheets("Sheet1 (2)").Name = "変換結果"
End Sub

Sub test3()
    Dim bk  As Workbook
    
    Set bk = ActiveWorkbook
    
    '// Sheet1を選択
    bk.Worksheets("変換結果").Select
End Sub

Sub Test4()
    Worksheets("変換結果").Activate
    Columns(3).Insert
End Sub

Sub test5()
    Columns(4).Select
    Selection.Copy
    Columns(3).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Answer 1

Range,Cells,Columns,Rows いずれもシートが指定されていないと、
ActiveSheet が適用されます。

ちゃんと作るなら、ThisWorkBook.Worksheets("シート名").Range()等と、
シートを指定してください。

毎回書くのが面倒なら、
プロジェクトウィンドウで、シートオブジェクトの名前(Sheet1等)を、
分かりやすい名前(Sheet_Data等)に変えて使う(Sheet_Data.Range()等)か、
Worksheet型の変数に格納して使ってください。

基本的にはWithを併用した方が書きやすいと思いますが、
Withをネストしすぎると分かり難くなる場合もあります。

Comments

[mimicon]

お返事が遅れてしまい申し訳ありません。
上記のソースは省略したものでして、実際は更に下に以下のような時間を9時間足すプログラムになっているのですが、ThisWorkBook.Worksheets("シート名").Range()等としてもSub Test6()が動かず、自己解決しようと思ったのですが、できず今になってご連絡をさせていただく形になってしまいました。
以下のプログラムも動くようにするには何かよろしい方法はございますでしょうか。
もしよろしければ教えていただけますと幸いです。

Sub Test6()
With ActiveSheet.UsedRange
Dim maxRow As Integer: maxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row

Dim rng As Range

For Each rng In Range("C2", Cells(maxRow, 3))
rng.Value = rng.Value + 0.375
Next
End With
End Sub

[ExcelVBAer]

自分でデバッグしなよ。
F8のステップ実行で、ローカルウィンドウなり、イミディエイトウィンドウなり使って、プログラムがどう動いてるか見てみたら分かるでしょ。

Answer 2

明示的に指定して記述すれば、他のWorkbookやWorkSheetも正しく制御できます。
下記コードは、Test3～5をまとめたものです。

With Thisworkbook.Worksheets("変換結果")
    .Columns(3).Insert
    .Columns(4).Copy Destination:=.Columns(3)
End With

Comments

[mimicon]

お返事が遅れてしまい申し訳ありません。
上記のソースは省略したものでして、実際は更に下に以下のような時間を9時間足すプログラムになっているのですが、With Thisworkbook.Worksheets("変換結果")〜End WithとしてもSub Test6()が動かず、自己解決しようと思ったのですが、できず今になってご連絡をさせていただく形になってしまいました。
以下のプログラムも動くようにするには何かよろしい方法はございますでしょうか。
もしよろしければ教えていただけますと幸いです。

Sub Test6()
With ActiveSheet.UsedRange
Dim maxRow As Integer: maxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row

Dim rng As Range

For Each rng In Range("C2", Cells(maxRow, 3))
rng.Value = rng.Value + 0.375
Next
End With
End Sub

Answer 3

Worksheetsが有ることからExcelの操作であると想像します。

質問の内容から処理を分割する必要があるのか疑問なのですが。
処理を分けている理由があるのでしょうか。

Columns(3).Insert等の処理はthisWorksheetに対して行われる為sheet1で実行すればsheet1に書き込まれます。

下記のように明示的にシートを指定した状態にコードを変更すれば対応できるかと思います。

Dim targetSheet as Worksheet
Set targetSheet = ActiveWorkbook.WorkSheets("変換結果")
targetSheet.Columns(3).Insert

Comments

[mimicon]

ご回答ありがとうございます。
Excelという情報が抜けており申し訳ありませんでした。
上記のソースはやや簡潔にしたものでして実際は時間を足すなど行うため元データも残しておきたく
処理を分けてます。
また、ご回答に従い作業を行ったのですが、「実行時エラー ‘9’: インデックスが有効範囲にありません。」とエラーが
出てしまいます。3列目のセル内には値も入っており特に問題がないように思えるのですが、何かお分かりでしたら教えていただけますと幸いです。
よろしくお願いいたします。

[n_takapyon]

「実行時エラー ‘9’: インデックスが有効範囲にありません。」のエラーは探したけど見つからなかったと言っているのでシート名での検索が失敗しているものと想像します。

実際に指定したいシート名を正しく指定する必要があります。（スペースや全角半角等の違いでもNGです）

[mimicon]

お返事が遅れてしまい申し訳ありません。
指定したいシートにつきましてはn_takapyonさんのご推測通りシート名が少々異なっておりました。すみません。
また、上記のソースは省略したものでして、実際は更に下に以下のような時間を9時間足すプログラムになっているのですが、教えていただいた通りにしますと、Sub Test6()が動かず、自己解決しようと思ったのですが、できず今になってご連絡をさせていただく形になってしまいました。
以下のプログラムも動くようにするには何かよろしい方法はございますでしょうか。
もしよろしければ教えていただけますと幸いです。

Sub Test6()
With ActiveSheet.UsedRange
Dim maxRow As Integer: maxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row

Dim rng As Range

For Each rng In Range("C2", Cells(maxRow, 3))
rng.Value = rng.Value + 0.375
Next
End With
End Sub

[TanakaHiroaki]

すべての処理について、明示的にシートを指定するとよいです。
例えば、RangeやCellsの前に.　をつければエラーが出なくならないでしょうか。

　For Each rng In .Range("C2", .Cells(maxRow, 3))

[ExcelVBAer]

問題は、「With ActiveSheet.UsedRange」としている点ですね。
Tanakaさんは「With ActiveSheet」の想定のハズ。
広い範囲に With する場合は、シートにしておく方が無難ですね。

[TanakaHiroaki]

その通りですね。ご指摘ありがとうございます。