Solution for charity

[mwfmtl]

Hi all, I am a volunteer for a charity, we have a foodbank, youthclub for boys and girls and various other classes.

We use a powerapp to do the register which populates "present" to each user(row) who is present to column name "Attendance". All I need is a script or macro that will create a column at the end with todays date and then cut the values from attendance to the new created column. I need this to run once a week which i can do via power automate.

I am by no means a coder but I did give it a good try. The below creates the column using today date.

function main(workbook: ExcelScript.Workbook) {
var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();
let x = dd + '/' + mm + '/' + yyyy;

let sh = workbook.getActiveWorksheet()
let tbl = sh.getTable("FBRegister")
tbl.addColumn().setName(x)

}

the below is what doesn't work : m2:m170 being the current attendance column. is it possible to call a variable in get range?

fBRegister.getRange("M2:M170").moveTo(fBRegister.getRange("x"));

Any advise will be appreciated.

 

[mwfmtl]

the first part was done using typescript to the the column based on today date.

 

[Joe4]

the first part was done using typescript to the the column based on today date.

Welcome to the Board!

I am not familiar with that code (as I suspect many here at this Excel forum aren't).
Is there some reason why you cannot use Excel VBA code?
That is our specialty (being an Excel forum).

 

[mwfmtl]

Sorry I am happy to use excel macro instead.

1. Create a column at the end of the table with today’s date.
2. Cut values from column named “attended” and paste into the newly created date column.

This macro will run weekly via powerautomate and will allow us to do register weekly on the app.

 

[Joe4]

I am not quite clear. Are you then looking for a PowerAutomate script, or an Excel VBA script?

Also, if would probably be beneficial to see a sample of the data you are working with.
Is this an actual table in Excel?
Can you post a small sample of your table with data so we can see its structure?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[mwfmtl]

Excel VBA script. I mentioned powerautomate as that will run the macro automatically for me once a week unless we can code that into vba too. mini sheet below

FBRegister1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Household	Date Of Birth	Email	Gender	House Number	First Line of Address	Postcode	Mobile	Attended	ID	__PowerAppsId__	1/1/2023	8/1/2023	15-01-2023	23/01/2023
2	Abdul Razzak	4	07/03/1956		Female	14	court avenue	E13 5ER	755555555	Present	136	5nQ7qYAuVlI	Present	Present	Present
3	Jo bells	2	07/03/1956		Female	86	st georges road	N5 6BT	755555555		139	ju51kZjB2dQ	Present	Present	Present
4	David Lay	1	07/03/1980		Male	56	downsell road	K6 5ER	755555555	Present	150	LCU0Ru5a1Ao	Present	Present	Present
5	Harsil Patel	5	01/01/1981	manshamuthir@gmail.com	female	5	newport road	G6 4LR	755555555		151	_r0cRHgtXss	Present	Present	Present
6	John Stones	3	07/06/1956		Male	23	jupiter street	G7 8TT	755555555	Present	141	SGA0a3ePJe4	Present	Present	Present
7	Sam Clark	2			Male	21	Holland avenue	F6 9TT	755555555		119	FShErTPFq-I	Present	Present	Present
FBRegister

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	Cell Value	="Female"	text	NO
E2	Cell Value	="Male"	text	NO
E3	Cell Value	="Female"	text	NO
E3	Cell Value	="Male"	text	NO
E4	Cell Value	="Female"	text	NO
E4	Cell Value	="Male"	text	NO
E5	Cell Value	="Female"	text	NO
E5	Cell Value	="Male"	text	NO
E6	Cell Value	="Female"	text	NO
E6	Cell Value	="Male"	text	NO
E7	Cell Value	="Female"	text	NO
E7	Cell Value	="Male"	text	NO

 

[Joe4]

So, you just want to add a new column to the end of your table, and populate every record in that new column with the value of "Present"?
Is that correct?

 

[mwfmtl]

So, you just want to add a new column to the end of your table, and populate every record in that new column with the value of "Present"?
Is that correc

 

[mwfmtl]

So, you just want to add a new column to the end of your table, and populate every record in that new column with the value of "Present"?
Is that correct?

Hi Joe,

Thanks for looking into this. Yes I need a new column named today date. Then i need it populated by the values in column J “Attended”

then i need column J cleared so it has no values. Hope that makes sense?

 

[Joe4]

OK, try this:

Sub MyAddColumn()

    Dim colName As String
    Dim lc As Long
    Dim lr As Long
    Dim tbl As ListObject
    
'   Set table object by name
    Set tbl = ActiveSheet.ListObjects("FBRegister")
    
'   Get new column name (using today's date)
    colName = Format(Date, "dd-mm-yyyy")
    
'   Find last column in table
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Find last row in table with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Insert new column to table
    tbl.ListColumns.Add lc + 1
    tbl.HeaderRowRange(lc + 1) = colName
    
'   Copy data from column J
    Range("J2:J" & lr).Copy Range(Cells(2, lc + 1), Cells(lr, lc + 1))
    
'   Clear column J
    Range("J2:J" & lr).ClearContents
    
End Sub