Asatoma Sadgamaya
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.Applic ation")
ObjExcel.Visible = False
ObjExcel.Workbooks.Open "C:\efg\efg.xlsx"
Set ObjSheet = ObjExcel.ActiveWorkbook.Wo rksheets(" DateTimeSt amp")
ObjSheet.Activate
'Set ObjSheet = ObjExcel.ActiveWorkbook.Wo rksheets(1 )
'ObjSheet.Activate
ObjSheet.Range("A1", "A2").HorizontalAlignment = xlCenter
ObjSheet.Range("A1", "A2").VerticalAlignment = xlCenter
ObjExcel.ActiveWindow.Disp layGridlin es = 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.Colo r = 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.BorderA round LineStyle:=xlDouble, Weight:=xlThick, ThemeColor:=4
ObjExcel.ActiveWorkbook.Sa ve
ObjExcel.ActiveWorkbook.Cl ose
ObjExcel.Quit
Set ObjExcel = Nothing
Set ObjSheet = Nothing
Set FilDatCretd = Nothing
End Sub
Thank you
A
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.
Set ObjExcel = CreateObject("Excel.Applic
ObjExcel.Visible = False
ObjExcel.Workbooks.Open "C:\efg\efg.xlsx"
Set ObjSheet = ObjExcel.ActiveWorkbook.Wo
ObjSheet.Activate
'Set ObjSheet = ObjExcel.ActiveWorkbook.Wo
'ObjSheet.Activate
ObjSheet.Range("A1", "A2").HorizontalAlignment = xlCenter
ObjSheet.Range("A1", "A2").VerticalAlignment = xlCenter
ObjExcel.ActiveWindow.Disp
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.Colo
.Range("A1") = "Last Time Data Updated"
.Range("A2") = Format(FilDatCretd, "dd/mmm/yyyy hh:mm")
.Range("A1").EntireColumn.
.Range("A1").Select
End With
ObjSheet.UsedRange.BorderA
ObjExcel.ActiveWorkbook.Sa
ObjExcel.ActiveWorkbook.Cl
ObjExcel.Quit
Set ObjExcel = Nothing
Set ObjSheet = Nothing
Set FilDatCretd = Nothing
End Sub
Thank you
A
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks D. Your code worked.
Open in new window
Your best bet is to record a Macro and then review the generated code.