VBA Code to add a new section of code to a different exsisting Macro

[JenksNo1]

Hi All,

Not sure if this is possible but thought I would ask and see if anyone knows a way of doing it.

I have made a a spreadsheet for imputting payments for our clients which has several buttons with Macros that turns the information that is put into the spreadsheet into reorts and CSV file which are then uploaded to another system. I am trying to create a macro for a button which adds in a new tab (each individual client has it's own tab) which I have managed to do. The only issue is that the report and CSV macros copy the information from each individual tab (i'm sure there is a neater/simpler way of doing it but it works for me so far).

My question is is there a way of writing part of the new customer code that will add in a new section of code in the reports and CSV modules, which will read the name of the new client that has been added? Obviously I can just add the code in manually however I am trying to make it automated in case I am not in the office and my co-workers need to add a new client for a payment.

To give you an example of what the code would look like that would need to be added it looks something like this:

Sheets("Client Name").Select - This would need to be the name of the new client that is added in
Range("E150:A3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Supplier Import.csv").Activate
Range("A1").Select
ActiveSheet.Paste
Windows("Payroll Import Spreadsheet.xlsm").Activate
Sheets("Client Name").Select
Range("k150:g3").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Supplier Import.csv").Activate
Range("A300").Select
ActiveSheet.Paste
Windows("Payroll Import Spreadsheet.xlsm").Activate

Hope that makes sense - any further clarification let me know!

Cheers

 

[Norie]

Couldn't you just use a variable for the client name?

That variable could get its value using various methods, eg it could read from a sheet or have the user enter the name via an inputbox.

 

[JenksNo1]

Couldn't you just use a variable for the client name?

That variable could get its value using various methods, eg it could read from a sheet or have the user enter the name via an inputbox.

Hey Norie,

Cheers for the reply.

The new Tab is added through an Input box which automatically adds the tab to the worksheet, i'm just stuck on how I get the report buttons to know that the new tab is there and copy the information that is put on the sheet. Obviously i've written the macro quite manually so if there is an easier way of doing that which will incorporate the new tab that has been added even better!

I'm self taught so no doubt there is an easier way of doing it!

 

[Norie]

Are you repeating this code, or something similar, for each client?

Do you have a list of clients?

 

[JenksNo1]

Yes the code is the same for each client (apart from the cells that it is pasted into on the new sheet) and there is a list of the clients as this is used to generate one of the reports.

Could i use this to look up the tabs that are in the work book and then generate the code? It is mainly the CSV creation code (which is the example posted above) which is the one i can't work out as the report does literally copy the list of clients and puts it into a brand new sheet.

Thanks again for your help

 

[Norie]

I was thinking more along the lines of looping through the client list and apply the code to each client sheet.