how to marge multiple sheet into one sheet

[emmr]

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]

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]

thanks a lot sir

[kevinlec21]

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