CODEWITHSUNDEEP
excelvba
fali
fali

Reputation: 57

Print name of the sheet along with copied cell

I have this code where it loops through all the sheets in the workbook and copies the value in F9 of each sheet and pastes it in "Summary" sheet column A. How can I also print the sheet name in column B? So the value is next to the sheet name in the "Summary" sheet.

code:

Sub loopsheet()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
   If Not wks.Name = "Summary" Then
   wks.Range("F9:F" & wks.Cells(Rows.Count, "F").End(xlUp).Row).Copy _
   Destination:=Worksheets("Summary").Cells(Rows.Count, "A").End(xlUp).Offset(1)

   End If
   Next
End Sub

Thank you

Upvotes: 0

Views: 31

Answers (1)

urdearboy
urdearboy

Reputation: 14590

Create two variables to track the last rows of your sheets as you loop. This will help with readability in your code. The combination of these two variables can also help you deduce the size of the range where you need to drop your sheet name.

I believe cLR + pLR - 11 is the size of range. The offset is due to headers, LR offset, and the fact that you are starting your copy from the 9th row. After you run this, you may need to tweak it up or down one if i'm wrong. 

Option Explicit

Sub LoopSheet()

Dim ws As Worksheet
Dim Summary As Worksheet: Set Summary = ThisWorkbook.Sheets("Summary")
Dim cLR As Long, pLR As Long

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> Summary.Name Then
        cLR = ws.Range("F" & ws.Rows.Count).End(xlUp).Row
        pLR = Summary.Range("A" & Summary.Rows.Count).End(xlUp).Offset(1).Row

        ws.Range("F9:F" & cLR).Copy Summary.Range("A" & pLR)
        Summary.Range(Summary.Cells(pLR, 2), Summary.Cells(cLR + pLR - 11, 2)).Value = ws.Name
    End If
Next ws

End Sub

Upvotes: 1

Related Questions