Link to home
Start Free TrialLog in
Avatar of Asatoma Sadgamaya
Asatoma SadgamayaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access vba to move sheets

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
Avatar of Daniel Pineault
Daniel Pineault

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.
Avatar of Asatoma Sadgamaya

ASKER

Sorry D that is not working for me
If you have renamed the worksheets, make sure that you use the new name(s) in the move.
ASKER CERTIFIED SOLUTION
Avatar of Daniel Pineault
Daniel Pineault

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks D. Your code worked.