Counting Macro, adding a less then statement

[Chris Akens]

I am trying to copy values from cells on sheet "scorecard" to page
"rounds", all the while increnting which row the data is copied to. So
far this is working well.

I am trying to come up with a piece of code to only paste to row 30,
and then once row 30 is reached, start overwriting at row 1. I cannot
seem to figure this out. So far here is my code, any and all help
would be greatly appreciated.


Sub testmacro()

Dim r As Integer
Dim c As Integer
Dim x As Integer


r = Sheets("hidden").Range("a1")
c = 1
x = 5

If IsEmpty(Sheets("Rounds").Cells(r, c)) Then

Sheets("ScoreCard").Range("AA21:AA25").Select
Selection.Copy
Sheets("Rounds").Select
Cells(r, c).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Sheets("ScoreCard").Activate
Sheets("ScoreCard").Range("AD21:AD25").Select
Selection.Copy
Sheets("Rounds").Select
Cells(r, x).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

With Sheets("hidden").Range("A1")
.Value = .Value + 1
End With

Else

Sheets("ScoreCard").Range("AA21:AA25").Select
Selection.Copy
Sheets("Rounds").Select
Cells(r, c).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

Sheets("ScoreCard").Activate
Sheets("ScoreCard").Range("AD21:AD25").Select
Selection.Copy
Sheets("Rounds").Select
Cells(r, x).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True



End If

End Sub

Thanks,
Chris

 

[Bernie Deitrick]

Chris,

Change:

With Sheets("hidden").Range("A1")
.Value = .Value + 1
End With

to

With Sheets("hidden").Range("A1")
.Value = .Value + 1
If .Value = 31 Then .Value = 1
End With


HTH,
Bernie
MS Excel MVP

 

[Chris Akens]

Thank you for the quick response. It works great except that once it
gets to 30 and goes to back to 1, it never leaves row 1 again. I find
this odd, because the value in cell a1 on sheet hidden goes back to 1,
but will never increment again. Any thoughts?

Also, I am trying to make it so it will not switch between sheets when
copy and pasting. I have tried using Application.ScreenUpdating =
false in numerous places, but that doesnt seem to help.

All help is greatly appreciated.

Thanks,
Chris

 

[Bernie Deitrick]

Chris,

I never really looked at your code except for the incrementing part. The
reason it stops is that you have code that say "Only do this if the cell is
empty". The first time through, you fill up the cells, then your code
doesn't do the same cells again because they are already full.

You're simply going to have to come up with a different logic system, or add
the incrementing part to both sections of the If-Then-Else structure. I
don't have enough information to help you further than that.

HTH,
Bernie
MS Excel MVP