VBA to copy sheets in a location

I have 2 workbooks in a location C:\new\folder. I want sheet 1 from wb1, sheet3 from wb2 and place it one after the other in another existing workbook wb3 in the same location when i click a button in wb3. can you help me with a code? 

Top Contributor Asked on July 2, 2016 in VBA: General.
Add Comment
2 Answer(s)

 New folder (if folder doesn’t exist) is created at the same place with workbook.

 

 

 

The workbook’s sheets as new workbooks are copied in this folder.

 

 

 

RE: VBA to copy sheets in a location

 

 

 

 

 

Sub createfolder()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
Application.ScreenUpdating = False
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
On Error Resume Next
MkDir MyFilePath
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
 
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xls"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheets(1).Activate
Application.ScreenUpdating = True
MsgBox "The Process Was Carried Out Successfully", vbInformation
 
End Sub

 

 

 

Working Template Can Be Downloaded Here

 

 

 

 

Expert Answered on July 4, 2016.
Add Comment

Hi,  Here is the VBA code to copy worksheets from Book1.xlsx and Book2.xlsx into target Workbook. Please create a module in your target workbook and create button to assign the below code:

Sub CopySheetsFromOtherWorkbooksInTheSameFolder()
Dim wb1 As Workbook
'Get1 sheet1 from Book1.xlsx
Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\Book1.xlsx")
wb1.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
wb1.Close
Set wb1 = Nothing
'Get1 sheet1 from Book2.xlsx
Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\Book2.xlsx")
wb1.Sheets("Sheet3").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
wb1.Close
Set wb1 = Nothing
'You can save
ThisWorkbook.Save
End Sub

 

Hope this helps-PNRao!

Keymaster Answered on July 2, 2016.
Add Comment
  • Found this useful?

    Please share using the share button above.

    If you found the answer is best answer for your question, Please mark as 'best answer' by clicking on the right tick mark icon at the left side of the answer.

    Found the answer useful and wants to credit the user, then vote the answer (vote up).

  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.