Copy/Paste using a macro that identifies occuoied range

[JorgeAE]

I am working in the macro below. The purpose of the macro is to copy and
paste a range from one file to another. So far that part of the macro as
it is works perfect. However, I need to produce a spreadsheet in which I
copy another range but I would like the macro to be able to determine if
the range B5 is occupied them move and paste to range B9, if B9 is
already occuoied, then paste to range B13, if B13 is occupaied then
paste to range B17 tand continue the same pattern as necessary.

Sub CDamielM()
'
' CDamielM Macro
' Macro recorded 3/9/2006 by JAENJ
'

'
Range("B7").Select
Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
Selection.Copy
Windows("Attendance.xls").Activate
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("B9").Select
Windows("MASTER PROGRAM 2006bB.xls").Activate
Sheets("Check List").Select
End Sub

 

[Mark]

I am not sure if you know how to use custom functions, but here is one
that will return the first range that is blank.
You must call the function by passing it the range you want to start
from, in your case, Range("B9"). consider the following example:

Sub test()

Dim Myrng As Range

Set Myrng = FindBlank(Range("B9"))
MsgBox "First Blank found @ " & Myrng.Address

End Sub

if you run this macro then the Message Box will display the Address of
the first cell found that is blank.

Here is the Function.

Function FindBlank(CurRng As Range) As Range

Dim TempRng As Range
Set TempRng = CurRng
Do
Debug.Print TempRng.Address
If TempRng <> "" Then
Set FindBlank = TempRng.Offset(4, 0)
'Debug.Print "Checking next cell @ " & FindBlank.Address
Set TempRng = FindBlank
Else
FindBlank = TempRng
Exit Function
End If
Loop

End Function

If you have trouble give me a holler.