Plz Help- need sheetname for each row importd

Nitin Joshi - Sep 29, 2010 at 06:28 AM
 Nitin Joshi - Sep 30, 2010 at 02:31 AM

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
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
Application.CutCopyMode = False
End If
Next ws

Thanking you in advance

1 response

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