SOLVED

VBA for copying specific cells

Contributor

Tried a VBA for copying specific cells from each worksheet and appending the data in one worksheet ("Comb") which is working as my requirement. But I understand that the code runs all the worksheet including the one which I assigned for copying and appending the data from other sheets.  I need to  exclude the "Comb" worksheet (as I am appending the data in this worksheet) and only run the code in other worksheets. If so how the code should be updated.
I appreciate if someone can give suggession to make the code better
Sub Copydatamod()
Dim Sh As Worksheet
For Each Sh In ThisWorkbook.Worksheets
Sh.Range("B7:EA46").Copy
Sheets("Comb").Activate
Range("B" & Rows.Count).End(xlUp).Rows.Cells.Offset(2, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next Sh
End Sub

(Please guide if the last row selection and paste can be combined)

3 Replies
best response confirmed by Rudrabhadra (Contributor)
Solution

@Rudrabhadra 

Sub Copydatamod()
Dim Sh As Worksheet

For Each Sh In ThisWorkbook.Worksheets
Select Case Sh.Name
Case Is = "Comb"
Case Else

Sh.Range("B7:EA46").Copy
Sheets("Comb").Activate
Range("B" & Rows.Count).End(xlUp).Rows.Cells.Offset(2, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False

End Select
Next Sh

End Sub

Maybe with a SELECT CASE statement.

Thanks it works perfectly.
Is it possible to combine row#11 and #12
Thanks in advance...

@Rudrabhadra 

You are welcome. Sorry i don't know if it's possible to combine rows 11 and 12.