Bulk Copy / Pasting

[PianoMan5]

Sub FillDown()

Dim LastCOF As Long
Dim PasteCOF As Integer
Dim FirstRow As Long
Dim LastRow As Long
Dim COF As Integer
Dim GroupMove As String
Dim CopyCOF As Integer

LastCOF = Sheets(2).UsedRange.Rows.Count
Application.ScreenUpdating = False

For COF = 2 To LastCOF
GroupMove = Sheets(2).Cells(COF, 3)
Sheets(3).Select
FirstRow = Sheets(3).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Worksheets("HOLD").Select
Sheets(1).Rows("3:3").AutoFilter
Sheets(1).Range("F:F").AutoFilter Field:=1, Criteria1:=GroupMove
Sheets(1).Rows("4:65000").Copy Destination:=Sheets(3).Cells(FirstRow, 1)
Sheets(1).Rows("3:3").AutoFilter
Sheets(3).Select
LastRow = Worksheets(3).Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For CopyCOF = FirstRow To LastRow
Cells(CopyCOF, 6) = Sheets(2).Cells(COF, 2)
Next
Next
Application.ScreenUpdating = True
End Sub


Above is the code that I'm having trouble with...I got so frustrated that I made it line by line so I apologize with how elementary it looks.

Anyways, I want the macro to look at each value in column B on a specific sheet (account#), see what's in the cell next to it (group), and look at another sheet and do a bulk copy of all values that's in the group and paste it to a new sheet with the account# to the left...in laymens terms, I don't want to have to copy something 1000 times over.

Any help? :doh:

[karrotman]

I'm sorry, not sure I understand. So you have account and group:

Account: Group:
12234 ABC
11224 ABC
2344 CFG

So you just want to bulk copy these arrays onto another sheet?

[PianoMan5]

karrotman...you are correct.

For each account# copy all the data that exists for the requested group to another sheet. For an example, if there are 5 separate account #s and each one has 3 different groups (of 10 lines each), then the total amount of rows after completition should be 150 rows.

[karrotman]

I think I understand: You don't want to do the copy cell by cell. Here is what I would recommend. Keep in mind I am certainly no pro but this should work.

Lets say the column "Account Number" populates column "B".


So the title is B1, the first account number is in B2. Click on B2 and then name the cell "FirstAccountNumber".
In your code loop down account number column until you hit an empty cell. Name the last filled cell "LastAccountNumber".
Now in VBA name the range ("FirstAccountNumber","LastAccountNumber").Name = "AccountArray".
You can now simply in one line copy Range("AccountArray") to anwhere else.Does this work for you?