PDA

View Full Version : [SOLVED] how to marge multiple sheet into one sheet



emmr
08-06-2019, 06:10 AM
i've already using this, but facing some difficulties expecting your help to solved that
i've attached my file also for your convenience
my "Input" sheet will not consider for marge, how?
TIA

Paul_Hossler
08-06-2019, 07:23 AM
Try something like this



Option Explicit

Sub MergeSheet()

'Declaring the Variables
Dim LastRow As Long, ShtCnt As Long, i As Long
Dim ShtName As String
Dim NewSht As Worksheet, ws As Worksheet

'Assigning a Sheet Name by UserInput
ShtName = vbNullString

Do While Len(ShtName) = 0

ShtName = InputBox("Enter the Sheet Name you want to create" & vbCrLf & vbCrLf & "[Cancel] to exit", "Merge Sheet", "Master Sheet")

'get out if canceled
If Len(ShtName) = 0 Then Exit Sub

i = 0
On Error Resume Next
i = Worksheets(ShtName).Index
On Error GoTo 0

If i = 0 Then
'Create a New Sheet
Worksheets.Add.Name = ShtName

'Assigning NewSht as Current Sheet
Set NewSht = Worksheets(ShtName)

'Moving Worksheet to the beginning of this workbook
NewSht.Move before:=Worksheets(1)

Else
Call MsgBox("Worksheet '" & ShtName & "' already exists. Try again", vbCritical + vbOKOnly, "Merge Sheet")
ShtName = vbNullString
End If
Loop


'Count of Total Worksheet in the present workbook
ShtCnt = Sheets.Count


'Copying all the data to the New Sheet Using For Loop
For Each ws In Worksheets
If Not ws Is Worksheets("Input") Then
If Len(NewSht.Cells(1, 1).Value) = 0 Then
ws.UsedRange.Copy NewSht.Cells(1, 1)

Else
ws.UsedRange.Offset(1, 0).Resize(ws.UsedRange.Rows.Count - 1, ws.UsedRange.Columns.Count).Copy NewSht.Cells(LastRow + 1, 1)
End If

LastRow = NewSht.Cells.SpecialCells(xlCellTypeLastCell).Row
End If
Next

'Displaying the Message after copying data successfully
Call MsgBox("Data has been copied to " & ShtName, vbInformation + vbOKOnly, "Merge Sheet")
End Sub

emmr
08-06-2019, 08:39 AM
thanks a lot sir

kevinlec21
08-20-2021, 11:52 AM
Hi Paul, please share how to add the vba code on (paste formulas as values) in addition to this thread vba, thank you in advance