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)
<tbody>
</tbody>
I also have a second sheet with following values:
<tbody>
</tbody>
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.
<tbody>
</tbody>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)
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 |
Value1 | 10 | |||||
Value2 | 20 | |||||
Value3 | 30 | |||||
Value4 | 45 | |||||
Value5 | 65 | |||||
Value6 | 88 | |||||
Value7 | 90 |
<tbody>
</tbody>
I also have a second sheet with following values:
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 |
10 | 1 | |||||
10 | 3 | |||||
10 | 5 | |||||
20 | 10 | |||||
20 | 15 | |||||
20 | 16 | |||||
30 | 20 | |||||
30 | 21 | |||||
30 | 22 |
<tbody>
</tbody>
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.
Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 |
Value1 | 10 | |||||
10 | 1 | |||||
10 | 3 | |||||
10 | 5 | |||||
Value2 | 20 | |||||
20 | 10 | |||||
20 | 15 | |||||
20 | 16 | |||||
Value3 | 30 | |||||
30 | 20 | |||||
30 | 21 | |||||
30 | 22 | |||||
Value7 | 90 | |||||
<tbody>
</tbody>
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