Copying data from closed Workbooks

Hi All,

I have recently been creating a lot of macros and VB code in excel so decided to join a help forum. I dont really understand the principle of coding so as you can imagine, most codes are cobbled together from various google searches.

The code I need help with does everything that I need, except that I have to have the workbooks open to copy the data.

I am using windows().activate statement. Is there a way to do this without having to massively re-write the code below?

Private Sub CommandButton1_Click()
    Windows("Clarity.xls").Activate
    Sheets("Sheet1").Range("D2:D500").Copy
    Windows("Comparison.xls").Activate
    Sheets("Sheet1").Select
    Range("A2:A500").Select
    ActiveSheet.Paste
    Windows("Clarity.xls").Activate
    Sheets("Sheet1").Range("G2:G500").Copy
    Windows("Comparison.xls").Activate
    Sheets("Sheet1").Select
    Range("B2:B500").Select
    ActiveSheet.Paste
    Application.ScreenUpdating = False
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("C2:C500")
For Each cell In r
 If cell.Value = Blank Then
   cell.EntireRow.Hidden = True
 End If
Next
ErrHandler:
Application.ScreenUpdating = True
    Dim DataCount As Integer, NextRow As Integer, NextCol As Integer
    Windows("Assyst.xls").Activate
    With Worksheets("Sheet1")
        DataCount = .Range("A" & Rows.Count).End(xlUp).Row
        For Each cell In .Range("A2:A" & DataCount)
            On Error Resume Next
            Windows("Comparison.xls").Activate
            NextRow = WorksheetFunction.Match(cell.Value, _
                Worksheets("Sheet1").Range("A:A"), 0)
            If Err = 0 Then
                For NextCol = 4 To 4
                    Worksheets("Sheet1").Cells(NextRow, NextCol) = _
                        .Cells(cell.Row, NextCol)
                Next NextCol
            End If
            Err.Clear
        Next cell
    End With
End Sub

Thanks in advance to anyone who takes the time to read this. Also, as this is cobbled together, I'm sure that some of the code is superfluous so anything that can be removed, I would love to know. Who knows? Maybe I'll eventually get a handle on this.