Error handling when copying rows.

A

atame

New Member
Joined
May 26, 2015
Messages
31
Hi All,

I'm having some real trouble getting the error handling to work properly on some code i'm using.

Code: 
Private Sub CommandButton7_Click()Dim i, lastG As Long
Dim j As Integer
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .CutCopyMode = False
End With


' find last row
lastG = sheets("Global").Cells(Rows.Count, "Q").End(xlUp).row


For i = 3 To lastG
    lookupVal = sheets("Global").Cells(i, "Q") ' value to find
    ' loop over values in "details"
    For j = 0 To Me.ComboBox2.ListCount - 1
        currVal = Me.ComboBox2.List(j, 2) ' value to match
        If lookupVal = currVal Then
            sheets("Global").Cells(i, "Q").EntireRow.Copy
            sheets(Me.ComboBox2.List(j, 1)).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Insert shift:=xlDown
        End If
    Next j
Next i


 With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .CutCopyMode = True
End With
End Sub

This is the code that i'm using, basically it searches column Q in the global sheet, and matches the value to Column 2 in a combobox, once a match it found it copys the row to the respective sheet defined in column 1 in the combobox. This all works well and copys it to the correct location of the respective sheets.

The problem that i am having is the error handling, if it finds a match in the sheet and column 2 in the combobox but there is no sheet for the value then it crashes the code. What i want it to do it to check Column Q in the global sheet first and make sure that all the sheets that are needed exists. If not it will display a msgbox telling the user what sheets need to be created. If for example two sheets need to be created it will say

Code: 
Please create Sheets: 
Example
Example 2

Once these have been create, repress button 4 to continue.

and then stop the code from running after that.

The user will then create the sheets, and run the code again, if all the relevant sheets now exists then the code would run as normal. If not the the mxgbox would appear again.

The last thing that i would like it to do is once all the information has been copied to the relevant sheets, i would like it to display a msgbox telling the user how many rows of data have been copied to the respective sheets. for example.

Code: 
Number of rows copied:
2780: 44
2782: 2
2800: 13

Any help you can provide is greatly appreciated, it took me months to get the bit of code i already have to work. I am still a novice at VBA but am slowly learning.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hi Guys,

I have managed to get it to work to create the sheet if it does not exist.

Below is the updated code.

What i need now is some help with displaying how many rows have been copied to each sheet along with the total amount of rows copied.

Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#00008B]Dim[/COLOR][COLOR=#000000] i [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] j [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] lastG [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] strWS [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] rngCPY [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Range

[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] Application
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ScreenUpdating [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]EnableEvents [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]CutCopyMode [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]

[/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]GoTo[/COLOR][COLOR=#000000] bm_Close_Out

[/COLOR][COLOR=#808080]' find last row[/COLOR][COLOR=#000000]
lastG [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Global"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Rows[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Count[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]"Q"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlUp[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]row

[/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#000000] i [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]3[/COLOR][COLOR=#00008B]To[/COLOR][COLOR=#000000] lastG
    lookupVal [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Global"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]i[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]"Q"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#808080]' value to find[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#808080]' loop over values in "details"[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#000000] j [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#00008B]To[/COLOR][COLOR=#00008B]Me[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ComboBox2[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ListCount [/COLOR][COLOR=#000000]-[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]
        currVal [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#00008B]Me[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ComboBox2[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]List[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]j[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]2[/COLOR][COLOR=#000000])[/COLOR][COLOR=#808080]' value to match[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] lookupVal [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] currVal [/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#000000] rngCPY [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Global"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]i[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]"Q"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]EntireRow
            strWS [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#00008B]Me[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ComboBox2[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]List[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]j[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]GoTo[/COLOR][COLOR=#000000] bm_Need_Worksheet  [/COLOR][COLOR=#808080]'<~~ if the worksheet in the next line does not exist, go make one[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] Worksheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]strWS[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
                rngCPY[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Copy
                [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Cells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Rows[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Count[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlUp[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Offset[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Insert shift[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]xlDown
            [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#000000] j
[/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#000000] i

[/COLOR][COLOR=#00008B]GoTo[/COLOR][COLOR=#000000] bm_Close_Out

bm_Need_Worksheet[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]GoTo[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] Worksheet
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] wb [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Workbook[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#000000] wb [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] ThisWorkbook
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] wsTemplate [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Worksheet[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#000000] wsTemplate [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wb[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Template"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] wsPayment [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Worksheet[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#000000] wsPayment [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wb[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Payment Form"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] wsNew [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#000000] Worksheet
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] lastRow2 [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] Contract [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000] Contract [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Payment Form"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"C9"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] SpacePos [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Integer[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000] SpacePos [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] InStr[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Contract[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]"- "[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] Name [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000] Name [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] Left[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Contract[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] SpacePos[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] Name2 [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000] Name2 [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] Right[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Contract[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] Len[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Contract[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#000000] Len[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]Name[/COLOR][COLOR=#000000]))[/COLOR][COLOR=#000000]

[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]String[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] strWS
[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] CCName [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Variant[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000] CCName [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#00008B]Me[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ComboBox2[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]List[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]j[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]

[/COLOR][COLOR=#00008B]Dim[/COLOR][COLOR=#000000] lastRow [/COLOR][COLOR=#00008B]As[/COLOR][COLOR=#00008B]Long[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000] lastRow [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"U36:U53"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlDown[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]row

[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] InStr[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Payment Form"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A20"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]"THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]>[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
lastRow2 [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A23:A39"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlDown[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]row
[/COLOR][COLOR=#00008B]Else[/COLOR][COLOR=#000000]
lastRow2 [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A18:A34"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlDown[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]row
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]

wsTemplate[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Visible [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
wsTemplate[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Copy before[/COLOR][COLOR=#000000]:=[/COLOR][COLOR=#000000]sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Details"[/COLOR][COLOR=#000000]):[/COLOR][COLOR=#00008B]Set[/COLOR][COLOR=#000000] wsNew [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] ActiveSheet
wsTemplate[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Visible [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]False[/COLOR][COLOR=#000000]

[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] InStr[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Payment Form"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A20"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]"THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]>[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] wsPayment
    [/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#00008B]Each[/COLOR][COLOR=#000000] cell [/COLOR][COLOR=#00008B]In[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A23:A39"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] Len[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]cell[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Payment Form"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A20"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"Network"[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
                cell[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]" - "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] Name2 [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]": "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] CCName
            [/COLOR][COLOR=#00008B]Else[/COLOR][COLOR=#000000]
                cell[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]" - "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] Name2 [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]": "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] CCName
            [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]Exit[/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#000000] cell
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]Else[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] wsPayment
    [/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#00008B]Each[/COLOR][COLOR=#000000] cell [/COLOR][COLOR=#00008B]In[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A18:A34"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] Len[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]cell[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Payment Form"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A20"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"Network"[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
                cell[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]" - "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] Name2 [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]": "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] CCName
            [/COLOR][COLOR=#00008B]Else[/COLOR][COLOR=#000000]
                cell[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]" - "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] Name2 [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]": "[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] CCName
            [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#00008B]Exit[/COLOR][COLOR=#00008B]For[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#00008B]Next[/COLOR][COLOR=#000000] cell
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]

[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000] InStr[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000],[/COLOR][COLOR=#000000] sheets[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"Payment Form"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A20"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value[/COLOR][COLOR=#000000],[/COLOR][COLOR=#800000]"THE VAT SHOWN IS YOUR OUTPUT TAX DUE TO CUSTOMS AND EXCISE"[/COLOR][COLOR=#000000])[/COLOR][COLOR=#000000]>[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#00008B]Then[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] wsNew
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Name [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] NewName
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D4"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A23:A39"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlDown[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D6"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"L11"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D8"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"C9"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D10"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"C11"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]Else[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] wsNew
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Name [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] NewName
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D4"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"A18:A34"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlDown[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D6"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"L11"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D8"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"C9"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"D10"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"C11"[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]If[/COLOR][COLOR=#000000]

wsPayment[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Activate

[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] wsPayment
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"J"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow2 [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]0[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"L"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow2 [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Formula [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"=N"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow2 [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"-J"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow2 [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]""[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"N"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow2 [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Formula [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"='"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"'!L20"[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"U"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]value [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]": "[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"V"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Formula [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"='"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"'!I21"[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"W"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Formula [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"='"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"'!I23"[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]Range[/COLOR][COLOR=#000000]([/COLOR][COLOR=#800000]"X"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] lastRow [/COLOR][COLOR=#000000]+[/COLOR][COLOR=#800000]1[/COLOR][COLOR=#000000]).[/COLOR][COLOR=#000000]Formula [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]"='"[/COLOR][COLOR=#000000]&[/COLOR][COLOR=#000000] NewName [/COLOR][COLOR=#000000]&[/COLOR][COLOR=#800000]"'!K21"[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000]

[/COLOR][COLOR=#00008B]On[/COLOR][COLOR=#00008B]Error[/COLOR][COLOR=#00008B]GoTo[/COLOR][COLOR=#000000] bm_Close_Out
[/COLOR][COLOR=#00008B]Resume[/COLOR][COLOR=#000000]

bm_Close_Out[/COLOR][COLOR=#000000]:[/COLOR][COLOR=#000000]

[/COLOR][COLOR=#00008B]With[/COLOR][COLOR=#000000] Application
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]ScreenUpdating [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]EnableEvents [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]CutCopyMode [/COLOR][COLOR=#000000]=[/COLOR][COLOR=#800000]True[/COLOR][COLOR=#000000]
[/COLOR][COLOR=#00008B]End[/COLOR][COLOR=#00008B]With[/COLOR]</code>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Creating and emailing Excel sheet with only visible data
Replies
0
Views
182
mecerrato
M
S
  • Question
VBA to loop through sheets and copy to new workbook including the pivot tab
Replies
0
Views
193
simont1975
S
S
  • Solved
Error executing vba code
Replies
2
Views
82
sofas
S
G
  • Question
VBA - add formula to cell when created
Replies
2
Views
293
Golaidron
G
S
  • Question
Trying to resize a table to match the number of rows in another table
Replies
5
Views
58
sspatriots
S

Forum statistics

Threads
1,214,980
Messages
6,122,563
Members
449,088
Latest member
Motoracer88

Share this page

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