VBA Macro help please!

Eddier32

New Member
Joined
Aug 3, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone,

I am new to the Macro world and need some help from the professionals.

I need to create a macro that creates:
  • from the folder list of excel files, I need them to be save in the same workbook as the combined worksheet but in different tabs
  • Finally from the saved files in the new tabs I would only need the information that has only been filled from column (AH) and the corresponding rows (A - AK) information and nothing else from that sheet
  • For the end results I should have, 1 combined worksheet with all the files including only column (AH) with corresponding rows (A-Ak) information as well.
FYI each excel file has large data.

Below is a code I was working with but I am most likely doing it wrong.

Sub MergeWorkbooks()

Dim FolderPath As String
Dim File As String

FolderPath = "H:\Benefits_Quality_Assurance\2021\Testing Team\Testing Projects\Reference Documents\Training Materials\Training Assignments_Eduardo Rodriguez\Automation Testing\Results\Professional\Professional Consolidate worksheets macro\"

File = Dir(FolderPath)

Do While File <> ""

Workbooks.Open FolderPath & File
ActiveWorkbook.Worksheets(1).Copy _
after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Workbooks(File).Close

File = Dir()

Loop

Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

Please feel free to improve on it or create a whole new code. I would definitely love to learn how you came about this.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Update on the code Below.

Sub MergeWorkbooks()

Dim FolderPath As String
Dim File As String
Dim wbkNew As Workbook
Dim wbkOpen As Workbook

FolderPath = "H:\Benefits_Quality_Assurance\2021\Testing Team\Testing Projects\Reference Documents\Training Materials\Training Assignments_Eduardo Rodriguez\Automation Testing\Results\Professional\Professional Consolidate worksheets macro\"
File = Dir(FolderPath & "*.xlsx")

Do While File <> ""

Set wbkOpen = Workbooks.Open(FolderPath & File)
If wbkNew Is Nothing Then
wbkOpen.Worksheets(1).Copy
Set wbkNew = ActiveWorkbook
Else
wbkOpen.Worksheets(1).Copy after:=wbkNew.Worksheets(wbkNew.Worksheets.Count)
End If
wbkNew.ActiveSheet.Name = Replace(File, ".xlsx", "")
wbkOpen.Close
File = Dir()
Loop

Dim J As Integer
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
Sheets(2).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next

MsgBox "All workbooks have been added! Please rename all tables."

End Sub

Still needing those certain number of columns and rows. need a code for that and should be good
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top