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

WithOUT looking at the whole thing
LastRow = .Range("A46").End(xlUp).Row
LastRow = .Range("A46").End(xlUp).Row+1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan" wrote in message
...
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

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" wrote:

WithOUT looking at the whole thing
LastRow = .Range("A46").End(xlUp).Row
LastRow = .Range("A46").End(xlUp).Row+1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan" wrote in message
...
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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan" wrote in message
...
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" wrote:

WithOUT looking at the whole thing
LastRow = .Range("A46").End(xlUp).Row
LastRow = .Range("A46").End(xlUp).Row+1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan" wrote in message
...
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

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

-Dan



"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan" wrote in message
...
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" wrote:

WithOUT looking at the whole thing
LastRow = .Range("A46").End(xlUp).Row
LastRow = .Range("A46").End(xlUp).Row+1


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dan" wrote in message
...
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