MrSams

09-27-2016, 05:38 PM

I am a professor at Texas A&M and a student came to me for some help. Excel is not in my bag of tricks so I decided to get some assistance here hopefully so bare with me when I try to explain what he wants.

I want to grab the numbers on tab “Drawn Numbers” from the bottom up starting at I2500:O2500 to I2500:O2 and place them to the “Input Sheet” starting at I49:O101. (Numbers 53 Game 3)

Every time we grab a row from the “Drawn Numbers” sheet I want to move each row down 1 row on the “Input” sheet and then have the Macro in Module 1 automatically take off running. Once complete it continues running with a new row from the “Drawn Numbers” sheet and the process starts over again.

So, row 49 on the “Input” sheet becomes numbers 52, row 50 becomes 53, row 49 becomes 54 and so on.

Here is the Macro in Module 1 that needs to run "after" the Input sheet and drawn Numbers sheet copy and paste data. I need help automating the Input and Drawn Numbers sheet. This Macro runs great. Its just included so we can tie into this after the numbers from the Drawn Numbers sheet is copied and pasted into the Input sheet

Option Explicit

Sub ertert()

Dim x, i&, j&

With Sheets("Counter Totals")

x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value

End With

For i = 1 To UBound(x)

If (x(i, 1)) = "Game" Then j = j + 1

If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then

With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)

.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0)

End With

End If

Next i

End Sub

Sub ClearGames()

Dim wsh As Worksheet, r As Range

For Each wsh In ThisWorkbook.Sheets

If Not wsh Is ActiveSheet Then

For Each r In wsh.Columns(1).SpecialCells(2).Areas

r.Resize(, 91).Offset(1).CLEAR

Next

End If

Next wsh

End Sub

I understand with out all the data from the external sheets it would be hard for you to test. If you all would be so kind to "include" a macro in the attached sheet or a way for me to copy and paste it into his book I cn try it out and let you all know how the testing does. His book is too large to post

Most thanks for your help here at vbaexpress

MrSams

I want to grab the numbers on tab “Drawn Numbers” from the bottom up starting at I2500:O2500 to I2500:O2 and place them to the “Input Sheet” starting at I49:O101. (Numbers 53 Game 3)

Every time we grab a row from the “Drawn Numbers” sheet I want to move each row down 1 row on the “Input” sheet and then have the Macro in Module 1 automatically take off running. Once complete it continues running with a new row from the “Drawn Numbers” sheet and the process starts over again.

So, row 49 on the “Input” sheet becomes numbers 52, row 50 becomes 53, row 49 becomes 54 and so on.

Here is the Macro in Module 1 that needs to run "after" the Input sheet and drawn Numbers sheet copy and paste data. I need help automating the Input and Drawn Numbers sheet. This Macro runs great. Its just included so we can tie into this after the numbers from the Drawn Numbers sheet is copied and pasted into the Input sheet

Option Explicit

Sub ertert()

Dim x, i&, j&

With Sheets("Counter Totals")

x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value

End With

For i = 1 To UBound(x)

If (x(i, 1)) = "Game" Then j = j + 1

If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then

With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)

.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0)

End With

End If

Next i

End Sub

Sub ClearGames()

Dim wsh As Worksheet, r As Range

For Each wsh In ThisWorkbook.Sheets

If Not wsh Is ActiveSheet Then

For Each r In wsh.Columns(1).SpecialCells(2).Areas

r.Resize(, 91).Offset(1).CLEAR

Next

End If

Next wsh

End Sub

I understand with out all the data from the external sheets it would be hard for you to test. If you all would be so kind to "include" a macro in the attached sheet or a way for me to copy and paste it into his book I cn try it out and let you all know how the testing does. His book is too large to post

Most thanks for your help here at vbaexpress

MrSams