Plz Help- need sheetname for each row importd

Closed
Report
-
 Nitin Joshi -
Hi,

1st of all thanks for the earlier support, it really helped..
Now I am encountered problem in VBA codes,

I want to import data rows from multiple sheets into single sheet & record the sheet name in the last available column of the data row,
well I am executing codes for the first part 7 those are working fine, however I am stucked for second part, (i.e. recording sheet name as against each imported row)

I am executing below codes, but it is giving me error "OBJECT REQUIRED"...
I am not so familiar with the coding language and usally survived on copy-paste...

Please advise where i am going wrong what is the solution
====

Dim sh1 As Integer, ws As Worksheet
Dim Sh2 As String
Dim LastCell As Range
Dim lastDumpRow, lastDumpCol, lastTabRow, lastTabCol, myCity As Integer
Dim citySheet As String
sh1 = Worksheets("Master").Index 'change to your master sheet name
For Each ws In Worksheets
lastDumpRow = Worksheets(sh1).Range("B" & Rows.Count).End(xlUp).Row
If ws.Index <> sh1 Then
With ws
.Activate
Sh2 = ws.Name
lastDumpRow = .Range("B" & Rows.Count).End(xlUp).Row
lastDumpCol = .Range("B" & Column.Count).End(xlToLeft).Column
End With
For myCity = 1 To lastDumpRow
citySheet = Range("B" & myCity).Value
lastTabRow = Worksheets(sh1).Range("A" & Rows.Count).End(xlUp).Row + 1
lastDumpCol = Worksheets(sh1).Range("A" & Column.Count).End(xlToLeft).Row + 1
ws.Range("B" & myCity).EntireRow.Copy Destination:=Worksheets(sh1).Range("A" & lastTabRow)
Worksheets(sh1).Range("B" & lastTabCol).Value = Sh2
Next
Application.CutCopyMode = False
End If
Next ws
Worksheets(sh1).Activate


Thanking you in advance

1 reply

just to add...
originally the codes working fine, I just added some lines which i thought would be able to give me sheetname as against each row imported, in the last column
may be there is problem with these lines only :---
------------------------------------------
Dim Sh2 As String
Sh2 = ws.Name
Worksheets(sh1).Range("B" & lastTabCol).Value = Sh2
-----------------------------------------
so the point is if you make these line 'OFF, the code works fine without it, but i need sheet name in the last column for each row imported as well... PLZ help