Glad to hear it works for you and thanks for the feedback. Please comply with Forum Rule No. 9.
9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.
The code copies and paste it directly to the workbooks.
Is there a way I can move the active sheet tab to worksheets and keep the formatting and printing options (the same everything) instead of copying and pasting?
I saw the code below, but I don’t know how to incorporate it into the code. Many thanks!!
ActiveSheet.Move Before:=Workbooks("Test.xls").Sheets(1)
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Maybe:
Sub allie14() Dim CurFile As String, DirLoc As String Dim ws As Worksheet Dim ws2 As Worksheet DirLoc = "D:\ENTER\YOUR\PATH\" 'location of files CurFile = Dir(DirLoc & "*.xls") Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False Set ws = ActiveSheet Do While CurFile <> vbNullString Dim OrigWb As Workbook Set OrigWb = Workbooks.Open(FileName:=DirLoc & CurFile, ReadOnly:=False) Set ws2 = Sheets.Add ws.UsedRange.Copy ws2.Range("A1") ws2.Activate ActiveSheet.Move Before:=Workbooks("Test.xls").Sheets(1) OrigWb.Close True Set ws2 = Nothing Set OrigWb = Nothing CurFile = Dir Loop Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True End Sub
I don't mean to step on anyone's toes, but I don't think you want to reference the workbook Test.xls
Maybe try something like this. It copies the worksheet to the opened workbook's tab position 1.
Sub allie14() Dim CurFile As String, DirLoc As String Dim ws As Worksheet, counter As Long DirLoc = "D:\ENTER\YOUR\PATH\" 'location of files CurFile = Dir(DirLoc & "*.xls") Application.ScreenUpdating = False Application.EnableEvents = False Application.DisplayAlerts = False Set ws = ActiveSheet Do While CurFile <> vbNullString With Workbooks.Open(Filename:=DirLoc & CurFile, ReadOnly:=False) ws.Copy Before:=.Sheets(1) .Close True End With counter = counter + 1 CurFile = Dir Loop Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True MsgBox counter & " files updated. ", vbInformation, "Worksheet Copy Complete" End Sub
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Good catch AlfaFrog. Looks like my toes needed some stepping on.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks