|
Sheet1 と Sheet2 のデータのうち、どちらをSheet3に抽出すればよいか
書いてないので、"A:E列には Sheet1 のデータ,G:K列には Sheet2 のデータ"
を抽出することとします。以下のマクロを試してみて下さい。
Sub Get_Def_Value()
Dim LR As Long, LR2 As Long
LR = Sheets("Sheet1").Range("A65536").End(xlUp).Row
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
With Sheets("Sheet3")
.Range("A1:A" & LR).Formula = _
"=CONCATENATE(Sheet1!A1,"","",Sheet1!B1,"",""," & _
"Sheet1!C1,"","",Sheet1!D1)"
.Range("E1:E" & LR).Formula = "=Sheet1!E1"
.Range("G1:G" & LR).Formula = _
"=CONCATENATE(Sheet2!A1,"","",Sheet2!B1,"",""," & _
"Sheet2!C1,"","",Sheet2!D1)"
.Range("K1:K" & LR).Formula = "=Sheet2!E1"
With .Range("A1:K" & LR)
.Copy
.PasteSpecial xlPasteValues
.Range("A:A,G:G").RowDifferences(.Range("G1")) _
.Offset(, 5).Value = 1
.Sort Key1:=.Columns(6), Order1:=xlAscending, _
Header:=xlNo, Orientation:=xlSortColumns
End With
LR2 = .Range("F65536").End(xlUp).Row + 1
.Rows(LR & ":" & LR2).ClearContents
.Range("F:F").ClearContents
.Range("A:A").TextToColumns _
DataType:=xlDelimited, Comma:=True
.Range("G:G").TextToColumns _
DataType:=xlDelimited, Comma:=True
End With
With Application
.Goto Sheets("Sheet3").Range("A1"), True
.CutCopyMode = False
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Subぬ
|
|