How to properly qualify a range

Hi,

I have written some code to export queries from access into excel and then apply some formatting.

Private Sub Export_Quarterly_Click()


Dim strPathFile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String, strInitialDirectory As String
Dim blnHasFieldNames As Boolean

strBrowseMsg = "Save Complete Records Report To:"
strInitialDirectory = "C:\Users\......\Quarterly VAT Records"
strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")

strPathFile = ahtCommonFileOpenSave(InitialDir:=strInitialDirectory, _
      Filter:=strFilter, OpenFile:=False, _
      DialogTitle:=strBrowseMsg, _
      Flags:=ahtOFN_OVERWRITEPROMPT)

If strPathFile = "" Then
      MsgBox "Report was not saved", vbOK, "No Selection"
      Exit Sub
End If

DoCmd.TransferSpreadsheet 1, 8, "Invoice Record 2", strPathFile, True
DoCmd.TransferSpreadsheet 1, 8, "VAT Record", strPathFile, True
DoCmd.TransferSpreadsheet 1, 8, "Recharge VAT Record", strPathFile, True

Set XL = CreateObject("Excel.Application")
XL.Visible = True
XL.UserControl = True
Set WB = XL.Workbooks.Open(strPathFile)
Set ws1 = WB.Worksheets(1)
Set ws2 = WB.Worksheets(2)
Set ws3 = WB.Worksheets(3)

Dim intCountofSheets, intCurrentSheet As Integer

With ws1
.Name = "Income"
.Range("A1") = "Invoice Date"
.Range("B1") = "Invoice Number"
.Range("C1") = "Net Amount"
lastrow = Range("A1").End(xlDown).Row
.Range(Range("B2"), Range("B" & lastrow)).NumberFormat = "\D\U\A\L0##\/\/###"
.Range("A" & lastrow + 2).value = "Net Total :"
.Range("C" & lastrow + 2).Formula = "=SUM(C2:C" & lastrow & ")"
End With

With ws2
.Name = "VAT"
.Range("A1") = "Payment Date"
.Range("B1") = "Invoice Number"
.Range("C1") = "Invoice Amount"
.Range("D1") = "VAT Rate"
.Range("E1") = "VAT Received"
End With

XL.DisplayAlerts = False
With ws3
If IsEmpty(.Range("A2").value) Then
.Delete
Else
.Name = "Recharged VAT"
.Range("A1") = "Payment Date"
.Range("B1") = "Invoice Number"
.Range("C1") = "Recharge"
.Range("D1") = "Details"
.Range("E1") = "Amount"
.Range("F1") = "VAT Rate"
.Range("G1") = "VAT Received"
End If
End With
XL.Application.DisplayAlerts = True

intCountofSheets = WB.Sheets.Count
intCurrentSheet = 1

Do While intCurrentSheet <= intCountofSheets

WB.Worksheets(intCurrentSheet).Activate
lastrow = Range("A1").End(xlDown).Row

If intCurrentSheet = 2 Then
Range(Range("B2"), Range("B" & lastrow)).NumberFormat = "\D\U\A\L0##\/\/###"
Range(Range("D2"), Range("D" & lastrow)).NumberFormat = "0.0%"
Range("E2").Formula = "=(C2*D2)"
Range("E2").Copy
Range("E2:E" & lastrow).PasteSpecial
Range(Range("E2"), Range("E" & lastrow + 2)).Select
Range("A" & lastrow + 2).value = "Net Total :"
Range("E" & lastrow + 2).Formula = "=SUM(E2:E" & lastrow & ")"
Range(Range("E2"), Range("E" & lastrow + 2)).Select
With Selection
.Style = "Currency"
.NumberFormat = "$#,##0.00"
End With
End If

If intCurrentSheet = 3 Then
Range(Range("B2"), Range("B" & lastrow)).NumberFormat = "\D\U\A\L0##\/\/###"
Range(Range("F2"), Range("F" & lastrow)).NumberFormat = "0.0%"
Range("G2").Formula = "=(E2*F2)"
Range("G2").Copy
Range("G2:G" & lastrow).PasteSpecial
Range("A" & lastrow + 2).value = "Recharged VAT Total :"
Range("G" & lastrow + 2).Formula = "=SUM(G2:G" & lastrow & ")"
Range(Range("G2"), Range("G" & lastrow + 2)).Select
With Selection
.Style = "Currency"
.NumberFormat = "$#,##0.00"
End With
End If

Columns("A").Select
Selection.EntireColumn.Insert
Columns("A").Select
Selection.ColumnWidth = 2
Cells.Select
With Selection.Font
.Name = "Tahoma"
.Size = 10
End With
Rows("1:1").Select
With Selection
.HorizontalAlignment = xlCenter
.EntireRow.Insert
.EntireRow.Insert
End With
Rows("1:1").Select
With Selection
.RowHeight = 26
With .Font
.Size = 20
.Bold = True
End With
End With
Rows("3:3").Select
With Selection.Font
.Bold = True
.Size = 12
End With

Range("B3").End(xlDown).Offset(2, 0).EntireRow.Select
With Selection.Font
.Bold = True
.Size = 12
End With

Range(Rows("4:4"), Rows("4:4").End(xlDown)).Select
Selection.HorizontalAlignment = xlRight

Columns.EntireColumn.Autofit

Range("A1").Select
Selection.value = ActiveSheet.Name

Range("B2").Select

intCurrentSheet = intCurrentSheet + 1
Loop

ws1.Activate
WB.Save

Set XL = Nothing
Set WB = Nothing
Set ws1 = Nothing
Set ws2 = Nothing
Set ws3 = Nothing

End Sub

Now this works perfectly fine but I know it isn't quite good practice.

In the first with ws1 section I understand that the lastrow = ...part works because it is in the currently active sheet. And then later down I have had to put some formatting into the If intcurrent = 2 and If intcurrent = 3 sections because I get an error if I bring them up under the with ws2 and with ws3 sections and use the lastrow reference again because, one it is referencing the first sheet, and 2 because there is no object properly set.

So my question is how can I neaten this up and what is the code for properly setting references to the ranges. Ideally i'd define the range Range("A1").End(xlDown).Row for each worksheet.

Thanks in advance!