Solved

Excel merge sheets

Posted on 2014-04-22
3
306 Views
Last Modified: 2014-04-22
Hello,
Can you please help,
I used below code to merge excel sheets.

- I need to have a pop box to ask me which sheets to merge together.
- The new created sheet to be named as the 2 sheets merged (Example Sheet1 & Sheet2)
- Delete the merged sheets.

Option Explicit
Sub Merge_Sheets()
Sheets.Add After:=Sheets(Sheets.Count)
   Dim wks As Worksheet
   Set wks = Sheets(Sheets.Count)

   wks.Name = "Sheet1 & Sheet2"
   
   With Sheets("Sheet1")
    Dim lastrow As Long
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1:O" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp)
   End With

   With Sheets("Sheet2")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A2:O" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
   End With
   
Application.DisplayAlerts = False
Worksheets("Sheet1").delete
Worksheets("Sheet2").delete

End Sub

any help is appreciated.
Thank you
0
Comment
Question by:Wass_QA
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
Hi,

pls try

Option Explicit
Sub Merge_Sheets()
Dim ResultSheet1 As String
Dim ResultSheet2 As String
Dim OrigWks1 As Worksheet
Dim OrigWks2 As Worksheet
Sheets.Add after:=Sheets(Sheets.Count)
   Dim wks As Worksheet
   
   ResultSheet1 = InputBox("What is the first sheet to merge?")
   
   ResultSheet2 = InputBox("What is the second sheet to merge?")
   
   On Error Resume Next
   Set OrigWks1 = ActiveWorkbook.Sheets(ResultSheet1)
   Set OrigWks2 = ActiveWorkbook.Sheets(ResultSheet2)
   On Error Goto 0

   If OrigWks1 Is Nothing Or OrigWks2 Is Nothing Then
        MsgBox "An error occured", vbOKOnly
        Exit Sub
   End If
   
   Set wks = Sheets.Add(after:=Sheets(Sheets.Count))

   wks.Name = ResultSheet1 & " & " & ResultSheet2
   
   With OrigWks1
    Dim lastrow As Long
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A1:O" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp)
   End With

   With OrigWks2
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A2:O" & lastrow).Copy wks.Range("A" & wks.Rows.Count).End(xlUp).Offset(1)
   End With
   
Application.DisplayAlerts = False
OrigWks1.Delete
OrigWks2.Delete
Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:Wass_QA
Comment Utility
Hello,
thank you for your help.

it is working, however, it is creating 2 sheets.
one with the merged sheets,
and another empty sheet.

I guess you can't get the pop up message's  for me to enter first sheet and second sheet to merge at same time.

thanks
0
 

Author Closing Comment

by:Wass_QA
Comment Utility
Thank you very much
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now