Access vba to move sheets

Asatoma Sadgamaya
Asatoma Sadgamaya used Ask the Experts™
on
Hi,
I am looking for an access vba script to move a tab on a workbook before another by its names. Please see below code

Public Sub Volume_SelectA1()
Dim ObjExcel
FilDatCretd = FileDateTime("N:\abcd\abc.txt")
   
   
    Set ObjExcel = CreateObject("Excel.Application")
    ObjExcel.Visible = False
    ObjExcel.Workbooks.Open "C:\efg\efg.xlsx"
   
   
    Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets("DateTimeStamp")
    ObjSheet.Activate
    'Set ObjSheet = ObjExcel.ActiveWorkbook.Worksheets(1)
    'ObjSheet.Activate

        ObjSheet.Range("A1", "A2").HorizontalAlignment = xlCenter
        ObjSheet.Range("A1", "A2").VerticalAlignment = xlCenter
        ObjExcel.ActiveWindow.DisplayGridlines = False
        ActiveSheet.Move Before:=Sheets("Sheet6") <--------------Error message
         
    With ObjSheet
        .Rows("1:1").Font.Size = 12.75
        .Range("1:1").Font.Bold = True
        .Range("A1").Font.Color = RGB(255, 255, 255)
        .Range("A1").Interior.Color = RGB(0, 81, 142)
        .Range("A1") = "Last Time Data Updated"
        .Range("A2") = Format(FilDatCretd, "dd/mmm/yyyy hh:mm")
        .Range("A1").EntireColumn.AutoFit
        .Range("A1").Select
        End With
       
        ObjSheet.UsedRange.BorderAround LineStyle:=xlDouble, Weight:=xlThick, ThemeColor:=4
                       
    ObjExcel.ActiveWorkbook.Save
    ObjExcel.ActiveWorkbook.Close
    ObjExcel.Quit
   
    Set ObjExcel = Nothing
    Set ObjSheet = Nothing
    Set FilDatCretd = Nothing
End Sub

Thank you
A
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
The basic concept is
Sheets("Sheet2").Move Before:=Sheets("Sheet1")

Open in new window



Your best bet is to record a Macro and then review the generated code.

Author

Commented:
Sorry D that is not working for me
Sam JacobsDirector of Technology Development, IPM

Commented:
If you have renamed the worksheets, make sure that you use the new name(s) in the move.
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
At the beginning use
ObjExcel.Sheets("DateTimeStamp").Move Before:=ObjExcel.Sheets("Sheet6")

Open in new window

Author

Commented:
Thanks D. Your code worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial