Ending Macros

**lj123** · 11-12-2008, 05:29 AM

I have a macro that continues to tell me that an End With and/or End Sub is expected. I continue to add these statements in various ways, but the macro will not complete properly.

Here is the code:

Sub TestDatabase()
'
' TestDatabase Macro
' Macro recorded 11/12/2008 by JLZ
'
' Keyboard Shortcut: Ctrl+t
'
With Windows("file 1.xls")
    Range("E2").Copy
    Windows("file 2.xls").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
With Windows("file 1.xls")
    Range("E3").Copy
    Windows("file 2.xls").Range("C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Windows("file 2.xls").Activate
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
Windows("file 1.xls").Activate
    Range("C9").Select

End With

End Sub

What is the proper order / format for these statements?

**Shijesh Kumar** · 11-12-2008, 05:33 AM

Hi

there are two With Statement , so you require two End With....

So you require one more End With... that should be placed before second with

With Windows("file 1.xls")
    Range("E2").Copy
    Windows("file 2.xls").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

end with


With Windows("file 1.xls")
    Range("E3").Copy
    Windows("file 2.xls").Range("C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Windows("file 2.xls").Activate
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
Windows("file 1.xls").Activate
    Range("C9").Select

End With

End Sub

**lj123** · 11-12-2008, 05:54 AM

Thank you for that information but i am still having trouble. The problem may be that several people have offered advice on improving this macro but i have only included parts of the macro each time. Now I think i need advice on the macro as a whole. The whole macro is provided below. What I am trying to accomplish it copy many parts of a purchase order to a master customer database. the purchase order is File 1 and the Customer database is File 2.

I feel like there is a lot of extra steps in this macro preventing it from working. This is a long macro, but there are a lot of individual cells in File 1 that need to be copied to individual cells in File 2. Any help would be appreciated.

Sub TestDatabase()
'
' TestDatabase Macro
' Macro recorded 11/12/2008 by JLZ
'
' Keyboard Shortcut: Ctrl+t
'
With Windows("File 1.xls")
    Range("E2").Copy
    Windows("File 2.xls").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E3").Copy
    Windows("File 2.xls").Range("C3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E4").Copy
    Windows("File 2.xls").Range("D3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("C6").Copy
    Windows("File 2.xls").Range("E3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("C7").Copy
    Windows("File 2.xls").Range("F3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("C8").Copy
    Windows("File 2.xls").Range("G3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E8").Copy
    Windows("File 2.xls").Range("H3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E9").Copy
    Windows("File 2.xls").Range("I3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E28").Copy
    Windows("File 2.xls").Range("J3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E29").Copy
    Windows("File 2.xls").Range("K3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E30").Copy
    Windows("File 2.xls").Range("L3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E31").Copy
    Windows("File 2.xls").Range("M3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("E33").Copy
    Windows("File 2.xls").Range("N3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("B29").Copy
    Windows("File 2.xls").Range("O3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("B31").Copy
    Windows("File 2.xls").Range("P3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("B33").Copy
    Windows("File 2.xls").Range("Q3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A12:E12").Copy
    Windows("File 2.xls").Range("R3:V3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A13:E13").Copy
    Windows("File 2.xls").Range("W3:AA3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A14:E14").Copy
    Windows("File 2.xls").Range("AB3:AF").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A15:E15").Copy
    Windows("File 2.xls").Range("AG3:AK3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A16:E16").Copy
    Windows("File 2.xls").Range("AL3:AP3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A17:E17").Copy
    Windows("File 2.xls").Range("AQ3:AU3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A18:E18").Copy
    Windows("File 2.xls").Range("AV3:AZ3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A19:E19").Copy
    Windows("File 2.xls").Range("BA3:.BE3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A20:E20").Copy
    Windows("File 2.xls").Range("BF3:BJ3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A21:E21").Copy
    Windows("File 2.xls").Range("BK3:BO3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A22:E22").Copy
    Windows("File 2.xls").Range("BP3:BT3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A23:E23").Copy
    Windows("File 2.xls").Range("BU3:BY3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A24:E24").Copy
    Windows("File 2.xls").Range("BZ3:CD3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A25:E25").Copy
    Windows("File 2.xls").Range("CE3:CI3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A26:E26").Copy
    Windows("File 2.xls").Range("CJ3:CN3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
With Windows("File 1.xls")
    Range("A27:E27").Copy
    Windows("File 2.xls").Range("CO3:CS3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End With
Windows("File 2.xls").Activate
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
Windows("File 1.xls").Activate
    Range("C9").Select

End Sub

**JBeaucaire** · 11-12-2008, 12:20 PM

I'd recommend something a little more direct and easier. Just copy the values directly using the format

[destination].Value = [source].Value

Sub TestDatabase()
'
' TestDatabase Macro
' Macro recorded 11/12/2008 by JLZ
'
' Keyboard Shortcut: Ctrl+t
' syntax is [destination].Value = [source].Value         
    Windows("File 2.xls").Range("B3").Value = Windows("File 1.xls").Range("E2").Value
    Windows("File 2.xls").Range("C3").Value = Windows("File 1.xls").Range("E3").Value
    Windows("File 2.xls").Range("D3").Value = Windows("File 1.xls").Range("E4").Value
    Windows("File 2.xls").Range("E3").Value = Windows("File 1.xls").Range("C6").Value
    Windows("File 2.xls").Range("F3").Value = Windows("File 1.xls").Range("C7").Value
    Windows("File 2.xls").Range("G3").Value = Windows("File 1.xls").Range("C8").Value
    Windows("File 2.xls").Range("H3").Value = Windows("File 1.xls").Range("E8").Value
    Windows("File 2.xls").Range("I3").Value = Windows("File 1.xls").Range("E9").Value
    Windows("File 2.xls").Range("J3").Value = Windows("File 1.xls").Range("E28").Value
    Windows("File 2.xls").Range("K3").Value = Windows("File 1.xls").Range("E29").Value
    Windows("File 2.xls").Range("L3").Value = Windows("File 1.xls").Range("E30").Value
    Windows("File 2.xls").Range("M3").Value = Windows("File 1.xls").Range("E31").Value
    Windows("File 2.xls").Range("N3").Value = Windows("File 1.xls").Range("E33").Value
    Windows("File 2.xls").Range("O3").Value = Windows("File 1.xls").Range("B29").Value
    Windows("File 2.xls").Range("P3").Value = Windows("File 1.xls").Range("B31").Value
    Windows("File 2.xls").Range("Q3").Value = Windows("File 1.xls").Range("B33").Value
    Windows("File 2.xls").Range("R3:V3").Value = Windows("File 1.xls").Range("A12:E12").Value
    Windows("File 2.xls").Range("W3:AA3").Value = Windows("File 1.xls").Range("A13:E13").Value
    Windows("File 2.xls").Range("AB3:AF3").Value = Windows("File 1.xls").Range("A14:E14").Value
    Windows("File 2.xls").Range("AG3:AK3").Value = Windows("File 1.xls").Range("A15:E15").Value
    Windows("File 2.xls").Range("AL3:AP3").Value = Windows("File 1.xls").Range("A16:E16").Value
    Windows("File 2.xls").Range("AQ3:AU3").Value = Windows("File 1.xls").Range("A17:E17").Value
    Windows("File 2.xls").Range("AV3:AZ3").Value = Windows("File 1.xls").Range("A18:E18").Value
    Windows("File 2.xls").Range("BA3:BE3").Value = Windows("File 1.xls").Range("A19:E19").Value
    Windows("File 2.xls").Range("BF3:BJ3").Value = Windows("File 1.xls").Range("A20:E20").Value
    Windows("File 2.xls").Range("BK3:BO3").Value = Windows("File 1.xls").Range("A21:E21").Value
    Windows("File 2.xls").Range("BP3:BT3").Value = Windows("File 1.xls").Range("A22:E22").Value
    Windows("File 2.xls").Range("BU3:BY3").Value = Windows("File 1.xls").Range("A23:E23").Value
    Windows("File 2.xls").Range("BZ3:CD3").Value = Windows("File 1.xls").Range("A24:E24").Value
    Windows("File 2.xls").Range("CE3:CI3").Value = Windows("File 1.xls").Range("A25:E25").Value
    Windows("File 2.xls").Range("CJ3:CN3").Value = Windows("File 1.xls").Range("A26:E26").Value
    Windows("File 2.xls").Range("CO3:CS3").Value = Windows("File 1.xls").Range("A27:E27").Value
    Windows("File 2.xls").Range("B3").Value = Windows("File 1.xls").Range("E2").Value

    Windows("File 2.xls").Activate
    Rows("3:3").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Windows("File 1.xls").Activate
    Range("C9").Select

End Sub

**JBeaucaire** · 11-12-2008, 12:22 PM

By the way, as I changed the format of your macro I spotted TWO typos in your macro that would cause problems.

Windows("File 2.xls").Range("BA3:.BE3").PasteSpecial

Notice the extra period. And...

Windows("File 2.xls").Range("AB3:AF").PasteSpecial

Notice the missing "3".

**lj123** · 11-12-2008, 01:10 PM

Thank you J that does seem more straight forward and thank you for catching the errors. However when I implemented the code you suggested I receive a Run Time Error '438' Object doesn't support this property or method.

When debugging it highlights the first row that identifies the destination and source. Any thoughts?

Ending Macros

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Ending Macros

Ending Macros

Ending Macros

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions