yes, give specific example.
Hi mic
Welcome to the board.
This code will copy all cells from all sheets, except one called "CollectAll". The sheet CollectAll is the sheet used to paste all data to as Values only.
Sub AllCellsAllSheets()
Dim rCell As Range
Dim wSheet As Worksheet
For Each wSheet In Worksheets
'Name of sheet to paste to
If wSheet.Name <> "CollectAll" Then
For Each rCell In wSheet.UsedRange
rCell.Copy
Sheets("CollectAll").Range("A65536").End _
(xlUp)(2, 1).PasteSpecial xlValues
Next rCell
End If
Next wSheet
End SubSorry. Still confused. Have you created "Stock Compile" manually from Stock and you are looking for the VB code to do this?
Hi Joelsmally,
Yup, I create the 'stock compile' manually from stock. Reason is that I do not know how to create loop to check the first row in a range before going to the second row.
The codes below only check for the first cell that has number on it but it does not proceed further.
Sub GetBlankCell()
Dim myRange As Range, cell As Range
Set myRange = ActiveSheet.Range("C121:C141")
For Each cell In myRange
If cell > "0" Then
MsgBox cell.Value
Range("c4") = cell
Exit For
End If
Next cell
End Sub
How can I improve this code further so that it will check all the cells within the range of (C121:C141), which is in row 8, copy each individual cell into another workbook under selected columns and proceed the next range in row 9?
Sorry for the trouble but I believe this is the one that I have problem with.
Thanks! 
Don’t have an account yet? Register yourself now and be a part of our community!