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

vbanewbie68

Board Regular
Joined
Oct 16, 2021
Messages
162
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,490
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 16, 2021
Messages
162
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,490
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 16, 2021
Messages
162
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 

Attachments

  • 3 macro files .PNG
    3 macro files .PNG
    5.3 KB · Views: 2
  • Main Macro.PNG
    Main Macro.PNG
    12.2 KB · Views: 2
  • Main VBE.PNG
    Main VBE.PNG
    16.4 KB · Views: 1

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,490
Office Version
  1. 365
Platform
  1. Windows
Yes, that is probably how I would do it.
 

vbanewbie68

Board Regular
Joined
Oct 16, 2021
Messages
162
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,490
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Sheets("Regular").Activate

Alternatively, you could use worksheet objects, i.e.
VBA Code:
Dim wsReg as Worksheet
Set wsReg = Sheets("Regular")

Then you can simply activate that sheet with a command like:
VBA Code:
wsReg.Activate

and if you wanted to refer to a range on that sheet, you could do so like:
VBA Code:
wsReg.Range(...)
 

vbanewbie68

Board Regular
Joined
Oct 16, 2021
Messages
162
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
VBA Code:
Sheets("Regular").Activate

Alternatively, you could use worksheet objects, i.e.
VBA Code:
Dim wsReg as Worksheet
Set wsReg = Sheets("Regular")

Then you can simply activate that sheet with a command like:
VBA Code:
wsReg.Activate

and if you wanted to refer to a range on that sheet, you could do so like:
VBA Code:
wsReg.Range(...)


Where about in the code section to add this?

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

wsReg.Activate
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,490
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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:
Rich (BB code):
ActiveWorkbook.Sheets("Regular").Cells.Copy targetRange
 

Forum statistics

Threads
1,186,001
Messages
5,955,230
Members
438,187
Latest member
DT1962

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top