Here is what I need to do. In Excel. There are rows of, say, patient information across columns. I want to copy some selected cells (like Patient Name and Date of Birth) from a row if a patient entered as "Yes" in a particular column to another sheet within the same Excel file. I can sort of understand the VBA language but not enough to build one.
Copy selected cells from one sheet to another sheet IF condition is met
- Thread starter Habari11
- Start date
Hi Suresh,
Thank your for your input. I think this is EXACTLY what I need to do. As I will need to do the same procedures to other sheet, do you mind explaining the each line of your VBA program so that I can modify to create other programs. Thank you so much for your support.
Thank your for your input. I think this is EXACTLY what I need to do. As I will need to do the same procedures to other sheet, do you mind explaining the each line of your VBA program so that I can modify to create other programs. Thank you so much for your support.
Hi Suresh,
Looking closely at your program, I noticed that it doesn't specify the name of the sheet. As I had mentioned, I need to do the same exercise to other sheets. For example, if a patient replied as "Yes," then the some selected information will be copied to a sheet "Yes", and similarly, if a patient replied as "No" then to Sheet "No", and so on. The main patient database which includes all the data is the only thing that I will enter manually. Using Macro, I want to update the list of patients in each sheet automatically. I hope this is not challenging. Thanks again.
Looking closely at your program, I noticed that it doesn't specify the name of the sheet. As I had mentioned, I need to do the same exercise to other sheets. For example, if a patient replied as "Yes," then the some selected information will be copied to a sheet "Yes", and similarly, if a patient replied as "No" then to Sheet "No", and so on. The main patient database which includes all the data is the only thing that I will enter manually. Using Macro, I want to update the list of patients in each sheet automatically. I hope this is not challenging. Thanks again.
Additionally, I also noticed that the current program copies all the columns from the main sheet. I would like to select the columns/information to be copied to a different sheet. Sorry for the confusion.
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
Columns("A:C").Select
Selection.AutoFilter
Range("C1").Select
ActiveSheet.Range("$A$1:$C$10000").AutoFilter Field:=3, Criteria1:="Yes"
Range("A1:C1000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Previous.Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
End Sub
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
Columns("A:C").Select
Selection.AutoFilter
Range("C1").Select
ActiveSheet.Range("$A$1:$C$10000").AutoFilter Field:=3, Criteria1:="Yes"
Range("A1:C1000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
ActiveSheet.Next.Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveSheet.Previous.Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
End Sub
Glad to help you Linda,
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
(This will select the data)
Columns("A:C").Select
(This will apply filter)
Selection.AutoFilter
Range("C1").Select
(This will apply the filter from 1st row to 10000th row and it will select the 3rd field(since yes/no available on 3rd column and criterial is Yes)
ActiveSheet.Range("$A$1:$C$10000").AutoFilter Field:=3, Criteria1:="Yes"
(This will select the data after the filter applied from 1st row to 1000th row only)
Range("A1:C1000").Select
(This will select only the visible data from 1st row to 1000th row only)
Selection.SpecialCells(xlCellTypeVisible).Select
(This will copy the visible data)
Selection.Copy
(This will select the next sheet - if you want to define the sheet name "worksheets("sheet name").select" )
ActiveSheet.Next.Select
Range("A1").Select
(This will paste the copied data)
ActiveSheet.Paste
Range("A1").Select
(This will select the rawdata sheet - if you want to define the sheet name "worksheets("sheet name").select" )
ActiveSheet.Previous.Select
Range("A1").Select
Application.CutCopyMode = False
(Thil will remove filter)
Selection.AutoFilter
Range("A1").Select
End Sub
Please let me know if you have any questions.
Thanks,
Suresh Kumar S
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
(This will select the data)
Columns("A:C").Select
(This will apply filter)
Selection.AutoFilter
Range("C1").Select
(This will apply the filter from 1st row to 10000th row and it will select the 3rd field(since yes/no available on 3rd column and criterial is Yes)
ActiveSheet.Range("$A$1:$C$10000").AutoFilter Field:=3, Criteria1:="Yes"
(This will select the data after the filter applied from 1st row to 1000th row only)
Range("A1:C1000").Select
(This will select only the visible data from 1st row to 1000th row only)
Selection.SpecialCells(xlCellTypeVisible).Select
(This will copy the visible data)
Selection.Copy
(This will select the next sheet - if you want to define the sheet name "worksheets("sheet name").select" )
ActiveSheet.Next.Select
Range("A1").Select
(This will paste the copied data)
ActiveSheet.Paste
Range("A1").Select
(This will select the rawdata sheet - if you want to define the sheet name "worksheets("sheet name").select" )
ActiveSheet.Previous.Select
Range("A1").Select
Application.CutCopyMode = False
(Thil will remove filter)
Selection.AutoFilter
Range("A1").Select
End Sub
Please let me know if you have any questions.
Thanks,
Suresh Kumar S
Hi Suresh,
We are so close to the completion! Thank you so much for expalining each line for me. It really helps me in the long run.
One last request. I want to copy only selected columns from the main sheet. The current program copy and paste all the data on the main sheet. For example, I would like to copy ONLY patient ID and Yes/No columns -- not date of birth. (ie Columns A, C, and F for an example). How can I modify the current program to meet my need?
Thank you very much in advance.
We are so close to the completion! Thank you so much for expalining each line for me. It really helps me in the long run.
One last request. I want to copy only selected columns from the main sheet. The current program copy and paste all the data on the main sheet. For example, I would like to copy ONLY patient ID and Yes/No columns -- not date of birth. (ie Columns A, C, and F for an example). How can I modify the current program to meet my need?
Thank you very much in advance.
Welcome Habari,
Please replace the below code with new one
(This will select the cells after filter and copy only the visible cells)
Range("A1:C1000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
(This will select the entire columns...like A:A,C:C,F:F So visible cells code not required)
Range("A:A,C:C,F:F").Select
Modify this and try you will get it. Also i request you to try with some record macros really it will help you.
Thanks,
Suresh Kumar S
Please replace the below code with new one
(This will select the cells after filter and copy only the visible cells)
Range("A1:C1000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
(This will select the entire columns...like A:A,C:C,F:F So visible cells code not required)
Range("A:A,C:C,F:F").Select
Modify this and try you will get it. Also i request you to try with some record macros really it will help you.
Thanks,
Suresh Kumar S
Hi Suresh,
This is what I have so far but this program does not work:
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
Columns("A:E").Select
Selection.AutoFilter
Range("C1").Select
Worksheets("Main").Range("$A$1:$E$10000").AutoFilter Field:=3, Criteria1:="HB"
Range("A:A,E:E").Select
Selection.Copy
Worksheets("HB").Next.Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Worksheets("Main").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
End Sub
One of the other problem is that this program will paste the E column from the main sheet to E column in the new sheet (ie "HB"). I would like to make the E comlumn to be copied to B column in the new sheet instead.
This is what I have so far but this program does not work:
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
Columns("A:E").Select
Selection.AutoFilter
Range("C1").Select
Worksheets("Main").Range("$A$1:$E$10000").AutoFilter Field:=3, Criteria1:="HB"
Range("A:A,E:E").Select
Selection.Copy
Worksheets("HB").Next.Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Worksheets("Main").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
End Sub
One of the other problem is that this program will paste the E column from the main sheet to E column in the new sheet (ie "HB"). I would like to make the E comlumn to be copied to B column in the new sheet instead.
I think this solves the copy problem, and simplifies things down a few steps. From the code, looks like you're copying from sheet "Main" to the sheet after "HB"
[pre]
[/pre]
[pre]
Code:
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
Application.ScreenUpdating = False
With Worksheets("Main")
.Range("$A$1:$E$10000").AutoFilter Field:=3, Criteria1:="HB"
.Range("A:A").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("HB").Next.Range("A1")
.Range("E:E").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("HB").Next.Range("B1")
.Range("a1").AutoFilter
End With
Application.ScreenUpdating = True
End SubWas an assumption I made, as I wasn't able to access the uploded file. We'll remove the ".Next" portion, and that should get things back on track:
[pre]
[/pre]
[pre]
Code:
Sub moveyesdata()
'
' moveyesdata Macro
'
' Keyboard Shortcut: Ctrl+q
'
Application.ScreenUpdating = False
With Worksheets("Main")
.Range("$A$1:$E$10000").AutoFilter Field:=3, Criteria1:="HB"
.Range("A:A").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("HB").Range("A1")
.Range("E:E").SpecialCells(xlCellTypeVisible).Copy _
Worksheets("HB").Range("B1")
.Range("a1").AutoFilter
End With
Application.ScreenUpdating = True
End Sub