Two sheets ... copy data from one sheet if criteria met ?

[lacogada]

Appreciated the vba code to accomplish task ... beyond my skill level.

 

[DanteAmor]

Last code you sent worked the first time I clicked an option button.
If I click another option button error below

And the first time it worked well?
That is, the macro correctly put the songs?

 

[DanteAmor]

Not sure what auto filter does .... but song list is already in order, no need to arrange anything.

Do not worry, the filter does not sort the data.

 

[lacogada]

And the first time it worked well?
That is, the macro correctly put the songs?

Yes ... it seemed to.

 

[lacogada]

Take that back ... I shut down and restarted ... put code in .... chose option I knew had least amount of songs, and it copied the whole list (233 songs)

 

[DanteAmor]

Again, delete the above and put the following code.

Private Sub OptionButton1_Click()
  Sheets("Search").Range("D3").Value = "P"
  Call Copy_Data
End Sub
Private Sub OptionButton2_Click()
  Sheets("Search").Range("D3").Value = "A"
  Call Copy_Data
End Sub
Private Sub OptionButton3_Click()
  Sheets("Search").Range("D3").Value = "B"
  Call Copy_Data
End Sub
Private Sub OptionButton4_Click()
  Sheets("Search").Range("D3").Value = "C"
  Call Copy_Data
End Sub
Private Sub OptionButton5_Click()
  Sheets("Search").Range("D3").Value = "BC"
  Call Copy_Data
End Sub
Private Sub OptionButton6_Click()
  Sheets("Search").Range("D3").Value = "F"
  Call Copy_Data
End Sub
Private Sub OptionButton7_Click()
  Sheets("Search").Range("D3").Value = "D"
  Call Copy_Data
End Sub

Sub Copy_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Search")
  Set sh2 = Sheets("Songs")
  sh1.Range("A:A").ClearContents
  If sh1.Range("D3").Value = "" Then
    MsgBox "Select type"
    Exit Sub
  End If
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
  sh2.Range("A1:B" & lr).AutoFilter 2, sh1.Range("D3").Value
  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
  sh2.Range("A1:A" & lr).Copy sh1.Range("A1")
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub

Note: My version of Excel is 2007 and your version is 2003, which is why I have to change some things. I also have no way to prove that it works in Excel 2003, so you will have to continue testing yourself.

 

[lacogada]

Thanks ... but still complaining about auto filter

 

[DanteAmor]

Please, again delete the above and put the following code.

Private Sub OptionButton1_Click()
  Sheets("Search").Range("D3").Value = "P"
  Call Copy_Data
End Sub
Private Sub OptionButton2_Click()
  Sheets("Search").Range("D3").Value = "A"
  Call Copy_Data
End Sub
Private Sub OptionButton3_Click()
  Sheets("Search").Range("D3").Value = "B"
  Call Copy_Data
End Sub
Private Sub OptionButton4_Click()
  Sheets("Search").Range("D3").Value = "C"
  Call Copy_Data
End Sub
Private Sub OptionButton5_Click()
  Sheets("Search").Range("D3").Value = "BC"
  Call Copy_Data
End Sub
Private Sub OptionButton6_Click()
  Sheets("Search").Range("D3").Value = "F"
  Call Copy_Data
End Sub
Private Sub OptionButton7_Click()
  Sheets("Search").Range("D3").Value = "D"
  Call Copy_Data
End Sub

Sub Copy_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Search")
  Set sh2 = Sheets("Songs")
  sh1.Range("A2:A" & Rows.Count).ClearContents
  If sh1.Range("D3").Value = "" Then
    MsgBox "Select type"
    Exit Sub
  End If
  If sh2.AutoFilterMode Then sh2.AutoFilterMode = False
  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
  sh2.Range("A1:B" & lr).AutoFilter 2, sh1.Range("D3").Value
  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
  If lr > 1 Then
    sh2.Range("A1:A" & lr).SpecialCells(xlCellTypeVisible).Copy sh1.Range("A1")
  End If
  If sh2.AutoFilterMode Then sh2.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub

 

[lacogada]

BINGO ... every option works like a charm.

Thanks so much for your patience and sharing your knowledge.

 

[DanteAmor]

I'm glad, thanks for the feedback.

I had already prepared another method.
Here I put it, in case you want to try it.

Private Sub OptionButton1_Click()
  Sheets("Search").Range("D3").Value = "P"
  Call Copy_Data
End Sub
Private Sub OptionButton2_Click()
  Sheets("Search").Range("D3").Value = "A"
  Call Copy_Data
End Sub
Private Sub OptionButton3_Click()
  Sheets("Search").Range("D3").Value = "B"
  Call Copy_Data
End Sub
Private Sub OptionButton4_Click()
  Sheets("Search").Range("D3").Value = "C"
  Call Copy_Data
End Sub
Private Sub OptionButton5_Click()
  Sheets("Search").Range("D3").Value = "BC"
  Call Copy_Data
End Sub
Private Sub OptionButton6_Click()
  Sheets("Search").Range("D3").Value = "F"
  Call Copy_Data
End Sub
Private Sub OptionButton7_Click()
  Sheets("Search").Range("D3").Value = "D"
  Call Copy_Data
End Sub

Sub Copy_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, j As Long
  Application.ScreenUpdating = False
  Set sh1 = Sheets("Search")
  Set sh2 = Sheets("Songs")
  sh1.Range("A2:A" & Rows.Count).ClearContents
  If sh1.Range("D3").Value = "" Then
    MsgBox "Select type"
    Exit Sub
  End If
  If sh2.AutoFilterMode Then sh2.AutoFilterMode = False
  lr = sh2.Range("A" & Rows.Count).End(xlUp).Row
  j = 2
  For i = 2 To lr
    If sh2.Cells(i, "B").Value = sh1.Range("D3").Value Then
      sh1.Cells(j, "A").Value = sh2.Cells(i, "A").Value
      j = j + 1
    End If
  Next
  Application.ScreenUpdating = True
End Sub

 

[lacogada]

I will save it ... thanks again.