Hi.
For the past hour I have been trouble shooting my VBA code to resolve the following error when trying to merge excel files with the code, however this error pops up every time on the 2nd phase of the code where it copies all sheets' data onto a single sheet named "Combined", can anyone assist to troubleshoot my code?
Code
- Sub Combine()
- Dim oWs As Worksheet, TargetWS
- Dim rRng As Range
- Dim iX As Integer
- Dim ws As Worksheet
- Dim c As Range
- Sheets(1).Select
- Set TargetWS = Worksheets.Add
- TargetWS.Name = "Combined"
- For Each oWs In ThisWorkbook.Worksheets
- Select Case oWs.Name
- Case "Script", "Combined"
- 'Do nothing
- Case Else
- iX = iX + 1
- ''/// copy headers first time
- If iX = 1 Then
- oWs.Range("A1").CurrentRegion.Copy TargetWS.Range("A1")
- Else
- Set rRng = oWs.Range("A1").CurrentRegion
- Set rRng = rRng.Offset(1, 0).Resize(rRng.Rows.Count - 1, _
- rRng.Columns.Count)
- With TargetWS
- rRng.Copy .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
- End With
- End If
- End Select
- Next oWs
- Set ws = ActiveSheet
- With ActiveSheet
- Worksheets(1).Select
- Dim last_row As Long
- last_row = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
- MsgBox (last_row), Title:="Number of transactions"
- End With
- With ActiveSheet
- Worksheets(1).Select
- Dim lastrow As Long
- lastrow = .Cells(.Rows.Count, "L").End(xlUp).Row
- Dim cl As Range
- For Each cl In Range("L2:L" & lastrow)
- cl = "'000" & cl
- Next cl
- Dim myRange As Range
- Set myRange = Range("L2:L" & lastrow)
- myRange.Replace What:="-", Replacement:="", LookAt:=xlPart, _
- SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
- ReplaceFormat:=False
- End With
- With ActiveSheet
- Worksheets(1).Select
- Dim sht As Worksheet
- ThisWorkbook.Worksheets("Combined").Cells.EntireColumn.AutoFit
- End With
- End Sub
I have attached test files just in case it is needed. Thanks!