Hi

A basic stocks spreasheet made of 3 worksheets: All(sheet1), In(Sheet2), and Out(Sheet3).
I need to write a macro which copies/adds the contents of cells from Worsheet 1, into Worksheet 2 and 3, according to wether the goods move in or out, for any given date.

I am looking for ways to make the code faster(and maybe shorter) as the spreadsheet is very very long. I have read about using ranges or arrays, or using shorthand like Set rng = Worksheets(1).[a1], but somehow I can't make it work.


Here is what I could come up with so far(sorry about the length):


Sub FlashyMacro()

Screen Update = False
Dim Row1 As Double, Row2 As Double, Row3 As Double, SheetOnScreen As String
Application.Calculation = xlManual

Row1 = 5
Row2 = 2
Row3 = 2

While Worksheets("Sheet1").Cells(Row1, 1) <> ""
If Worksheets("Sheet1").Cells(Row1, 1) = "In" Then
If Worksheets("Sheet2").Cells(Row2, 1) = "" Then
Worksheets("Sheet2").Cells(Row2, 1) = Worksheets("Sheet1").Cells(Row1, 2)
[...]
Worksheets("Sheet2").Cells(Row2, 5) = Worksheets("Sheet1").Cells(Row1, 8)
Else
Worksheets("Sheet2").Cells(Row2, 2) = Worksheets("Sheet2").Cells(Row2, 2) + Worksheets("Sheet1").Cells(Row1, 5)
[...]
End If
End If
If Worksheets("Sheet1").Cells(Row1, 1) = "Out" Then
' Same for "out"
End If
Row1 = Row1 + 1
If Worksheets("Sheet1").Cells(Row1, 1) = "In" And Worksheets("Sheet2").Cells(Row2, 1) <> Worksheets("Sheet1").Cells(Row1, 2) Then
Row2 = Row2 + 1
If (SheetOnScreen = "Sheet2") Then
Worksheets("Sheet2").Cells(Row2 + 1, 1).Select
End If
Worksheets("Sheet2").Cells(Row2, 1) = ""
[...]
Worksheets("Sheet2").Cells(Row2, 5) = ""
End If
' Same for "OUT"
Wend

Calculate
Application.Calculation = xlAutomatic

Worksheets(SheetOnScreen).Activate

End Sub
Thanks