Code to copy text from one sheet to another if the value of a cell is above 0

[darrensconfused]

Hello Everyone

I am very new to VBA so i am at a loss with this code i have tried copying existing codes from other posts but havent had any luck as yet. The workbook is essentially a price book and i am trying to create a macro that will copy the text from the woksheet "flexible duct" to the "summary" worksheet if the value of F9:F18 is above 0

it would be amazing if the pasted text would autofit into the cells of the summary sheet.

any help would be greatly appreciated as i am at a loss

Thanks

Darren

i have posted a link to my workbook below

https://www.dropbox.com/s/r4m9l4ud9d9gz33/ADA Price Book.xlsm?dl=0

 

[darrensconfused]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

Hey guys

the code seems to be very spiratic and will not paste the copied data to a specific location on the summary sheet is there any amendments i could make to the code to correct this ?

i get an error at the moment it says -

cant execute code in break mode

 

[Michael M]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

Did you see my comment in Post #10 ??
For the error...go back to the editor window and press the square blue reset button on the toolbar to stop the code !!

 

[MARK858]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

I'm also still a bit concerned that the OP might not have the "Flexible Duct" sheet active when running the code.

 

[darrensconfused]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

Hello Guys

yes i do have the "Flexible Duct" sheet active while running the code and yes i did miss your post about resetting the macro in Post#10.

Unfortunately i don't believe the code is actually what i am looking for but i thank you all very much for your help, it seems that every time the code is executed it copies all entries to the summary sheet. Perhaps my fault in the description of what i was looking for in my original post.

The workbook will be used by many users and users will need to input quantities in the various sheets "Flexible Duct"(Rows F & Q) , "Plastic outlets" (Rows F & O) etc then only the cells that have a quantity entered i would like to copy to the "product code" & "description" to the summary sheet to create an order of the goods entered on the various sheets.

Once again i thank you all for your time and effort in helping me with the issue.

Darren

 

[Michael M]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

So your saying step through each sheet, except the "ADA" sheet, check to see if any quantites have been input into either col "F" AND / OR Col "Q" then copy the row "A:H" AND / OR "L:S" to the "Summary" Sheet

 

[darrensconfused]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

Hello Michael

Yes that is the outcome i am looking for except copy row "A:C" or "L:N" to the "Summary" sheet , i may need to put all my text into uniform rows across my various sheets in the workbook

 

[Michael M]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

Ok, maybe this....BUT....the version I have from dropbox that you uploaded has merged cells all over the place. Merged cells and VBA can be a real pain and cause plenty of issues when copying

Sub MM1()
Dim r As Long, lr As Long, ws As Worksheet
lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
For Each ws In Worksheets
    If ws.Name <> "ADA" Then
    ws.Activate
        For r = 9 To 18
            If Range("F" & r).Value <> "" Then
                Range("A" & r & ":C" & r).Copy Sheets("Summary").Range("A" & lr)
                lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
                End If
            If Range("Q" & r).Value <> "" Then
                Range("L" & r & ":N" & r).Copy Sheets("Summary").Range("A" & lr)
                lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
            End If
        Next r
    End If
Next ws
End Sub

 

[Michael M]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

I have just noticed that you Qnty columns are different on each page .....SO.....what I have posted works for 1 page and not others.
So you will need to modify code to suit OR modify your sheets to suit code !!

 

[darrensconfused]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

Hello Michael the new code works much better thank you so much for your assistance with this, i just have a few questions if you don't mind ?

how can i change the location the text pastes to on the "Summary" sheet and is it possible for this macro to run constantly in the background so it doesn't have to be executed each time ?

Thanks again for your help.

p.s i uploaded a new copy of the worksheet without merged cells. also your code on the workbook

https://www.dropbox.com/s/r4m9l4ud9d9gz33/ADA Price Book.xlsm?dl=0

 

[Michael M]

Re: Please help! looking for code to copy text from one sheet to another if the value of a cell is above 0

change the location the text pastes to on the "Summary" sheet

Where do you want it to go ??

is it possible for this macro to run constantly

yes, but it a better idea might be to run it on save / on calculate / on sheet change / before close or something else!!