Hi Guys,

I am looking for some help.

I have a macro that I have adapted from Ron de Bruin. Instead of setting the
SourceRange explicitly I would like to code something like this:

Select Sheets("List").Range("e2") then select until the column is empty and
use that as my SourceRange.

Sub copy_1()
Dim SourceRange As Range
Dim DestRange1 As Range
Dim DestRange2 As Range
Dim DestRange3 As Range
Dim DestRange4 As Range
Dim DestSheet1 As Worksheet, Lr As Long
Dim DestSheet2 As Worksheet
Dim DestSheet3 As Worksheet
Dim DestSheet4 As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'fill in the Source Sheet and range
Set SourceRange = Sheets("Stock Summary").Range("A4:e55")

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet1 = Sheets("Opening Stock")
Lr = LastRow(DestSheet1)
Set DestSheet2 = Sheets("Closing Stock")
Lr = LastRow(DestSheet2)
Set DestSheet3 = Sheets("Purchases")
Lr = LastRow(DestSheet3)
Set DestSheet4 = Sheets("Usage")
Lr = LastRow(DestSheet4)

'With the information from the LastRow function we can
'create a destination cell and copy/paste the source range
Set DestRange1 = DestSheet1.Range("A2")
SourceRange.Copy DestRange1
Set DestRange2 = DestSheet2.Range("A2")
SourceRange.Copy DestRange2
Set DestRange3 = DestSheet3.Range("A2")
SourceRange.Copy DestRange3
Set DestRange4 = DestSheet4.Range("A2")
SourceRange.Copy DestRange4

' Set DestRange1 = DestSheet1.Range("A" & Lr + 1)
' SourceRange.Copy DestRange1
' Set DestRange2 = DestSheet2.Range("A" & Lr + 1)
' SourceRange.Copy DestRange2
' Set DestRange3 = DestSheet3.Range("A" & Lr + 1)
' SourceRange.Copy DestRange3
' Set DestRange4 = DestSheet4.Range("A" & Lr + 1)
' SourceRange.Copy DestRange4

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

Thanks
Albert