Trouble with Excel 2010 Macro Copy cell range with values

[trickedout]

First off>> I would like to say Hi to all you awesome people on this forum. Yep a little brown nosing goes a long way>LOL. I really haven’t had the use for excel that much in my life, even though I have been in the PC would for about 8 years hard, just over one year ago I was hired into my new job for my mgmt. and computer experience, and it turned out they thought PC, exp. Meant a Microsoft Office master. Needless to say I ran out bought an excel 2010 book and stated learning. I have leaped great distances since that day in excel, but still have a long way to go . This macro sheet attached is my first large build. I deleted all but 4 tabs to down size for uploading. I am in need of some macro help! I have made the keyboard macro’s I need for unhidden columns as I need them. And some other useful macro’s, but I’m at a loss on the macro I need to make my job and life so much easier.

Ok so hear it goes, on the attached excel sheet you’ll see column (L) that is my spin button column, column (K) is my numeric or counter column were the spin button puts the count of incoming parts’. I color coded some examples of what I need. You will also see there are two part# lines on each tab, on the color coded tab DELPHI-HR-HD the column (x) has the value on the right side of the work sheet.

I need a keyboard macro to copy all the cells on the entire sheet highlighted in blue and the cell in red, when there is a sum greater than zero in red column (K). You will see that I highlighted rows (5) green (6) blue (7) orange, let me explain this. The green you will see (DELPHI CVC SMALL BODY #6562043) the part description. A sum in this row means the part was good. The blue [PWDC] (PUMP WITH DAMAGE CLUTCH) > ORANGE [PNC] (PUMP NO CLUTCH)>>> so blue and orange are the same part as green but with defects. So if there is a sum greater then (0) in blue or orange I will need green copied as well. Even if green has no sum, as green holds the part name. But if the sum is in the green row only, I only need green copied, if there were a sum in all three I would need all three, as you can see on rows (11) (14) and (20)the sum is in the part description row, so that is the only row I would need copied of this part #. But on the part above I would need (6) blue and (5) green copied. You will see some tabs below there is about (30) on this sheet. I deleted them for this forum so you can see if a lot of parts come in, I’ll have a lot of manual coping and pasting to do lol. I need the macro to do this throughout the entire sheet on every tab. MY job description with this excel sheet is as follows. After an incoming load in checked in, I go through all the tabs, copy all row’s with a sum (explained above), and paste it to a separate work sheet. Then email that to the Bose to do price changes if needed, and then import the emailed sheet into our inventory software. I have been trying to write this macro with no luck. Just can’t get a grasp on this one as of yet. If I did not explain this well enough or too much let me know I will email, call, or forum chat whatever it takes. Also columns (G) (H) (I) (J) AND (T) (U) (V) (W) are hidden while the work sheet is in use. I unhide them just for my part only. PS, I forgot to mention that the copied cells get pasted into the emailed work sheet as, paste text only.

PS. If this can’t be done then a macro to copy the blue and red cells if there is a sum greater than zero in the red, will be a great help!! Or a macro to copy all green, blue and orange if there is a sum in any one of the three rows. Once I paste into my other sheet I can sort by QTY and delete. Any help will be much appreciated.

Thank You all for looking at my post
Trickedout TD

[trickedout]

No problem: I will retitle and hope that it is following the rules. I did read the rule book ones over, I guess it wasn’t enough,lol. Newbies are thinking of their problems more than the rules.

Thank You for heads up and the reply.

[trickedout]

Well this is where I am with the (). First time with VBA (). I am getting an error 1004.
I believe it is because I'm not Obtaining Range Values correctly. I want excel to copy (f2:m2) (f2:M147). If Value > (0) in column (K) (K2:K147).I uploaded the excel worksheet in the first post. No reply's as of yet. All my tabs are named on the sheet so I wanted to leave the last tap named sheet1 , have it copy to that sheet in paste special text ONLY. I know you all are busy but I have put about 18 hours into this and without any advice at all. With no experience at VBA () except recording macro’s and a little editing. Any help would be appreciated,

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
   Dim sourceRng As Range
   Dim cell As Range
   Dim i As Long
   
   Set sourceRng = Worksheets("DELPHI-HR-HD").Range("F2:M147")
   ActiveSheet.Cells(1, 1).Select
     i = 1
     
     For Each cell In sourceRng
     If cell.Value > 0 Then
     cell.Resize(1, 2).Copy
     Destination = Range("m" & i)
     i = i + 1
     End If
     Next cell
     
     
     
   
   
End Sub 

[trickedout]

I haven’t had any feedback or advice since my original post. But after about 10 hours of reading I am getting a little closer. Any help or advice would me greatly appreciated.

[trickedout]

Please Login or Register  to view this content.

Well I am a little closer but this is not set up as a string, and I need a string range I think. But it looks like I am the only one posting here. So again some help would be nice. Am I not sucking up to the right person or what?? I'm watching post after post getting answer's that were posted after mine what gives. Just stating facts, not being rude even though you will take it that way.