Can someone correct my code???

[WillR]

Sub OpenXLGLDay1()
'**********************************************************************
'* OpenXLGL Macro for Day1 of Week  by Will Riley                     *
'* Opens the GL Statement file contained in range File1 of Menu Sheet *
'* Copies the GL and then pastes it to the correct column for the     *
'* date of the GL in the sheet GL import. Once it's done that it      *
'* closes the XLGL file without saving it.                            *
'**********************************************************************
    Application.ScreenUpdating = False
    Workbooks.OpenText FileName:= _
    Range("File1").Text, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=True, OtherChar:="~", FieldInfo:=Array(Array(1, 1 _
    ), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
  ' the above code opens the gl file in range "File 1" and parses
  ' text to columns in a new worksheet using ~ character as spacer
    Columns("A:D").Select
    Columns("A:D").EntireColumn.AutoFit
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  ' file gets formatted and sorted by column A (GL No) ascending
    Range("A1").Select
    ActiveCell.Name = "Bastardos"
  'names a range in the GL file for referencing later in the code
    Range("C1:C" & Range("C1").End(xlDown).Row).Name = "GLtoCopy"
    Range("GLtoCopy").Copy
  'names and copies the whole of column C to clipboard (GL Balances)
    Workbooks("Weekly Retail 2003 (WIP).xls").Sheets("GL Import").Activate
  ' activates the weekly retial workbook
  ' then we run a test to see where to paste the GL values
    col = Range("Day1ofst").Value ' this is the value of the column offset
        If Range("WhatHalf").Value = "1st Half" Then 'if it's 1st half of year
            Range("GLrange1").Offset(1, col).Activate 'offset 1 row & col Columns
  ' then paste sprecial the GL values
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            Range("Bastardos").Select 'this was what we named "A1"
            ActiveWorkbook.Close savechanges:=False
            Application.Goto Sheets("Menu").Range("A1")
  ' last bit gets us back to menu in Wkly Statement
        ElseIf Range("WhatHalf").Value = "2nd Half" Then
            Range("GLrange2").Offset(1, col).Activate 'if 2nd half goto GLrange2
  ' then paste special the GL values here
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
            Range("Bastardos").Select 'this was what we named "A1"
            ActiveWorkbook.Close savechanges:=False
            Application.Goto Sheets("Menu").Range("A1")
  ' last bit gets us back to menu in Wkly Statement
        Else: MsgBox "Macro Failed due to strange value in Menu cell D1"
  ' i.e. if summink badly 'pear', give error msg & stop
        End If
  ' so now we've copied & pasted the GL we just need to close
  ' the XLGL file we opened without saving it
    Application.Goto Sheets("Menu").Range("A1")
  ' last bit gets us back to menu in Wkly Statement
    Application.ScreenUpdating = True
    End Sub

Currently, all goes OK until I try to paste special.... anyone got any ideas what i've done wrong (I'm not sure i've used the correct commands...therefore there's nowt left to paste when it gets to the paste bit....)

Any constructive criticism of my code (i'm only learning!) appreciated too

 

[Mudface]

Not sure what's going wrong, Phil, as I can't easily check, but I've edited your code a bit to remove the selecting and activating parts. I've also truncated some bits to remove parameters set as default and removed your remarks to make the code structure a bit clearer. Give the following a whirl, although I can't vouch for success :wink:-
<pre>
Sub OpenXLGLDay1()

Application.ScreenUpdating = False
Workbooks.OpenText Filename:=Range("File1").Text, Origin:=xlWindows, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="~", _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))

With Columns("A:D")
.EntireColumn.AutoFit
.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With

Range("A1").Name = "Bastardos"
Range("C1:C" & Range("C1").End(xlDown).Row).Name = "GLtoCopy"
Range("GLtoCopy").Copy

With Workbooks("Weekly Retail 2003 (WIP).xls").Sheets("GL Import")
col = .Range("Day1ofst").Value
If .Range("WhatHalf").Value = "1st Half" Then
.Range("GLrange1").Offset(1, col).PasteSpecial Paste:=xlValues

ElseIf .Range("WhatHalf").Value = "2nd Half" Then
.Range("GLrange2").Offset(1, col).PasteSpecial Paste:=xlValues

Else: MsgBox "Macro Failed due to strange value in Menu cell D1"

End If
End With

ActiveWorkbook.Close savechanges:=False
Application.Goto Sheets("Menu").Range("A1")
Application.ScreenUpdating = True

End Sub
</pre>

 

[WillR]

Mudders,

I think we're getting somewhere, thanks....

However, the code still errors at ....

With Workbooks("Weekly Retail 2003 (WIP).xls").Sheets("GL Import")
        Col = Range("Day1ofst").Value
        If .Range("WhatHalf").Value = "1st Half" Then
            .Range("GLrange1").Offset(1, Col).PasteSpecial Paste:=xlValues

....as soon as it gets tocode starting 'Col'
Now, the ranges "Day1ofst", "What Half" are in sheet "Menu" whereas the range "GLrange1" is in sheet "GL Import"... is this going to be the problem?

 

[Mudface]

Yes, I would think so. Try qualifying all references to ranges with the workbook and worksheet names (eg Workbooks("Book1").Worksheets("Sheet1").Range("A1")). If that works OK, re-post with your working code and we can probably 'smarten' it up a bit with some Set and With statements.

 

[WillR]

On 2003-01-30 07:37, Mudface wrote:
Yes, I would think so. Try qualifying all references to ranges with the workbook and worksheet names (eg Workbooks("Book1").Worksheets("Sheet1").Range("A1")). If that works OK, re-post with your working code and we can probably 'smarten' it up a bit with some Set and With statements.

Mate, thanks for the help so far...

It now works lovely with the following amendments i made to reference workbooks/sheets

Sub OpenXLGLDay1()
'**********************************************************************
'* OpenXLGL Macro for Day1 of Week  by Will Riley                     *
'* Opens the GL Statement file contained in range File1 of Menu Sheet *
'* Copies the GL and then pastes it to the correct column for the     *
'* date of the GL in the sheet GL import. Once it's done that it      *
'* closes the XLGL file without saving it.                            *
'**********************************************************************
    Dim wkb1 As Workbook
 
    Application.ScreenUpdating = False
    Workbooks.OpenText FileName:=Range("File1").Text, Origin:=xlWindows, StartRow:=1, _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="~", _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1))
    Set wkb1 = ActiveWorkbook
    
    With Columns("A:D")
        .EntireColumn.AutoFit
        .Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    End With
    
    Range("A1").Name = "Bastardos"
    Range("C1:C" & Range("C1").End(xlDown).Row).Name = "GLtoCopy"
    Range("GLtoCopy").Copy
    
    Workbooks("Weekly Retail 2003 (WIP).xls").Activate
    
    With Workbooks("Weekly Retail 2003 (WIP).xls")
            cl = Range("Day1ofst").Value
        If Range("WhatHalf").Value = "1st Half" Then
            Range("GLrange1").Offset(1, cl).PasteSpecial Paste:=xlValues
    
        ElseIf Range("WhatHalf").Value = "2nd Half" Then
            Range("GLrange2").Offset(1, cl).PasteSpecial Paste:=xlValues
            
        Else: MsgBox "Macro Failed due to strange value in Menu cell D1"
        
        End If
    End With
    
    wkb1.Close (False)
    Application.Goto Sheets("Menu").Range("A1")
    Application.ScreenUpdating = True

End Sub

Now,
How do I auto-empty the clipboard? so the user doesn't have to click on the msgbox that pops up...??

 

[Mudface]

Good show .

Application.CutCopyMode =False will clear your clipboard.

 

[WillR]

On 2003-01-30 08:12, Mudface wrote:
Good show .

Application.CutCopyMode =False will clear your clipboard.

Lovely....

All is well - cheers for your help