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

dummies

New Member
Joined
Jul 10, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 10, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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:

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

Watch MrExcel Video

Forum statistics

Threads
1,130,217
Messages
5,640,943
Members
417,180
Latest member
nomans2325

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top