Skip to main content
Code Review

Return to Question

deleted 6 characters in body; edited tags; edited title
Source Link
Jamal
Jamal
  • 34.9k
  • 13
  • 133
  • 237

speed up merging Merging worksheets and using find/replace

The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicodeUnicode/character issues. I cant help but think that could be handled better.

I'd like to find out where the bottlenecks in this code are, how to handle these unicodeUnicode issues, perform the Find/replace better, and all in all how to execute better vbaVBA practices.

speed up merging worksheets and using find/replace

The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues. I cant help but think that could be handled better.

I'd like to find out where the bottlenecks in this code are, how to handle these unicode issues, perform the Find/replace better, and all in all how to execute better vba practices.

Merging worksheets and using find/replace

The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of Unicode/character issues. I cant help but think that could be handled better.

I'd like to find out where the bottlenecks in this code are, how to handle these Unicode issues, perform the Find/replace better, and all in all how to execute better VBA practices.

added 12 characters in body
Source Link
RubberDuck
RubberDuck
  • 30.9k
  • 6
  • 71
  • 174

Of all the macros that iI put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'dI'd like to know why code 16x as long is running much more instantly.

The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues - which i. I cant help but think that could be handled better.

I'd like to find out where the bottlenecks in this code are, how to handle these unicode issues, perform the Find/replace better, and all in all how to execute better vba practices.

I'd like to find out were the bottlenecks in this code are, how to handle these unicode issues or even Find/replace better, and in all how to execute better vba practices.

Of all the macros that i put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'd like to know why code 16x as long is running much more instantly.

The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues - which i cant help but think could be handled better.

I'd like to find out were the bottlenecks in this code are, how to handle these unicode issues or even Find/replace better, and in all how to execute better vba practices.

Of all the macros that I put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but I'd like to know why code 16x as long is running much more instantly.

The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues. I cant help but think that could be handled better.

I'd like to find out where the bottlenecks in this code are, how to handle these unicode issues, perform the Find/replace better, and all in all how to execute better vba practices.

Source Link
mango
mango
  • 223
  • 2
  • 7

speed up merging worksheets and using find/replace

Of all the macros that i put into heavy rotation these days, this one is running the slowest. ~4-5 seconds depending on the size of the files. It's not a lot but i'd like to know why code 16x as long is running much more instantly.

The code tries to merge documents (usually 2 excel docs out of at most 5) depending on their names and then rename those to exactly what I need. Then, another big issue, is using find/replace to fix a bunch of unicode/character issues - which i cant help but think could be handled better.

Option Explicit
Sub MergeBooks()
Dim wb As Workbook
Dim ws As Worksheet

On Error GoTo Handler:
Application.ScreenUpdating = False
For Each wb In Application.Workbooks
    If wb.Name <> "CompanyBook.xlsm" Then
        If FindString(wb.Name, "Report2") Then
            wb.Worksheets.Move after:=Workbooks("CompanyBook.xlsm").Sheets("Aggregate")
        ElseIf FindString(wb.Name, "Report1") Then
            wb.Worksheets.Move after:=Workbooks("CompanyBook.xlsm").Sheets("Aggregate")
        End If
    End If
Next

For Each ws In Workbooks("CompanyBook.xlsm").Worksheets
    If FindString(ws.Name, "Report2") Then
        ws.Name = "Report2"
    ElseIf FindString(ws.Name, "Report1") Then
        ws.Name = "Report1"
    End If
Next ws

'Char mishap replacements
With Workbooks("CompanyBook.xlsm")
.Worksheets("Report1").Cells.Replace What:="&amp;", Replacement:="&", LookAt:=xlPart, MatchCase:=False
.Worksheets("Report1").Cells.Replace What:="&quot;", Replacement:=Chr(34), LookAt:=xlPart, MatchCase:=False
.Worksheets("Report2").Cells.Replace What:="’", Replacement:="’", LookAt:=xlPart, MatchCase:=False
.Worksheets("Report2").Cells.Replace What:="…", Replacement:="…", LookAt:=xlPart, MatchCase:=False
.Worksheets("Report2").Cells.Replace What:="£", Replacement:="£", LookAt:=xlPart, MatchCase:=False
'.Worksheets("Report2").Cells.Replace What:="‘", Replacement:="‘L", LookAt:=xlPart, MatchCase:=False
.Worksheets("Company").Select
End With

Application.ScreenUpdating = True
Exit Sub
Handler:
Application.ScreenUpdating = True
MsgBox "Please make sure that one and only one type of each database file is open.", vbExclamation, "Merge Documents"
End Sub
Function FindString(strCheck As String, strFind As String) As Boolean
Dim intPos As Integer
    intPos = InStr(strCheck, strFind)
    FindString = intPos > 0
End Function

I'd like to find out were the bottlenecks in this code are, how to handle these unicode issues or even Find/replace better, and in all how to execute better vba practices.