VBA Code Not working Looks Correct Simple Copy Last Cell

[smd747]

I am having problems with my code. I want to select a range in cells S2:BI in worksheet "DailyUpdateForm" and copy data, then go to worksheet "SupervisorUpdateData' and find the last row in column A, then move down 1-cell and paste special values only. Then go back to worksheet "DailyUpdateForm" and select cell C2 as active cell. I have been at this for hours. My code is below. The problem is that it works fine, but as soon as I click in the "SupervisorsUpdateData" sheet" the code next time that it runs it loses orientation because when it ran the first time the whole line would be selected and kept continuing down until I click another cell when exploring data. What am I doing wrong?? Any help would be greatly appreciated.


Sub UpdateSupervisorsData()
'
' UpdateSupervisorsData Macro
' Update Supervisors Data Table
'
' Keyboard Shortcut: Ctrl+u
'
Range("S2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("SupervisorUpdateData").Select
Sheets("SupervisorUpdateData").Name = "SupervisorUpdateData"
ActiveCell.Offset(-2, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DailyUpdateForm").Select
Range("C2:D2").Select
Application.CutCopyMode = False
End Sub

 

[jbeaucaire]

Maybe this, we take out all the "selecting" and "activating", address the sheets directly, then your macro won't get lost.

Option Explicit

Sub UpdateSupervisorsData()
'
' UpdateSupervisorsData Macro
' Update Supervisors Data Table
'
' Keyboard Shortcut: Ctrl+u
'
With Sheets("DailyUpdateForm")
    .Range("S2", .Range("S2").End(xlToRight)).Copy
    Sheets("SupervisorUpdateData").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
    .Range("C2:D2").Select
End With

Application.CutCopyMode = False
End Sub

 

[Akinrotimi]

smd747,

The code below would solve your problem:

Option Explicit
Sub UpdateSupervisorsData()
'
' UpdateSupervisorsData Macro
' Update Supervisors Data Table
'
' Keyboard Shortcut: Ctrl+u
' akinrotimi, 14/08/2011
http://www.mrexcel.com/forum/newreply.php?do=newreply&noquote=1&p=2827648
Application.ScreenUpdating = False
Sheets("DailyUpdateForm").Select
Range("B1:S2").Select
Selection.Copy
Sheets("SupervisorUpdateData").Select
Range("a1000000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
selection.copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Sheets("DailyUpdateForm").Select
Range("c2").Select
Application.CutCopyMode = False
Application.ScreenUpdating = True
end sub

Regards

Rotimi

 

[smd747]

Thanks for the quick response jbeaucaire. It worked, it fixed the problem.
I even moved the curser and ran the code and it worked flawlessly.

Thanks again

 

[smd747]

It worked for about 10 times then I get a run time error on this line

Sheets("SupervisorUpdateData").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues

I do not get it. Is there a way to attach or send a example of file?

It strange

Thanks

 

[smd747]

I tired the second solution from Rotimi

I get the erroe code on this line
Range("a1000000").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

This doe not work either.

Any thoughts, i though the first solution was the fix but it stoped working

 

[Akinrotimi]

Hi,

It is working perfectly here.

What version of excel are you using.I set the code up on MS Excel 2007.Modify the code : Range("a1000000").End(xlUp).Offset(1, 0).Select

to Range("a65536").End(xlUp).Offset(1, 0).Select

It seems you are on MS Excel 2003 with 65536 rows.

Modify the code and retry.It would work well.

Regards

Rotimi

 

[smd747]

I am using excel version 2007. can I attach a sample on this post to show you?

 

[jbeaucaire]

Try this:

Option Explicit

Sub UpdateSupervisorsData()
Dim NR As Long
'
' UpdateSupervisorsData Macro
' Update Supervisors Data Table
'
' Keyboard Shortcut: Ctrl+u
'
With Sheets("SupervisorUpdateData")
    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1
End With

With Sheets("DailyUpdateForm")
    .Range("S2", .Range("S2").End(xlToRight)).Copy
    Sheets("SupervisorUpdateData").Range("A" & NR).PasteSpecial xlPasteValues
    .Range("C2:D2").Select
End With

Application.CutCopyMode = False
End Sub

 

[Akinrotimi]

Hi,

Still working perfectly here and at a loss as to what the problem could be.Can someone else test it and help explain what is happening?

Regards

Rotimi