Copying data from an open workbook

**thedon_1** · 11-04-2011, 08:48 AM

I have some code that looks through my open workbooks, looks for a sheet called UK_3 and then copies the data from row 3 to the end and then pastes it into my current workbook, sheet1.

Unfortunately it doesn't actually select and copy the data. Any idea what's wrong?

Sub test()
Dim wb As Workbook
Dim ws As Worksheet

With ThisWorkbook.Sheets("Sheet1")
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete
    
    For Each wb In Application.Workbooks
        For Each ws In wb.Worksheets
            If ws.Name = "UK_3" Then
                
                    With wb.Sheets("UK_3")
                    Rows("3:3").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                    
                    
                    ThisWorkbook.Activate
                    Sheets("Sheet1").Select
                    Range("A3").Select
                    ActiveSheet.Paste
                    
                    End With
                    
                
                
                
                Exit For
            End If
        Next ws
    Next wb

End With


End Sub

Thanks

**Steffen Thomsen** · 11-04-2011, 09:09 AM

Hi

Try this change and se if it helps

Sub test()
Dim wb As Workbook
Dim ws As Worksheet

With ThisWorkbook.Sheets("Sheet1")
    Rows("3:3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete
End With  

    For Each wb In Application.Workbooks
        For Each ws In wb.Worksheets
            If ws.Name = "UK_3" Then
                
                    With wb.Sheets("UK_3")
                    Rows("3:3").Select
                    Range(Selection, Selection.End(xlDown)).Select
                    Selection.Copy
                    
                    
                    ThisWorkbook.Activate ' here you have to define the actual workbook you need to have the data, or you will just paste it in to the current wb in the loop, thus making no changes.
                    Sheets("Sheet1").Select
                    Range("A3").Select
                    ActiveSheet.Paste
                    
                    End With
                    
                
                
                
                Exit For
            End If
        Next ws
    Next wb

End Sub

**johncassell** · 11-04-2011, 09:16 AM

try selecting the workbook before attempting to copy so add this line in between your existing lines

If ws.Name = "UK_3" Then
                ws.Activate
                    Rows("3:3").Select

**thedon_1** · 11-04-2011, 09:24 AM

Originally Posted by johncassell

try selecting the workbook before attempting to copy so add this line in between your existing lines

If ws.Name = "UK_3" Then
                ws.Activate
                    Rows("3:3").Select

That seemed to do the trick, nice one!

**thedon_1** · 11-04-2011, 09:18 AM

From what i've read, ThisWorkbook selects the workbook running the macro. I've used the line in other things similar to this and it's been OK. I'm not 100% though so it may be that.

The thing is, the workbook in which I will be using the code will change so I don't really want to define the workbook name.

I tried defining the current books name and checking if it is the cause of the problem but it just does the same thing.

Any way around this?

**Steffen Thomsen** · 11-04-2011, 09:27 AM

Hi,

Try this sub instead.
This makes no selections and is faster.

Sub Steffen()

Dim wb As Workbook
Dim ws As Worksheet
Dim thWb As Workbook

Set thWb = Workbooks("yourworkbookname")

With thWb
    Sheets(1).Rows("3:3").Delete
End With

    For Each wb In Application.Workbooks
        For Each ws In wb.Worksheets
            If ws.Name = "UK_3" Then 
            ws.Rows("3:3").Copy
            thWb.Sheets(1).Range("A3").PasteSpecial
            GoTo endSub
        Next ws
    Next wb
endSub:
End Sub