Excel 2010 - copy filled in cells

[Closed]
Report
-
Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
-
Hello,

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!

1 reply

Posts
2818
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 18, 2021
486
Hi Chris,

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

Best regards,
Trowa