Checking if a worksheet is empty otherwise should go to the final row of the data

[Ricardo Caicedo]

Hello Again Gurus</SPAN>

I am trying to copy and paste information from several worksheets to a main sheet named ‘Burn Reports”, but I need to check if a worksheet has a row already copied (so that means that a second filed needs to be appended).</SPAN>

I use the next code in RED</SPAN> but</SPAN> for some reason is not working anymore; the value for the range named as “rRNG” is 1(one) even though there is nothing in the file, so it shoul be zero.The variable eEmptyS does not get true...</SPAN>

While Len(fName) > 0</SPAN>
Set wbkOld = Workbooks.Open(fName)</SPAN>
'Sheets(1).Activate</SPAN>
wbkOld.Sheets(1).Cells.Find(What:="Rate", After:=ActiveCell, LookIn:=xlValues, LookAt:= _</SPAN>
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _</SPAN>
False, SearchFormat:=False).Activate</SPAN>
wbkOld.Sheets(1).Cells.FindNext(After:=ActiveCell).Activate</SPAN>
wbkOld.Sheets(1).Cells.FindNext(After:=ActiveCell).Activate</SPAN>
ActiveCell.Offset(2, 0).Range("A1").Select</SPAN>
Range(Selection, Selection.End(xlToRight)).Select</SPAN>
Range(Selection, Selection.End(xlDown)).Select</SPAN>
Selection.Copy</SPAN>
wbkNew.Worksheets("Burn Reports").Activate 'assumes data starts in A1</SPAN></SPAN>
Set rRNG = wbkNew.Worksheets("Burn Reports").Cells(1, 1).CurrentRegion</SPAN></SPAN>
If rRNG.Cells.Count = 0 Then 'no data in master sheet</SPAN></SPAN>
eEmptyS = True</SPAN></SPAN>
Else: eEmptyS = False</SPAN></SPAN>
End If</SPAN></SPAN>
If eEmptyS Then</SPAN>
ActiveSheet.Paste</SPAN>
Else:</SPAN>
Range("A1").Select</SPAN>
Selection.End(xlDown).Select</SPAN>
ActiveCell.Offset(2, 0).Range("A1").Select</SPAN>
ActiveSheet.Paste</SPAN>
End If</SPAN>
fName = Dir</SPAN>
wbkOld.Close False</SPAN>

Loop</SPAN>


Any Suggestions please</SPAN>

 

[JoeMo]

If a worksheet is empty then by your code rRNG is cell A1. So, the count of cells in rRNG is 1.

Maybe try:
If Application.Counta(rRNG) = 0 Then '<--- rRNG is empty

 

[Ricardo Caicedo]

Thank you JoeMo it works perfectly!