simple VB

Hey all,

I have the following code that summarises the data from my first sheet on the second sheet. I need it to first clear all of the data on the second sheet though, when I added some code to do this it occurs at the end, which obvioulsy just erases all of the data sent accross. Also any other suggestions would be great as I'm rather new to VB and I'm sure I'm not using it that well!

Sub TransferData()
    Sheets("External Invoicing").Select   'THIS IS THE SHEET TO BE CLEARED
    Range("A5:S500").Select
    Selection.ClearContents
Dim strInvoiceNumber As String
Dim strSDM As String
Dim strCI As String
Dim strECHNo As String
Dim strPONo As String
Dim strProjectName As String
Dim strCost As String
Dim strExpenses As String
Dim i As Integer
Dim j As Integer

i = 7
j = 5

 
Do Until Cells(i, 3) = ""
    
    strInvoiceNumber = Cells(i, 128)
    strSDM = Cells(i, 5)
    strCI = Cells(i, 6)
    strECHNo = Cells(i, 2)
    strPONo = Cells(i, 4)
    strProjectName = Cells(i, 3)
    strCost = Cells(i, 117)
    strExpenses = Cells(i, 126)

    If Not strInvoiceNumber = "" Then

        Sheets("External Invoicing").Select
        Cells(j, 1).Select
        ActiveCell.FormulaR1C1 = strSDM
        Cells(j, 2).Select
        ActiveCell.FormulaR1C1 = strCI
        Cells(j, 3).Select
        ActiveCell.FormulaR1C1 = strECHNo
        Cells(j, 5).Select
        ActiveCell.FormulaR1C1 = strInvoiceNumber
        Cells(j, 8).Select
        ActiveCell.FormulaR1C1 = strPONo
        Cells(j, 9).Select
        ActiveCell.FormulaR1C1 = strProjectName
        Cells(j, 10).Select
        ActiveCell.FormulaR1C1 = strCost
        Cells(j, 11).Select
        ActiveCell.FormulaR1C1 = strExpenses
        Sheets("Monthly Data").Select

    j = j + 1

    i = i + 1

    Else

    i = i + 1

    End If

Loop

End Sub

I don't understand what you exactly need... in the attached file I added a macro in the first sheet where I tried to simplify your macro... I hope it can help you.

Regards,
Antonnio

With your macro as you are only using the data from the Monthly Data once it makes no sense to fill avariable with the data before putting into the External Invoicing sheet when it can be all done it the one action.

If using the data more than once then variables are the recomended way to go.

Sub TransferData()
Dim strInvoiceNumber As String
Dim wsExtInv As Worksheet
Dim wsMonDat As Worksheet

Dim i As Integer
Dim j As Integer

Set wsExtInv = Sheets("External Invoicing")
Set wsMonDat = Sheets("Monthly Data")

wsExtInv.Range("A5:S500").ClearContents
    
i = 7
j = 5

Do Until Cells(i, 3) = ""
   strInvoiceNumber = wsMonDat.Cells(i, 128).Value
   If Not strInvoiceNumber = "" Then
      wsExtInv.Cells(j, 5).Value = strInvoiceNumber
      wsExtInv.Cells(j, 1).Value = wsMonDat.Cells(i, 5).Value
      wsExtInv.Cells(j, 2).Value = wsMonDat.Cells(i, 6).Value
      wsExtInv.Cells(j, 3).Value = wsMonDat.Cells(i, 2).Value
      wsExtInv.Cells(j, 8).Value = wsMonDat.Cells(i, 4).Value
      wsExtInv.Cells(j, 9).Value = wsMonDat.Cells(i, 3).Value
      wsExtInv.Cells(j, 10).Value = wsMonDat.Cells(i, 117).Value
      wsExtInv.Cells(j, 11).Value = wsMonDat.Cells(i, 126).Value
      j = j + 1
      i = i + 1
   Else
      i = i + 1
   End If
Loop
End Sub