pulling the data from one sheet to another depending on the condition.

[dummies]

i have this code which can pull the data . i have 7 sheets. loop through all the sheets and copy the data if the condition is fulfilled.
but i can copy only data of one sheet.
i dont know where i have made a mistake. so i would be very grateful if someone could help me to look into this code and point me into right direction.
thank you very much.

Dim fileName As String
    Dim WS_name As Worksheet
    Dim scrSht As Worksheet
    Dim desSht As Worksheet
    Dim wrkbook As Workbook
    Dim wb As Workbook
    Dim StrRow As Long
   
         On Error GoTo ErrorHandler
'        fileName = wrkbook.Sheets("REcords").Range("Filename").Value
      
        Set wb = Workbooks.Open(fileName)
        Set scrSht = ActiveSheet
        Set wrkbook = Workbooks("Application.xlsm")
        Set desSht = wrkbook.Sheets("sht_form")
        StrRow = 2
       
'        lastRow = desSht.Cells(Rows.Count, 1).End(xlUp).row
'        lastcol = desSht.Cells(2, Columns.Count).End(xlToLeft).Column
      
                For Each WS_name In Worksheets
                        Debug.Print WS_name.Name
                   
                    If WS_name.Tab.ColorIndex = 16 Then
                    End If
                   
                    If WS_name.Range("A1").Value = "CONFIRMATION" And WS_name.Tab.ColorIndex = xlColorIndexNone Then
                  
                        desSht.Range("A2") = ThisWorkbook.Path
                        desSht.Range("B2") = ThisWorkbook.Name
                        desSht.Range("C2") = WS_name.Name
                        desSht.Range("D2") = WS_name.Range("B2:P2").Value
                        desSht.Range("E2") = WS_name.Range("B3").Value
'                        desSht.Range("F2") = WS_name.Range("").Value
'                        desSht.Range("G2") = WS_name.Range("").Value
                      
    Dim rng As Range, cell As Range
    Dim R_Count As Long
        
         R_Count = 1
             Set rng = Range("Q1:AD33")
                 For Each cell In rng
                     If cell.Value <> "" Then
                      
                         desSht.Cells(StrRow, templets.other1 + R_Count) = cell.Value
                         R_Count = R_Count + 1
                     End If
                  Next cell
                         
                     Else
                          MsgBox "no"
                     End If
                   Next
       
        StrRow = StrRow + 1
   
ErrorHandler:
    MsgBox Err.Description, Err.Number

 

[StephenCrump]

A couple of very quick observations to start with:

- I'm guessing that Range("Q1:AD33") should perhaps be WS_name.Range("Q1:AD33") ?
- You overwrite desSht.Range("A2:E2") in each iteration of the loop.

Your code could probably be written more efficiently, but without seeing your layout it's a little hard to see what it's doing.

 

[dummies]

A couple of very quick observations to start with:

- I'm guessing that Range("Q1:AD33") should perhaps be WS_name.Range("Q1:AD33") ?
- You overwrite desSht.Range("A2:E2") in each iteration of the loop.

Your code could probably be written more efficiently, but without seeing your layout it's a little hard to see what it's doing.

thank you very much WS_name.Range("Q1:AD33") did a trick. it worked as expected.
in case of desSht.Range("A2:P2") , i have admission number from A2 to P2 (eg.1234567890123456) ,so i wanted to pull that value from A2 to P2 in D2 in another sheet.
but i cant get only value of cell A2 (only one number that is 1).how do i do with this ? thank you once more.

 

[StephenCrump]

i have admission number from A2 to P2 (eg.1234567890123456) ,so i wanted to pull that value from A2 to P2 in D2 in another sheet.
but i cant get only value of cell A2 (only one number that is 1).how do i do with this ? thank you once more.

So A2 is 1, B2 is 2, C2 is 3 .... P2 is 6?

I think you have the relatively new CONCAT() function in Excel 2016, so one way you could do this would be:

desSht.Range("D2") = Application.Concat(WS_name.Range("B2:P2").Value)