Need help cleaning up a macro that "copies" data from another work

[Ryan Hess]

This is the code I use to update Workbook2(sheet2) with ever changing data in
Workbook1(sheet1). Note: Workbook2 will be open and I initiate the update
with a button created on sheet2.

Sub UpdateList()

Dim wS1 As Worksheet
Dim wS2 As Worksheet

Workbooks.Open ("C:\Documents and
Settings\Ryanhess\Desktop\Workbook1.xls")

Set wS1 = Workbooks("Workbook2.xls").Sheets("Sheet2")
Set wS2 = Workbooks("Workbook1.xls").Sheets("Sheet1")

wS1.Unprotect Password:="fakepassword"
wS1.Range("B2:B1000").Value = wS2.Range("A2:A1000").Value
wS1.Range("D21000").Value = wS2.Range("B2:B1000").Value
wS1.Range("E2:E1000").Value = wS2.Range("C2:C1000").Value
wS1.Range("F2:F1000").Value = wS2.Range("D21000").Value
wS1.Range("G2:G1000").Value = wS2.Range("E2:E1000").Value

Workbooks("Workbook1.xls").Close savechanges:=False

wS1.Range("A2:G1000").Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDash
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

wS1.Range("B2:C1000").Select
With Selection.Borders(xlInsideVertical)
.LineStyle = False
End With

wS1.Protect Password:="fakepassword"
wS1.Range("A2").Select

End Sub


1) How can I simpilize the "copy/paste" of data to select Columns A thru E
and only selecting Rows that have data in Column A starting with Row 2 and
down?
Note: I suppose I would need to do this twice, once for Column A and once
for Columns B thru E as when I transfer the data from Workbook1(Sheet1)Column
A, info will go to Workbook2(Sheet2)Column B and when I transfer the data
from Workbook1(Sheet1)Column B thru E, info will go to
Workbook2(Sheet2)Column D thru G.

2) How can I create the borders on the selection of data "copy/pasted" from
Question1 so that I don't end up putting borders around Cells in empty Rows?


Thank you in advance for your help.
-Ryan

 

[Joel]

Sub UpdateList()

Dim wS1 As Worksheet
Dim wS2 As Worksheet

Workbooks.Open ("C:\Documents and Settings\" & _
"Ryanhess\Desktop\Workbook1.xls")

Set wS1 = Workbooks("Workbook2.xls").Sheets("Sheet2")
Set wS2 = Workbooks("Workbook1.xls").Sheets("Sheet1")

wS1.Unprotect Password:="fakepassword"
wS2.Range("A2:A1000").Copy _
Destination:=wS2.Range("B2:G1000")


Workbooks("Workbook1.xls").Close savechanges:=False

For RowCount = 2 To 1000
Set RowRange = wS1.Range("A" & RowCount & ":G" & RowCount)
If WorksheetFunction.CountA(RowRange) > 0 Then
RowRange.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlDash
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With Selection.Borders(xlInsideVertical)
.LineStyle = False
End With
End If
Next RowCount
wS1.Protect Password:="fakepassword"
wS1.Range("A2").Select

End Sub