Hi Friends, First of all thank you very much to all the ninjas who r really helping me. and secondly, i'm learning VBA codes with the help of this. as an experiment, i have written some VBA codes (How Accurate is it i don't know) in a workbook which have more than 100 Worksheets (Roughly 150 to 170) with different Sheets name. i wrote codes such that in "Column N" the Sheet name should be entered automatically corresponding to column "A" (i.e. if Column A as matter upto A6 then the Sheet name should be entered upto N6). I've succeeded in achieving it. But the Problem is, the worksheet looping is not working. i.e it is working on Active worksheet only (if i select A1 in sheet2 then it code is executing to that sheet only). I want this procedure to continue till the end of the sheet.
i'm submitting a sample file and codes as well.
Second, is it possible to combine all sheets into Sheet 1 after executing the above code, without repeating the Headers as, all the headers in sheets are same.
Thanks in advance.
i'm submitting a sample file and codes as well.
Second, is it possible to combine all sheets into Sheet 1 after executing the above code, without repeating the Headers as, all the headers in sheets are same.
Thanks in advance.
Code:
Sub MergeWorkbook()
Dim myTsheets As String, wsname As String, c As String, row As Integer, col As Integer, wksht As Worksheet
myTsheets = Application.Worksheets.Count 'For Counting how many worksheets are there
col = 14 ' This is the column "N" where i want the table name
For Each wksht In ActiveWorkbook.Worksheets
wsname = ActiveSheet.Name
callerrows = Application.WorksheetFunction.Count(Range("A2:A10"))
On Error Resume Next ' mya be not required as all sheets will contain number
If callerrows <> 0 Then
c = callerrows + 1
For row = 2 To c
Cells(row, col).Value = wsname
Next row
End If
Next wksht
End Sub