G
Guest
Guest
So far i have this code.
It is supposed to do this:
1. Go to a user defined sheet
2. search down a column finding a specific piece of text
3.when it finds the right cell. use the offset command to find pieces of data
4.copy and paste this data to another sheet
5. and add all accumulated data to the bottom of a table.
6. and then carry on searching through that column for more of the same criteria
Dim List As Object
Dim Cell1 As Object
Set List = Range((Range("MonthCHoice").Value) & 1)
Sheets(Range("MonthCHoice").Value).Select
For Each Cell1 In List
If Cell1 = Range("CourseName") Then
ActiveCell.Copy
Range("G7").Select
Selection.Copy
Sheets("Lists").Select
Range("Course45").Select
ActiveSheet.Paste
Sheets("January").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lists").Select
Range("Attendees45").Select
ActiveSheet.Paste
Sheets("January").Select
ActiveCell.Offset(0, -6).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lists").Select
Range("Date45").Select
ActiveSheet.Paste
Range("Row").Select
Selection.Copy
Range("Dummy").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets(Range("MonthCHoice").Value).Select
End If
Next
The problems that i am having are that. It finds the first data okay. Then it copies it like i want it to. Then say i have five rows of the same criteria, it would create five rows onto the table, but use the same data for each, instead of using its own data. (also when there are four rows of the same criteria, four rows of the same data would be added). This is related to my earlier post, because the final table would be the range I want to name. Does anyone see any errors or anything wrong with the code. I have been trying to do this all day!
It is supposed to do this:
1. Go to a user defined sheet
2. search down a column finding a specific piece of text
3.when it finds the right cell. use the offset command to find pieces of data
4.copy and paste this data to another sheet
5. and add all accumulated data to the bottom of a table.
6. and then carry on searching through that column for more of the same criteria
Dim List As Object
Dim Cell1 As Object
Set List = Range((Range("MonthCHoice").Value) & 1)
Sheets(Range("MonthCHoice").Value).Select
For Each Cell1 In List
If Cell1 = Range("CourseName") Then
ActiveCell.Copy
Range("G7").Select
Selection.Copy
Sheets("Lists").Select
Range("Course45").Select
ActiveSheet.Paste
Sheets("January").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lists").Select
Range("Attendees45").Select
ActiveSheet.Paste
Sheets("January").Select
ActiveCell.Offset(0, -6).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Lists").Select
Range("Date45").Select
ActiveSheet.Paste
Range("Row").Select
Selection.Copy
Range("Dummy").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets(Range("MonthCHoice").Value).Select
End If
Next
The problems that i am having are that. It finds the first data okay. Then it copies it like i want it to. Then say i have five rows of the same criteria, it would create five rows onto the table, but use the same data for each, instead of using its own data. (also when there are four rows of the same criteria, four rows of the same data would be added). This is related to my earlier post, because the final table would be the range I want to name. Does anyone see any errors or anything wrong with the code. I have been trying to do this all day!