VBA Copy range of data from multiple sheets & paste into single column of summary sheet

[Susi D]

HI,

I am very new to VBA and using Google to self teach!

I have data on several sheets (sheet 1-sheet14)
with data in cells (dh13-fc113)- these are concatenation of names and labour activitites/areas
and also data in in cells (fe13-gz113) - these are labour hours

I want to copy the data from the above cell ranges on each sheet and paste into a single column of a summary sheet, splitting out the concatenation into separate columns, whilst removing entire rows where there are "0" hours to produce the following output on the summary sheet:
Col A Name
Col B Activity
Col C hours (excluding 0)

So far, i have managed to pull together a macro, which gives me most of my desired output without splitting out the concatenation, but only for an individual sheet, if anyone can help me get this the rest of the way, or suggest an alternative to give me the output i need , i would be very grateful!

I have attached an image of the VBA i have currently which works for a single sheet with the concatenation in tact and the output it is providing,

I am using Office 365

Thanks in advance

 

[Micron]

Not sure what it is you need - looping or splitting, or both. Google how to loop over workbook sheets? Maybe also look into Split function and then loop over the resulting array to insert the array elements into cells as required. Might need to use Offset property to insert the array elements into the correct cells. That should work as long as either a) every row will produce the same size array to be offset or b) each array will not be the same size but it's OK if the values are inserted left to right and values don't have to line up with each other.

When you post code, copy and paste within code tags (use vba button on posting toolbar) to maintain indentation and readability. Pics of code are of little use.