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
SheetName = ActiveSheet.Name
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Name = SheetName
End With
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xls"
.Close SaveChanges:=True
End With
.CutCopyMode = False
End With
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)
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)
Set wb1 = Nothing
'You can 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.