Macro for copy [row] cell range if [column] cell value is greater then (0) then past text only.Trouble

trickedout · Feb 2, 2014

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 row (14) 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, 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.

https://www.dropbox.com/s/go5t2dwr9nfmi2d/MASTER MACRO TRANSFER.xlsm Main worksheet

https://www.dropbox.com/s/23odxlil8rpa0xi/CHASE MACRO TRANZ 1-23-14.xls What email sheet lools like after coper paste delete unneeded cells>

Thank You all for looking at my post
Trickedout TD

trickedout · Feb 6, 2014

I know I’m way off but this is what I have so far, it will copy some cells to the sheet I need. It’s still way off, I would really appreciate some help is anyone has the time!!!

Code:

<code>Sub Macro2() ' ' Macro2 Macro ' ' Keyboard Shortcut: Ctrl+x '     Dim sourceRng As Range     Dim cell As Range     Dim i As Long      Set sourceRng = Sheets("DELPHI-HR-HD").Range("k1:k860")     i = 1      For Each cell In sourceRng         If cell.Value > 0 Then             cell.Resize(1, 2).Copy Destination:=Range("A" & i)             i = i + 1             End If         Range("F8:M8").Select     Application.CutCopyMode = False     Selection.Copy     Sheets("Sheet1").Select     Range("A1").Select     Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _         :=False, Transpose:=False                  Next cell End Sub</code>

trickedout · Feb 6, 2014

Code:

Sub atest()
Dim LR As Long, i As Long
With Sheets("sheet1")
    LR = .Range("K" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With .Range("K" & i)
            If .Value <> "" Then
                .Offset(-5, 3).Copy Destination:=Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Copy Destination:=Sheets("sheet2").Range("G" & Rows.Count).End(xlUp).Offset(1)
            End If
        End With
    Next i
End With
End Sub

a little closer: but I thing the problem is that I need a string range, and this is not set as a string.. some help would be really nice at this point going nuts over here.

Macro for copy [row] cell range if [column] cell value is greater then (0) then past text only.Trouble

trickedout

New Member

Excel Facts

trickedout

New Member

trickedout

New Member

Similar threads

Forum statistics

Share this page

Macro for copy [row] cell range if [column] cell value is greater then (0) then past text only.Trouble

trickedout

New Member

Excel Facts

trickedout

New Member

trickedout

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock