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

 

[DanteAmor]

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

Welcome to the forum

You could upload a book with data on the "flexible duct"sheet and that data on the "summary" sheet to see where it is copied from and where it is pasted.

 

[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 Dante

Thanks for your kind welcome

the forum wont allow me to upload the workbook but i did link it from my dropbox account for anyone to look at. Perhaps a bit more information might also help, the worksheet "flexible duct" has a quantity box so what i would like the formula to do is once the quantity is entered into the box the text from row A and Row C get copied to the summary sheet for only the cells that indicate a quantity. so essentially the summary will become my order sheet to send to the supplier.

Thanks again for your help

 

[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 why only F9:F18....are no other cells involved ??
AND
Get rid of the merged cells on the Summary Sheet !!.....use Rclick>>Format cells>>Alignment>>Horizontal>>"center across selection" instead

Sub MM1()
Dim r As Long, lr As Long
lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
For r = 9 To 18
If Range("F" & r).Value <> "" Then
    Range("A" & r & ":H" & r).Copy Sheets("Summary").Range("A" & lr)
    lr = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
Next r
    
End Sub

 

[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

thanks very much for your help you are correct i would like to apply the formula to more cells than F9:F18 but i thought i may be able to replicate the code across the rest of the sheets.

i followed your instructions and got rid of all my merged cells and tried the code but unfortunately although the macro ran it had no results on the spreadsheet.

Thankyou so much for taking the time to try and help me.

 

[MARK858]

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

Is sheet "flexible duct" active when you ran Michael's code? and have you put any values in F9:F18 as there weren't any in your upload?

 

[MARK858]

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

Are you also sure that the data hasn't pasted to row 48? and did you also change the merged cells and remove the protection on the "flexible duct" 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

Are you also sure that the data hasn't pasted to row 48? and did you also change the merged cells and remove the protection on the "flexible duct" sheet?

Hello Mark

yes i removed the protection on the sheet and removed every single merged cell and the code worked which was fantastic, as you mentioned the code did paste to Row48 on the summary sheet which is cool i can try to change that. Thanks everyone for your input into my issue it has been a fantastic help and a good start

 

[MARK858]

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

If you move Order Totals from A47 to B47 it should paste to the correct location

 

[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 also noted that CTRL + End takes the cursor to row 100..
So, if you select say row 50 then press CTRL +SHIFT+Down Arrow and delete ALL the selected rows, it might help with the lr problem as well