copy column to the right if cell is not blank

[igorburger]

Hi experts,

I'm a complete newbie and have searched the internet for some time trying to find sample vba scripts helping me achieve my goal. In order to explain what I need, please also see the attached workbook. In short, I have a worksheet containg names of partipants in monthly activities. The number and names (can) change per month. I also have "monthly worksheets" which basically list the type of activities, the costs hereof and how the costs are divided over the participants.

I now manually copy and transpose the participants from sheet1 to each monthly sheet. What I would like to do is automatically copy the individual names from sheet 1 (participants) to each monthly worksheet and when a "new" name is added in row 4 of a monthly-sheet (in the attached workbook for month Feb f) I would like to copy the contents of the preceeding column (from row 5 down to row 69). Actually since all columns are the same, it doesn't have to be the preceeding column but any column from H onward can be used. I can't find the correct formula to achieve this copy-action

I do already use a vba script which deletes the column if a cell in row 4 turns up empty. Thus, the sheet for August (where a and b no longer participate and therefor return cells h4 and i4 empty) will, after executing the macro, delete the entire columns h and i. The following vba script is taken from the original workbook:


Sub del()
Dim i As Integer
With Sheets("Jan")
For i = 141 To 8 Step -1
If IsEmpty(.Cells(4, i)) Or .Cells(4, i) = "" Then
.Cells(4, i).EntireColumn.Delete
End If
Next
End With
End Sub

I would be very grateful if anyone could help me automate the copy/transpose participants names and fill the costs/formula column in case a new participant starts participating.

[Richard Buttrey]

Hello and welcome to the forum.

I can't help thinking you are approaching this from the wrong angle. Data capture and Data reporting require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report or which at first glance seems the best way of capturing data, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

So before you get too far with this I'd create a single sheet database that contains the all your current columns, but with additional columns for Month, Activity and whatever stuff like "Prijs p.p." represents

Your then is a much better position to report individual months, if that's your goal, either by one of the Filtering mechanisms or a Pivot Table.

[igorburger]

hi Richard,

thanks for your prompt reply. You may very well be correct by stating the approach is all wrong, but in most cases (certainly mine) that wil be the case when adopting a file which has been around and used for quite some time and one wants to make it somewhat smarter instead of revising it as I lack those skills as well. The file is used to calculate each participants contribution fee and, based on attendance, how much he/she is due by the end of each month.

Each month activities vary, so does the number of participants. That is why there's a monthly sheet with an overview of that month's activities and participants rather then having one "big" sheet with all columns, names, prices etc representing a full year. Although I see what you mean by the monthly reports (currently monthly worksheets) being more simple to generate in any which way I'd want, I'm concerned with the (eledged) complexity from an input side.

In the end what this does (or belong to) is a financial reporting with A/R and A/P together with a P&L. The input is being done by the person of the foundation who is at a given time in charge of finance of the specific foundation. The way it was set up (i.e. per month) allows anyone with little to no skills to come up with a correct reporting. Changing this as you suggest would/could also require more skills or more control.

I thought my goal to be achieved only required the skill to change participants names on the participants sheet (aside from entering the price per person per activity on a monthly-sheet). I did not think about a complete do-over of the existing sheets into one because I thought it would be not as clear due to the amount of data and limited screen size and therefor more room for error. I will give it some thought based on your recommendation though.

KR Igor