Creating a Workbook with a Series of Macros

[Stacker]

Hi I wanna create a workbook containing all the macros I am likely to need.

I will have a table already downloaded and the first macro I wanna create involves adding additional columns from AU with these headings

Postcode

ID_for_db

Firstname_for_db

Lastname_for_db

Postcode_for_db

Address_for_db

Employer_for_db

AU= =IF(LEN(J2)=6, LEFT(J2,3)&" "&RIGHT(J2,3), LEFT(J2,4)&" "&RIGHT(J2,3))
AV= =[@ID]
AW= =[@[Patient Forename]]
AX= =[@[Patient Surname]]
AY= =[@Postcode]
AZ= =IF([@[Preferred Address Line 1]]="",[@[Preferred Address Line 2]],[@[Preferred Address Line 1]])
BA= =[@Employer]

The second macro is similar. I want to add columns with these headings from CB

Postcode2

ID_for_db

Firstname_for_db

Lastname_for_db

Postcode_for_db

Address_for_db

Employer_for_db

CB==IF(LEN(J2)=6, LEFT(J2,3)&" "&RIGHT(J2,3), LEFT(J2,4)&" "&RIGHT(J2,3))
CC= =[@ID]
CD==[@[Patient Forename]]
CE==[@Surname]
CF==[@Postcode2]
CG==[@[Address Line 1]]
CH will have no formula


I will then save these files based on the most recent date.

I am not sure if it's possible to automatically execute a SQL query, then import the most recent files to a certain destination and close afterwards. I get the data from a power BI dashboard with a URL like https://app.powerbi.com/groups/me/apps/....... I have power BI on my computer so maybe I can automate this.

 

[Stacker]

Does anyone have an idea how to create this macro?

 

[Alex Blakenburg]

Can I assume you have an actual Excel Table that already extends from A to AT ?
Please provide the sheet name and table name and a copy of the headings of the table preferable as an XL2BB or at least a copied in the way you copied in the additional headings your provided in Post #1

Also the table headings at AU are the same as those at CB, an Excel Table is not going to like that. How do you want to handle that ?

 

[Stacker]

Can I assume you have an actual Excel Table that already extends from A to AT ?

Yep


Please provide the sheet name and table name and a copy of the headings of the table preferable as an XL2BB or at least a copied in the way you copied in the additional headings your provided in Post #1

the Default name of the download is the default sheet 1

 

[Alex Blakenburg]

sheet name = sheet1
table name = ?
heading of the table A to AT = ?
the table headings at AU are the same as those at CB, an Excel Table is not going to like that. How do you want to handle that ?


Also there is a still a significant gap of columns between the end of the headings at AU and the headings at CB. If CB is to form part of the same table there needs to be headings between the 2 sections.

 

[Stacker]

I should clarify these are two seperate macros for two separate files to be downloaded. Both files will be downloaded and be slightly different with different headings.

In the first macro Postcode will be the heading in AU ... and so on until Employer_for_db will be the heading for BA

 

[Alex Blakenburg]

Ahh.
ok this has the code you need in a single macro. You probably know enough to take from it what you want for each of your 2 macros.
I am login off for the night back tomorrow.

Sub test()

    Dim arrHdg1 As Variant
    Dim arrHdg2 As Variant
    Dim arr As Variant
   
    ' XXX Check Preferred Address Heading and postion
    arrHdg1 = Array("Postcode", "ID_for_db", "Firstname_for_db", "Lastname_for_db", _
                    "Postcode_for_db Address_for_db", "Preferred Address Line 1", "Employer_for_db")
    arrHdg2 = Array("Postcode2", "ID_for_db", "Firstname_for_db", "Lastname_for_db", _
                    "Postcode_for_db Address_for_db", "Preferred Address Line 2", "Employer_for_db")

    Range("AU1").Resize(1, UBound(arrHdg1) + 1).Value = arrHdg1
    Range("CB1").Resize(1, UBound(arrHdg2) + 1).Value = arrHdg2
   
    ' Headings1 section
    Range("AU2").Formula = "=IF(LEN(J2)=6, LEFT(J2,3)&"" ""&RIGHT(J2,3), LEFT(J2,4)&"" ""&RIGHT(J2,3))"
    Range("AV2").Formula = "=[@ID]"
    Range("AW2").Formula = "=[@[Patient Forename]]"
    Range("AX2").Formula = "=[@[Patient Surname]]"
    Range("AY2").Formula = "=[@Postcode]"
    Range("AZ2").Formula = "=IF([@[Preferred Address Line 1]]="""",[@[Preferred Address Line 2]],[@[Preferred Address Line 1]])"
    Range("BA2").Formula = "=[@Employer]"
   
    ' Headings2 section
    Range("CB2").Formula = "=IF(LEN(J2)=6, LEFT(J2,3)&"" ""&RIGHT(J2,3), LEFT(J2,4)&"" ""&RIGHT(J2,3))"
    ' XXX Currently not an extension of the table so will need the table name in the reference
    Range("CC2").Formula = "=[@ID]"
    Range("CD2").Formula = "=[@[Patient Forename]]"
    Range("CE2").Formula = "=[@Surname]"
    Range("CF2").Formula = "=[@Postcode2]"
    Range("CG2").Formula = "=[@[Address Line 1]]"
    'Range("CH2").Formula = "=[@Employer]"  - No formula

End Sub