Pasting values on first blank row

[Dan]

Hi,

I am trying to modify my macro to paste the values on the first blank row of
the "Totals" worksheet but the problem I am running into is that all of the
examples I see actually "paste" the data, where I do not.

It works great the first time, copying over all the information from the
other sheets, but if I run it a 2nd time, then it just copies right over the
original. I tried experimenting with a dynamic offset (instead of 0, X), but
I cannot get that to work.

Any help would be really appreciated! Thank you!
-Dan

-------------------------------------------------------------

Sub Starting()

Dim ws As Worksheet
Dim rCopy As Range
Dim rDest As Range
Dim rDate As Range
Dim rHours As Range
Dim LastRow As Long
Dim HowManyRows As Long

Set rDest = ActiveWorkbook.Worksheets("Totals").Range("C5")
Set rDate = ActiveWorkbook.Worksheets("Totals").Range("B2")
Set rHours = ActiveWorkbook.Worksheets("Totals").Range("E5")

For Each ws In ActiveWorkbook.Worksheets
'Define worksheets to loop through
If ws.Name = "Kristine" Or _
ws.Name = "Toby" Or _
ws.Name = "Carl" Or _
ws.Name = "Tamara" Or _
ws.Name = "Melanie" Or _
ws.Name = "Amy" Or _
ws.Name = "Dan" Then

With ws
If IsEmpty(.Range("A46").Value) = False Then
LastRow = 46
Else
LastRow = .Range("A46").End(xlUp).Row
End If
HowManyRows = LastRow - 6 + 1
End With

'Paste date
rDest.Offset(0, -1).Resize(HowManyRows).Value = rDate.Value

'Paste worksheet name (person)
rDest.Offset(0, -2).Resize(HowManyRows).Value = ws.Name

'Paste activity and category
With ws.Range("A6:B" & LastRow)
rDest.Resize(.Rows.Count, .Columns.Count).Value = .Value
Set rDest = rDest.Offset(.Rows.Count, 0)
End With

'Paste hours
With ws.Range("I6:I" & LastRow)
rHours.Resize(.Rows.Count, .Columns.Count).Value = .Value
Set rHours = rHours.Offset(.Rows.Count, 0)
End With

End If

Next ws

End Sub

 

[Don Guillett]

WithOUT looking at the whole thing

LastRow = .Range("A46").End(xlUp).Row

LastRow = .Range("A46").End(xlUp).Row+1

 

[Dan]

When I try to change it that way then I start getting extra blank rows that I
don't need.

It works great now, it just keeps on overriding what is already there
instead of starting down on a blank row.

I think the area of concern is:

'Paste date
rDest.Offset(0, -1).Resize(HowManyRows).Value = rDate.Value

'Paste worksheet name (person)
rDest.Offset(0, -2).Resize(HowManyRows).Value = ws.Name

'Paste activity and category
With ws.Range("A6:B" & LastRow)
rDest.Resize(.Rows.Count, .Columns.Count).Value = .Value
Set rDest = rDest.Offset(.Rows.Count, 0)
End With

'Paste hours
With ws.Range("I6:I" & LastRow)
rHours.Resize(.Rows.Count, .Columns.Count).Value = .Value
Set rHours = rHours.Offset(.Rows.Count, 0)

It doesn't seem like it should be too hard but I can't figure it out. A
specific change I tried was altering:

rDest.Offset(0, -1).Resize(HowManyRows).Value = rDate.Value

to replace the 0 with a dynamic number that tried to use a CountA function
to see how many rows contained data and then offset the starting point below
that.

Another idea would be me altering how rDest, rDate, and rHours are defined
at the top to make it a dynamic reference instead of a set cell to reference
all the other pasting from.

But in either case, I cannot figure out how to actually code those changes
and am getting a lot of errors.

-Dan

 

[Don Guillett]

If desired, send your wb to my addy below along with these msgs and exactly
what you want to do with examples.

 

[Dan]

Thanks Don for the offer - I sent it to you with the email subject "Pasting
values on first blank row Workbook".

-Dan