Help adjusting code - offset row destination

[rikvny02]

Hello All Helpers
My code works great, However I'd like to add a second function. Once its added it overwrites the previous functions data. How can i adjust the code to add the data to the next available row?

Function vacation()

Dim sheetSource, shetDest As Worksheet
Dim rowSource, rowDest As Integer
Dim strcomments As String

Set sheetSource = Sheets("Vacation data")
rowSource = 4

Set shetDest = Sheets("Vacation load")
rowDest = 9

While sheetSource.Cells(rowSource, 4).Formula <> ""
strcomments = sheetSource.Cells(rowSource, 5).Value


boolAddToSheet = False

Select Case strcomments
Case Is >= 32
shetDest.Cells(rowDest, 6).Value = sheetSource.Cells(rowSource, 2).Value 'hours
shetDest.Cells(rowDest, 8).Value = sheetSource.Cells(rowSource, 5).Value 'employee id
shetDest.Cells(rowDest, 13).Value = sheetSource.Cells(rowSource, 8).Value 'shift
shetDest.Cells(rowDest, 14).Value = sheetSource.Cells(rowSource, 9).Value 'check group

rowDest = rowDest + 1



End Select

rowSource = rowSource + 1
Wend

End Function

 

[rikvny02]

Should I go another route and try new VBA

 

[jolivanes]

Just explain in a concise manner what you want to do.

 

[DanteAmor]

Hi @rikvny02

Change this line:

rowDest = 9

For this:

rowDest = shetDest.Range("H" & Rows.Count).End(3).Row + 1

I hope it's what you want.

 

[rikvny02]

Just explain in a concise manner what you want to do.

The function copies data from the Vacation Data (sheet) to the Vacation Load (sheet) only if there is data to transfer. My issue was if, the function was used more than once it was overwriting the data transferred to the vacation load. Hope this helps others in understanding what I was trying to accomplish. The below correction from @DanteAmor worked great.

 

[rikvny02]

Hi @rikvny02

Change this line:

rowDest = 9

For this:

rowDest = shetDest.Range("H" & Rows.Count).End(3).Row + 1

I hope it's what you want.

It was exactly what I was looking for THANK YOU.