PDA

View Full Version : Setting ws name



MPDK166
04-18-2011, 05:21 AM
How can I set the Worksheetname, where the data is coming from, in column F?

For Each ws In Worksheets
wsM_LR = wsMain.Cells.Find("*", , , , xlByRows, xlPrevious).Row
If UCase(ws.Name) <> UCase(wsMain.Name) And UCase(ws.Name) <> UCase(wsMain1.Name) And UCase(ws.Name) <> UCase(wsMain2.Name) And UCase(ws.Name) <> UCase(wsMain3.Name) And UCase(ws.Name) <> UCase(wsMain4.Name) And UCase(ws.Name) <> UCase(wsMain5.Name) Then
With ws
LR = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
.Range("C7:C" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "A")
.Range("B7:B" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "B")
.Range("E7:E" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "C")
.Range("G7:G" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "D")
.Range("F7:F" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "E")
End With
End If
Next ws

Bob Phillips
04-18-2011, 10:43 AM
I think a bit more explanation would be helpful.

Kenneth Hobs
04-18-2011, 10:57 AM
Before the End With:
wsMain.Cells(wsM_LR + 1, "F").Value2 = .Name

MPDK166
04-19-2011, 03:09 AM
The worksheet name is set in column F, however, this is only hapening for the data getting from the firstline of each sheet. I want to get the sheet name for the range of:
.Range("C7:C" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "A") -----> & ws.name in column F
.Range("B7:B" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "B") -----> & ws.name in column F
.Range("E7:E" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "C") -----> & ws.name in column F
.Range("G7:G" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "D") -----> & ws.name in column F
.Range("F7:F" & LR).Copy Destination:=wsMain.Cells(wsM_LR + 1, "E") -----> & ws.name in column F

any ideas?