Loop through worksheets and copy rows

[metanei7]

Hello,

I am kind new on VBA so please be kind :D

I am trying to make a code that will loop through worksheets that have same name but different number exp Dataset 2(1), Dataset 2(2), Dataset 2(3).

Then i want to have another loop inside that loop that will look for the last row from column A and copy it on worksheets("Sheet1").
If the copied value has the same number on worksheets("sheet1") on column A, i want it to then add only the value from the column D which it was trying to copy from.

Anyone has any clue how to do it?

Regards,
Metanei

 

[gallen]

Hello, and welcome.

Missing a little info. When you say "same name but different number" can the maximum / minimum number change, is there ever a chance that a number in the sequence is missing?

Are there any other sheets with the word "Dataset" in the sheet title that you want to miss out? If not it may just be easier to loop through all sheets with that in the name and perform the actions

 

[gallen]

A little more confusion with the logic. Is this correct?

1. Loop through all worksheets with "Dataset" in their name
2. Get the last used cell in column A of each worksheet
3. Copy that cell to the last blank space on column A of Sheet1.
4. If that value already exists on Sheet1 then also copy the value from column D of the last row on the Dataset sheet

 

[metanei7]

Hello and thank you for the quick reply.

So let me explain myself better :D

For the first question, unfortunately the first Sheet is named as Dataset 2, the second Dataset 2 (1), the 3rd Dataset 2 (2). This was created from another loop i have already made that checks folders and copies the sheet. On the same workbook i have 2 more sheets that i am doing my loops jobs. Sheet1 and Sheet2.

Your first point is correct:

1. Loop through all worksheets with "Dataset" in their name

2.Get the last used cell in column A of each worksheet
I want the last row to be copied to the Sheet1, but the help is that on column A it has different numbers, all of the randomly.

3.Copy that cell to the last blank space on column A of Sheet1.
I want to copy the row no cell.

4.If that value already exists on Sheet1 then also copy the value from column D of the last row on the Dataset sheet
The last one is more complicated, every row we copy the column A has a specific number(like code number as 157 or 78, it can go up to 187). For every row we copy, on column D it has the frequency(as 3 or 7 or 20) that this code happen. And on column E it has the duration(as 25.5 or 0.7 or 3.4) that this code happened. If the code number already exists on Sheet1 i want those 2 values to be added as they are numbers on sheet1 which it has already some duration time and frequency as we copied it in the past.

 

[metanei7]

The point 4.If that value already exists on Sheet1 then also copy the value from column D of the last row on the Dataset sheet

Be aware that the numbers are random, every row can have from zero to 20 code numbers in different locations

 

[gallen]

OK I'm little more confused now

Not to worry we'll get there.

So do we only copy the row if the value in column A is found?

Here is some starter code showing where it's going. I know it doesn't achieve what you want because I'm not clear but it's the start of the logic. I've commented as much as possible.

Are you ok with using macros at least?

Sub LoopSheets()


     Dim ws As Worksheet 'loop variable
     Dim wsPaste As Worksheet 'worksheet to sopy to
     Dim rFind As Range 'cell value to look for
     Dim sCopy As String 'variable to hold the valuein the copied cell
     Dim lr As Long 'last used row
     Dim nb As Long 'next blank row
     


     Set wsPaste = Worksheets("sheet1")
     
     'get the next blank row on Sheet1
     nb = wsPaste.Range("A" & Rows.Count).End(xlUp).Row + 1
     
     'loop through ALL sheets in this workbook
     For Each ws In ThisWorkbook.Worksheets
     
        'does the worksheet title have "Dataset" in it?
        If InStr(1, ws.Name, "Dataset") > 0 Then
        
            'get last row
            lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        
            'get the value in the last used cell in column A
            sCopy = ws.Range("A" & lr)
            
            'try to find the vlaue in column A of sheet1
            Set rFind = wsPaste.Range("A:A").Find(sCopy)
            
    '*************This is where the confusion is******************
            
            'if rFind is nothing then the value wasn't found
            If rFind Is Nothing Then
                'Value not found so what do we paste?
            Else ' value was found
                'Copy value from column D? Surely if we are copying the whole row then column D comes with it.
                
            End If
        End If
        
     Next ws
    
End Sub

 

[metanei7]

All right we are getting there, Thank you @gallen for your help

So if we don't find anything i just want it to go to the next sheet which i believe your code does that.
I don't understand though even if you have write it
'get the value in the last used cell in column A
sCopy = ws.Range("A" & lr)

'try to find the vlaue in column A of sheet1
Set rFind = wsPaste.Range("A:A").Find(sCopy)

It doesn't copy anything when i try to run the macro, or is there more lines that need to be added?
I am really new on this :D

Regards,
Metanei

 

[metanei7]

I have just realized i didn't answer your questions :D

So yes we only copy the row if the A isn't found on Sheet1.
I am ok with using macro at least executing it but i don't know how VBA works :D

Regards,
Metanei

 

[metanei7]

I believe i am missing the paste function . So after copy should have something like paste? And if the column value is the same it should like insert the value from column d and e.
You are far better than me on this so i am counting on one of the guys that know more than me.

 

[gallen]

Hello

I've amended the code to paste the row.

Always ensure while testing , you use a copy of your original data as this alters your sheets

Sub LoopSheets()


     Dim ws As Worksheet 'loop variable
     Dim wsPaste As Worksheet 'worksheet to sopy to
     Dim rFind As Range 'cell value to look for
     Dim sCopy As String 'variable to hold the valuein the copied cell
     Dim lr As Long 'last used row
     Dim nb As Long 'next blank row
     


     Set wsPaste = Worksheets("sheet1")
     
     'get the next blank row on Sheet1
     nb = wsPaste.Range("A" & Rows.Count).End(xlUp).Row + 1
     
     'loop through ALL sheets in this workbook
     For Each ws In ThisWorkbook.Worksheets
     
        'does the worksheet title have "Dataset" in it?
        If InStr(1, ws.Name, "Dataset") > 0 Then
        
            'get last row
            lr = ws.Range("A" & Rows.Count).End(xlUp).Row
        
            'get the value in the last used cell in column A
            sCopy = ws.Range("A" & lr)
            
            'try to find the vlaue in column A of sheet1
            Set rFind = wsPaste.Range("A:A").Find(sCopy)
            
            
            'if rFind is nothing then the value wasn't found
            If rFind Is Nothing Then
                'Value not found so pate nothing
            Else ' value was found
            
                'Copy the last row
                ws.Rows(lr).Copy
                
                'Paste the copied row to the next blank row
                wsPaste.Range("A" & nb).PasteSpecial xlPasteAll
                
                'next balnk row is +1
                nb = nb + 1
            End If
        End If
        
     Next ws
    
End Sub