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

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
Try this

VBA Code:
Sub Copy_Data()
  Dim sh1 As Worksheet, sh2 As Worksheet
  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
  sh2.Range("A1:B1").AutoFilter 2, sh1.Range("D3").Value
  sh2.AutoFilter.Range.Range("A1:A" & sh2.Range("A" & Rows.Count).End(xlUp).Row).Copy sh1.Range("A1")
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
End Sub
 

lacogada

Board Regular
Joined
Jan 26, 2011
Messages
147
Thanks Dante ... but it does nothing when I click on the form option.
On Search sheet I chose view code and pasted your data.
 

Attachments

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
After selecting an option from your form, you must press a button, within that button you put the macro.

Ah, another detail, you must put a first row in the "Songs" sheet, it can be a title, in order to perform the filter properly.
 

lacogada

Board Regular
Joined
Jan 26, 2011
Messages
147
Inserted a row on Songs sheet and added title in A1.

But nothing when clicking any option on the user form.

Thanks
 

lacogada

Board Regular
Joined
Jan 26, 2011
Messages
147
If I go to Tools-Macros and choose run the macro I get error message 400.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
But nothing when clicking any option on the user form.
You have to put a commandbutton in your form.
Select an option and then press the button.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
Ok, Looks like you have a userform with optoinbuttons
Try this, Put the following code inside the userform.

VBA 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
  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
  sh2.Range("A1:B1").AutoFilter 2, sh1.Range("D3").Value
  sh2.AutoFilter.Range.Range("A1:A" & sh2.Range("A" & Rows.Count).End(xlUp).Row).Copy sh1.Range("A1")
  If sh1.AutoFilterMode Then sh1.AutoFilterMode = False
  Application.ScreenUpdating = True
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,749
Office Version
2007
Platform
Windows
What do you have in cell D3 on the "Search" sheet?
 

Forum statistics

Threads
1,077,824
Messages
5,336,593
Members
399,090
Latest member
Mcoca

Some videos you may like

This Week's Hot Topics

Top