Hello!
I need help with vba code. I have to insert the same sheet to over 100 workbooks. Instead of opening all the workbooks and copying the sheet is there a way I can use VBA to insert the sheet to all the excel files?
Thank you!!!
Hello!
I need help with vba code. I have to insert the same sheet to over 100 workbooks. Instead of opening all the workbooks and copying the sheet is there a way I can use VBA to insert the sheet to all the excel files?
Thank you!!!
I don't know of a way to copy a sheet to a closed workbook.
A macro could open each workbook in a folder, copy the one sheet, and save\close the workbook.
If you want help with that macro, record a macro where you manually do all the steps (open, copy sheet, save, close) for one workbook from the folder. Then post the code for the recorded macro here. Remember to surround your pasted code with CODE tags (it's a forum rule). See my signature block below.
Someone here can then change your recorded macro to loop through all the workbooks in the folder.
Last edited by AlphaFrog; 04-01-2014 at 01:38 PM.
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.
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") OrigWb.Close True Set ws2 = Nothing Set OrigWb = Nothing CurFile = Dir Loop Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True End Sub
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.
Recorded the macro: Wanted to move "Sheet1" to "Workbook" file.
THANKS!!
Sub CopySheet()
'
' CopySheet Macro
'
'
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks("Workbook"). _
Sheets(4)
End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks