VBA: Is it possible that 3 macros workbooks into one workbook macro?

[vbanewbie68]

Hello

I have 3 macros workbooks ( 3 macro files).

The question is, is it possible to add 3 macros (workbooks) files into one macro workbook? Ideally I would like each tab to have its own macro to run reports.

I have 3 different types of data files downloaded from a platform in CSV format. I import data into a macro, then into a report, and add extra columns.

Or do you have a better idea on how to do it?

Thank you

Best regards

V

 

[Joe4]

I don't think there isn't a limit to how many different VBA procedures you can have in a workbook.
I have had workbooks with over a dozen sheets/tabs and VBA procedures before.

 

[vbanewbie68]

Hello Joe4
Thank you for answering my questions. I appreciate your time on this matter.
In a new workbook using macro format I will add one macro and then the rest of it will go under it to support each tab that has its own macro to run reports?
Regards
R

 

[Joe4]

Hello Joe4
Thank you for answering my questions. I appreciate your time on this matter.
In a new workbook using macro format I will add one macro and then the rest of it will go under it to support each tab that has its own macro to run reports?
Regards
R

How you structure it depends on a few things.

1. If you have event procedure VBA code (i.e. code that is automatically triggered upon some event happening), those procedures need to go in the appropriate Sheet or ThisWorkbook modules.

2. For all other VBA code, it can go pretty much wherever you want. What I usually like to do is create separate VBA modules, and group them in some fashion that make sense.

 

[vbanewbie68]

Please find attached for you to take a look.

So for each macro files such as Regular, Thank you and Upgrade I simply copy coding and paste it into the main macro's new module to support those tabs at see the attached Main Macro?

 

[Joe4]

Yes, that is probably how I would do it.

 

[vbanewbie68]

For example for Regular codes I copied it and pasted it into a Main macro. I can't figure out how this code to support Regular tab the main macro spreadsheet?

Please see my codes as below.

Public FilePath As String, Importa As Worksheet
Sub Main()

'Import Sheet
ChDrive ("J:")

'ChDir ("J:\PS\FSD_Rest\SOPS_Data")
ChDir ("J:\PS\FSD_Rest\SOPS_Data\Database Team\Macros\Regular.xlsm")

' Set File Name to selected File
FilePath = Application.GetOpenFilename(, , "Select Report")

Set Importa = ThisWorkbook.Sheets.Add

If FilePath <> "False" Then 'if filepath selected...
Call copyWorkbookasText(Importa.Cells(1, 1), FilePath)
Else
Application.ScreenUpdating = True
MsgBox "No file selected!"
End
End If
Application.ScreenUpdating = True

'Modify Sheet

Call Add_Columns
Call Add_Rows_Data

'Export Sheet to new workbook (let the user save for themselves later)

'This one is for to move onto a new workbook
'Importa.Move



End Sub


Sub copyWorkbookasText(targetRange As Range, FilePath As String)
'Copy over contents of report
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open FilePath, ReadOnly:=True
If ActiveWorkbook.FileFormat = 51 Then '.xlsx files
ActiveWorkbook.Sheets(1).Cells.Copy targetRange
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub
Else
Colcount = ActiveWorkbook.Sheets(1).Columns.Count - 1
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End If
'Columns to ensure we import file as text
ReDim TextArray(0 To Colcount) As Variant
For i = 0 To Colcount
If i = 0 Then
TextArray(i) = 4
Else
TextArray(i) = 2 '2 is the value assigned to 'Text' '
End If
Next i

'Import the text file into excel as text
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & FilePath, Destination:=targetRange)
.TextFilePlatform = 850
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = TextArray '!!!!Note, use of TextArray from code above!!!
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[Joe4]

I can't figure out how this code to support Regular tab the main macro spreadsheet?

By "support" do you mean run on/against the "Regular" tab?

Just activate that sheet in your code before running the code against it, i.e.

Sheets("Regular").Activate

Alternatively, you could use worksheet objects, i.e.

Dim wsReg as Worksheet
Set wsReg = Sheets("Regular")

Then you can simply activate that sheet with a command like:

wsReg.Activate

and if you wanted to refer to a range on that sheet, you could do so like:

wsReg.Range(...)

 

[vbanewbie68]

By "support" do you mean run on/against the "Regular" tab?

Just activate that sheet in your code before running the code against it, i.e.

Sheets("Regular").Activate

Alternatively, you could use worksheet objects, i.e.

Dim wsReg as Worksheet
Set wsReg = Sheets("Regular")

Then you can simply activate that sheet with a command like:

wsReg.Activate

and if you wanted to refer to a range on that sheet, you could do so like:

wsReg.Range(...)

Where about in the code section to add this?

Dim wsReg as Worksheet
Set wsReg = Sheets("Regular")

wsReg.Activate

 

[Joe4]

Where about in the code section to add this?

Dim wsReg as Worksheet
Set wsReg = Sheets("Regular")

wsReg.Activate

Wherever you want the code to run against that sheet.
I have not tried to analyze your code (it would be very difficult to do without all the data) to know where you want things to run.

However, note that in your code, you have sheet references like this:

ActiveWorkbook.Sheets(1).Cells.Copy targetRange

That is referencing the first sheet in the active workbook, regardless of name.

If you wanted it to reference the "Regular" sheet, then you would need to change that, to something like:

ActiveWorkbook.Sheets("Regular").Cells.Copy targetRange