WillR
Well-known Member
- Joined
- Feb 18, 2002
- Messages
- 1,143
Code:
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