Excel2003を使用しています。
ある一覧表形式のデータSheet1をSheet2に値のみコピーして、このSheet2を“印刷用”として、ページ設定等をして、印刷のみに使用しようと思っています。
Sheet2のI列、J列、K列には数値が入力されていて、I列、J列、K列のデータの最終行の1行下に、それぞれ6行目からデータ最終行までの合計の数式が入力されるよう、コードを追加したのですが、数式は入力されるものの、入力したい行に数式が入力されません。
マクロを実行して、数式が入力された行を見てみると、301行目に入力されていました。
Sheet1は別のシートのデータを数式により表示していて、数式が300行まで入力されているので、Sheet2の元になっているSheet1の影響(?)なのかな~?と…。こういう場合、どうすればいいでしょうか?
コードは下記のようになっています。
よろしくお願いします。
----------------------------------------
Sub 印刷用作成()
Dim i As Integer
Dim j As Long
Sheets("Sheet1").Activate
Range("B6:L6", Range("L6").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B6").PasteSpecial xlPasteValues
Application.CutCopyMode = False
For i = 9 To 11
j = Application.Max(j, Cells(65536, i).End(xlUp).Row + 1)
Next
For i = 9 To 11
Cells(j, i).FormulaR1C1 = "=SUM(R2C[0]:R[-1]C[0])"
Next i
End Sub
No.2ベストアンサー
- 回答日時:
>Sheet1は別のシートのデータを数式により表示していて
>数式が300行まで入力されているので、Sheet2の元に
>なっているSheet1の影響(?)なのかな~?と…
その通りです。
I,J,K列以外にどんな値が入っているのか提示されてないのでなんですが。。。
ふつうこういった表には、他の列(B、C。。)に項目(得意先名など)が入っているはずなんですが、
それらはどうなってますか?
I,J,K列以外で表の最終行はとれないのですか?
例えば、B列で表の最終行が取れるとして。。。
'-------------------------------------------------
Sub 印刷用作成()
Dim i As Integer
Dim LastRow As Long
Dim myRange As Range
Sheets("Sheet1").Activate
LastRow = Cells(65536, "B").End(xlUp).Row
Range("B6:L" & LastRow).Copy
Sheets("Sheet2").Activate
Range("B6").PasteSpecial xlPasteValues
Application.CutCopyMode = False
For i = 9 To 11
Set myRange = Range(Cells(6, i), Cells(LastRow, i))
Cells(LastRow + 1, i).Value = WorksheetFunction.Sum(myRange)
Next i
End Sub
'-------------------------------------------------
以上。
回答ありがとうございます。
>I,J,K列以外で表の最終行はとれないのですか?
Sheet2のデータは全てSheet1から数式で表示しているので、Sheet2からはデータの最終行を取得できませんでしたが、大元のSheetを利用することで、データの最終行を取得できたので、これを元にうまく処理することができました。
ありがとうございました。
No.3
- 回答日時:
こんばんは。
この部分は、ユニークですね。こういう方法もよいですね。
For i = 9 To 11
j = Application.Max(j, Cells(65536, i).End(xlUp).Row + 1)
Next
シート2のI列から3列の最終行の次の行を決めるためですが、ただ、コード自体が、シート・モジュールに書いてしまっているかもしれません。
Sheet1 のシートモジュール(ローカル)に書くと、Cells の親オブジェクトは、いくら、Sheetを変えても、何も上位オブジェクトを書かないと、Sheet1 の場所を探してしまいます。
Excelの場合は、特殊な場合を除いて、標準モジュールを使います。
あえて、シートモジュールに書くためには、親オブジェクトを意識して、例えば、以下のように、With ステートメントを入れてみたらいかがでしょうか。そうすると、狂いが少なくなります。(中に書くのは、.Range, .Cells というように、オブジェクト等の前に「.(カンマ)」が付きます。)
Sheets("Sheet2").Select 'あってもなくてもよい
With Worksheets("Sheet2") '←With ステートメントを加える
.Range("B6").PasteSpecial xlPasteValues
Application.CutCopyMode = False
For i = 9 To 11
j = Application.Max(j, .Cells(65536, i).End(xlUp).Row + 1)
Next
For i = 9 To 11
.Cells(j, i).FormulaR1C1 = "=SUM(R2C[0]:R[-1]C[0])"
Next i
End Sub
End With
-----------------------------
'私案:(標準モジュール、またはシートモジュール)
Sub 印刷用作成2()
Dim i As Integer
Dim j As Long
Dim r As Range
With Worksheets("Sheet1")
Set r = .Range("B6", .Range("L6").End(xlDown))
Worksheets("Sheet2").Range("B6").Resize(r.Rows.Count, r.Columns.Count).Value _
= r.Value '値コピーの場合
End With
With Worksheets("Sheet2")
For i = 9 To 11
If j <= (.Cells(65536, i).End(xlUp).Row + 1) Then
j = .Cells(65536, i).End(xlUp).Row + 1
End If
Next
If j > 1 Then
.Cells(j, 9).Resize(, 3).FormulaLocal = "=SUM(R2C:R[-1]C)"
End If
End With
Set r = Nothing
End Sub
Wendy02 さん、こんにちは。
いつもありがとうございます。
>コード自体が、シート・モジュールに書いてしまっているかもしれません。
コードは標準モジュールに記述していますが、やはり、Sheet2の元になっているSheet1の影響のようです。
Sheet2のデータは全てSheet1から数式で表示しているので、そのSheet1の元になっているSheetを利用して、データの最終行を求めることができました。
同様の処理をするコードでも、いろんな書き方があるのですね。
勉強になります。ありがとうございました。
No.1
- 回答日時:
Sheet2の、(65536,9)、(65536,10)、(65536,11)それぞれで、そのセルをアクティブにした状態から Ctrl+↑で、どのセルにジャンプするか試してみましょう。
アドバイスありがとうございます。
Ctrl + ↑で、どのセルにジャンプするかで調べることができるのですね。大変参考になりました。
やはり、Sheet2の元になっているSheet1の影響で、数式を入力したい行に入力できなかったようです。
今回は、ANo.2の方からアドバイスをいただいた“別の列からデータ最終行を取得する”方法で、うまくいきました。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) 改行ごとに行を追加し、数量を分割 4 2023/07/11 16:39
- Excel(エクセル) 列の最終行に新たに入力されたらその値を自動参照 1 2023/01/21 09:59
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- Visual Basic(VBA) 数字が「0」の列を削除するため、下記のコードを実行しましたが、コンパイルエラーSubまたはFunct 3 2022/12/04 00:00
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【Excel VBA】CSV取込時、数字...
-
Word2016でExcelデータを差込し...
-
EXCELの列の幅
-
エクセルで電話番号にハイフン...
-
Excel 表の必要箇所だけを抜き...
-
EXCELで2つのシートから一致し...
-
エクセルでページ毎の計をつけ...
-
EXCELで不良率を出そうと思って...
-
VBAで列に計算式を入れたい
-
超初心者がマクロ集計をするこ...
-
エクセルで前年同日・前月同日...
-
エクセルで縦線のいっぱい入っ...
-
excelの列がいっぱいになり列を...
-
エクセルの複数ワークシートの...
-
エクセルの並べ変え
-
Excel複数のファイルデータを一...
-
Excelのセル内で規則に従った部...
-
エクセルシートの選択範囲をコ...
-
エクセルで顧客リスト
-
セル入力文字が、「右のセルに...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Word2016でExcelデータを差込し...
-
【Excel VBA】CSV取込時、数字...
-
EXCELで2つのシートから一致し...
-
Excel 表の必要箇所だけを抜き...
-
EXCELの列の幅
-
エクセルで前年同日・前月同日...
-
エクセルでページ毎の計をつけ...
-
EXCEL2007で2つのシートのどっ...
-
excelの列がいっぱいになり列を...
-
エクセルで電話番号にハイフン...
-
エクセルVBAで複数列データを1...
-
EXCELで不良率を出そうと思って...
-
エクセルの複数ワークシートの...
-
エクセルの余白を0にしても列...
-
エクセルで縦線のいっぱい入っ...
-
Excelで奇数行を削除
-
ExcelのIF関数について
-
Excelのhperlink関数で作ったモ...
-
エクセル:最新データ12件で...
-
ピボットテーブル作成後、重複...
おすすめ情報