I have a Workbook that contains a number of worksheets related to estimating costs. On one worksheet I have a several sections that use drop down boxes to pick the material of choice, one material per cel. I have 10 empty cels per section that can be filled or not depending on what is required.
What I would like to do is have any cels which have a material selected in them to be copied to another worksheet to make a materials list. As I said there are a number of sections, each with a partial list. Ideally, all the information from these sections would be copied to the other worksheet and fill cels with no empty spaces between them.
In short, perhaps 2 cels from column A, 5 cells from column B and 1 cel from column C all forming a sequential list in worksheet - Materials.
Any suggestions on how to accomplish this? Thanks!
So you would like to check a specific range for data. When data is found copy each cell to a single column on another sheet. If this is true then the following should work for you:
Sub MoveData()
For Each cell In Sheets("Sheet1").Range("A2:C10")
If cell.Value <> vbNullString Then cell.Copy _
Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell
End Sub
