Hi All,
I have access this forum multiple times as a guest and I have found many answers to my issues; however, I have now one problem that I cannot figure out a solution. I would like to ask for your help since I am not a VBA guru and I am new in the forum.
Here is my issue:
I have one sheet (Sheet1) with following sample values (real sheet has more values)
[TABLE="width: 448"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]Column3
[/TD]
[TD]Column4
[/TD]
[TD]Column5
[/TD]
[TD]Column6
[/TD]
[TD]Column7
[/TD]
[/TR]
[TR]
[TD]Value1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Value2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]Value3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Value4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]Value5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]65
[/TD]
[/TR]
[TR]
[TD]Value6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]88
[/TD]
[/TR]
[TR]
[TD]Value7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90
[/TD]
[/TR]
</tbody>[/TABLE]
I also have a second sheet with following values:
[TABLE="width: 448"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]Column3
[/TD]
[TD]Column4
[/TD]
[TD]Column5
[/TD]
[TD]Column6
[/TD]
[TD]Column7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22
[/TD]
[/TR]
</tbody>[/TABLE]
My goal is to create a macro that will read the first value (i.e. 10) from Sheet1 column 7 and use it as filter for the values in sheet2 (column 2 as Key). Once the rows are filtered (i.e. 3 rows with value 10); copy these in memory and switch back to sheet 1> add a row underneath original number (10)and 'insert copy cells' .
This following table shows my intended goal.
[TABLE="width: 448"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]Column3
[/TD]
[TD]Column4
[/TD]
[TD]Column5
[/TD]
[TD]Column6
[/TD]
[TD]Column7
[/TD]
[/TR]
[TR]
[TD]Value1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Value2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]Value3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]Value7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So far, I have a macro that works for the first value (Sheet 1 - G2 cell as active) but when I select the second value in sheet1 column 7 (i.e. 20). The macro does not work anymore:
Following is my code so far:
NOTE: I'm open to any other approach if your gurus feel is better.
Also, the real files will have more rows in them.
Sorry for above tables but once I figure out how to paste images or add files I will do it.
Thanks in advance,
Sub CopyFilteredValues()
Dim CLValue As String
Dim cellAddress2 As Integer
CLValue = ActiveCell.Value
cellAddress = ActiveCell.Address
Sheets("Sheet2").Select
Set My_Range = Range("$A$2:$G$10")
My_Range.Parent.Select
My_Range.Parent.AutoFilterMode = False
My_Range.AutoFilter Field:=2, Criteria1:=CLValue
My_Range.Parent.AutoFilter.Range.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End Sub
I have access this forum multiple times as a guest and I have found many answers to my issues; however, I have now one problem that I cannot figure out a solution. I would like to ask for your help since I am not a VBA guru and I am new in the forum.
Here is my issue:
I have one sheet (Sheet1) with following sample values (real sheet has more values)
[TABLE="width: 448"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]Column3
[/TD]
[TD]Column4
[/TD]
[TD]Column5
[/TD]
[TD]Column6
[/TD]
[TD]Column7
[/TD]
[/TR]
[TR]
[TD]Value1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Value2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]Value3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Value4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45
[/TD]
[/TR]
[TR]
[TD]Value5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]65
[/TD]
[/TR]
[TR]
[TD]Value6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]88
[/TD]
[/TR]
[TR]
[TD]Value7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90
[/TD]
[/TR]
</tbody>[/TABLE]
I also have a second sheet with following values:
[TABLE="width: 448"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]Column3
[/TD]
[TD]Column4
[/TD]
[TD]Column5
[/TD]
[TD]Column6
[/TD]
[TD]Column7
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22
[/TD]
[/TR]
</tbody>[/TABLE]
My goal is to create a macro that will read the first value (i.e. 10) from Sheet1 column 7 and use it as filter for the values in sheet2 (column 2 as Key). Once the rows are filtered (i.e. 3 rows with value 10); copy these in memory and switch back to sheet 1> add a row underneath original number (10)and 'insert copy cells' .
This following table shows my intended goal.
[TABLE="width: 448"]
<tbody>[TR]
[TD]Column1
[/TD]
[TD]Column2
[/TD]
[TD]Column3
[/TD]
[TD]Column4
[/TD]
[TD]Column5
[/TD]
[TD]Column6
[/TD]
[TD]Column7
[/TD]
[/TR]
[TR]
[TD]Value1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Value2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]20
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]16
[/TD]
[/TR]
[TR]
[TD]Value3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]22
[/TD]
[/TR]
[TR]
[TD]Value7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So far, I have a macro that works for the first value (Sheet 1 - G2 cell as active) but when I select the second value in sheet1 column 7 (i.e. 20). The macro does not work anymore:
Following is my code so far:
NOTE: I'm open to any other approach if your gurus feel is better.
Also, the real files will have more rows in them.
Sorry for above tables but once I figure out how to paste images or add files I will do it.
Thanks in advance,
Sub CopyFilteredValues()
Dim CLValue As String
Dim cellAddress2 As Integer
CLValue = ActiveCell.Value
cellAddress = ActiveCell.Address
Sheets("Sheet2").Select
Set My_Range = Range("$A$2:$G$10")
My_Range.Parent.Select
My_Range.Parent.AutoFilterMode = False
My_Range.AutoFilter Field:=2, Criteria1:=CLValue
My_Range.Parent.AutoFilter.Range.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown
End Sub