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
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