Help with VBA data new row each time

C

Csummers

New Member
Joined
Mar 10, 2018
Messages
10
I will try and explain what im doing just playing around with VBA and excel to see if i can get this to work. I have used this in the past but it has been so long since i have done anything with it im at a loss of where im going wrong.


I have 4 sheets that im working with


1 - Stats
2 - Ratings
3 - Rerates 2016
4 - Final Ratings


Im basically trying to make a macro with VBA to Copy the Ratings in range (B2:S2) over to the Rerates 2016 range (B7:S7)
Then copy range (B8:N8) on the Rerates 2016 sheet and paste in the Final Ratings sheet to a new row each time i would click the macro button
after i paste the data in the next row on the Final Ratings page i would then go back to the Stats sheet and delete the data out of range (A10:H10)
then the same thing on the Ratings sheet delete data out of the range (A2:S2) after this it should hopefully start the process over until i run out of data
but what ends up happening after 1 run it stops and the data that is pasted in to the Final Ratings sheet is incomplete basically it doesnt match the data that was copied out of the Rerates 2016 sheet range (B8:N8).


Below is the test i ran and the code i was using


ExcelRerateAuto.JPG



Range("B2:S2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Rerates 2016").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B8:N8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Paste Rerates here First").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Total Player").Select
Rows("10:10").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("RatingVitalCombined").Select
Rows("2:2").Select
Selection.Delete Shift:=xlUp
Range("B2:S2").Select
Selection.Copy
Sheets("Rerates 2016").Select
Range("B7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub


Any help would be awesome


Thanks
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
Csummers,

Welcome to the MrExcel forum.

It would help if we could see your actual raw data workbook/worksheets, and, what the results (manually formatted by you) should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
 
Upvote 0
hiker95 said:
Csummers,

Welcome to the MrExcel forum.

It would help if we could see your actual raw data workbook/worksheets, and, what the results (manually formatted by you) should look like.

You can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com
Click to expand...

here is a link to the location of the file

http://www.fchlonline.com/FCHL/Exce...CHL Rerate System Test (Updated 3_10_18).xlsm
 
Upvote 0
Csummers,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.


Then, in your next reply, also post your original VBA code, below, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]
 
Upvote 0
hiker95 said:
Csummers,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.


Then, in your next reply, also post your original VBA code, below, please use Code Tags - like this:

[code=rich]

Paste your code here.

[/code]
Click to expand...

here is the file
https://www.dropbox.com/sh/28qolm7vozlxf39/AAACRLEniHcMexAzet-hIQ2ta?dl=0

wasnt sure how to make it to where you could edit but hopefully you can download and edit

here is the code

Rich (BB code): 
Range("B2:S2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B8:N8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Paste Rerates here First").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Total Player").Select
    Rows("10:10").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("RatingVitalCombined").Select
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("B2:S2").Select
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 
Upvote 0
Csummers,

Your latest link brings up/displays the following:

FCHL Rerate System Te...8).xlsm

Tool.xlsx


And, you have not displayed all of your macro code????


Maybe someone else on MrExcel will be able to help you.
 
Last edited:
Upvote 0
hiker95 said:
Csummers,

Your latest link brings up/displays the following:

FCHL Rerate System Te...8).xlsm

Tool.xlsx


And, you have not displayed all of your macro code????


Maybe someone else on MrExcel will be able to help you.
Click to expand...

here is the link to just that file not sure why it was showing both files

https://www.dropbox.com/s/n6fecb5cs6485al/Tool.xlsx?dl=0

here is all the code

Code: 
Sub AutoRerate()
'
' AutoRerate Macro
'

'
    Range("B2:S2").Select
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub
Sub AutoRerate2()
'
' AutoRerate2 Macro
'

'
    Range("B2:S2").Select
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B8:N8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Paste Rerates here First").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Total Player").Select
    Rows("10:10").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("RatingVitalCombined").Select
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Sheets("Paste Rerates here First").Select
    Range("A1").Select
End Sub
Sub AutoRerate3()
'
' AutoRerate3 Macro
'

'
    Range("B2:S2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B8:N8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Paste Rerates here First").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Total Player").Select
    Rows("10:10").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("RatingVitalCombined").Select
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("B2:S2").Select
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
 
Upvote 0
Csummers said:
here is the link to just that file not sure why it was showing both files

https://www.dropbox.com/s/n6fecb5cs6485al/Tool.xlsx?dl=0

here is all the code

Code: 
Sub AutoRerate()
'
' AutoRerate Macro
'

'
    Range("B2:S2").Select
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End Sub
Sub AutoRerate2()
'
' AutoRerate2 Macro
'

'
    Range("B2:S2").Select
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B8:N8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Paste Rerates here First").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets("Total Player").Select
    Rows("10:10").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("RatingVitalCombined").Select
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Sheets("Paste Rerates here First").Select
    Range("A1").Select
End Sub
Sub AutoRerate3()
'
' AutoRerate3 Macro
'

'
    Range("B2:S2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B8:N8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Paste Rerates here First").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Total Player").Select
    Rows("10:10").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("RatingVitalCombined").Select
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Range("B2:S2").Select
    Selection.Copy
    Sheets("Rerates 2016").Select
    Range("B7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
Click to expand...

Sorry if im a noob to posting in this forum but any help with what im doing wrong or how to post to where you can see my file would be much appreciated

I really would like to figure out how to fix my excel issue

Thanks to any help that is given
 
Upvote 0
Csummers,

When I click on the new link in your reply #9 , I get the following:

Google recommends using Chrome????

And, there is a Sign in button to click?????


Maybe someone else on MrExcel will be able to help you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
212
MikeVol
MikeVol
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K
C
  • Question Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
441
myall_blues
myall_blues
brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
378
jolivanes
jolivanes

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top