Cells Selection Question

[rjacmuto32]

I have a table which the amount of rows changes each month. I have about 10 columns on it always the same. Column A is a project ID. Sevearl rows can contain the same project id.


I want to keep my file size down. Is there any way to copy say Columns C-H anytime Column A contains a certain project ID? I tried to record the macro but it puts the row number in the macro. However my row number varies depending on the project id. How do I ignore the row number.


Project ID = 977 is on say 3 rows in my table (they are in consecutive order). How can I copy columns C-H for those 3 rows and paste into another sheet? I know I can do this via pivot table but that doubles my filesize. Anyway to do it based off the data table itself without a pivot?

 

[fred]

I'll get a Pivot table. Copy and paste the data from PT onto another worksheet as value. come back and delete the PT. That should not double your file size.

 

[rjacmuto32]

Ahh I didn't think to create the pivot table and delete it. Good idea. I will play around with that now.


I did find a code to run thru the data to copy certain cells being this. My question about this is at the end of that code goes to sheet3, and pastes the data into Column A offsets to the next row. What if I always want to paste it into B45 and then offset every one down from there. How would code work? (There are seveal blank rows above B45 on my summary page) So I don't think row count would work?


Sub cpy()

Dim LR As Long, i As Long

With Sheets("Sheet1")

LR = .Range("A" & Rows.Count).End(xlUp).Row

For i = 1 To LR

If .Range("A" & i).Value = "CITI-PROJECT-00139-2011" Then .Range("B" & i & ":E" & i).Copy

Destination:=Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1)

Next i

End With


End Sub