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

 

[smd747]

I tested jbeaucaire it worked fine, until I hide coluums S2:BI to prevent someone from overwriting cells and keep it hidden. When I hide the coluums it only copied S2, when I unhide the coluums it worked fine.

first thank every one for you help, I have spend hours triing to figure this out.

Any thoughts on why only S2 copies when hidden and not S2:BI ?

Thanks again

 

[smd747]

Rotimi

I keep getting run time errors on your code. I tied changing

Range("B1:S2").Select
I changed to this
Range("S2:BI").Select

but still get an error running it.

Thanks

 

[smd747]

jbeaucaire

This revision works until I hide coluums S2:BI so they can not be seen and overwritten. Otherwise it works fine. When I do hide all that copies over is S2 cell the balance are blank, when I unhide again all cells populate.

What do you think? is it because of
.Range("S2", .Range("S2").End(xlToRight)).Copy

should it be "S2:BI" some how?

Thanks

 

[jbeaucaire]

I would never try to do stuff like this on worksheets with "manually" hidden anything, it's just asking for frustration.

Meanwhile, if you know you always want the data from S2:BI2, then you could adjust your code to that exactly.

Maybe even simpler as:

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
    .Range("A" & NR).Resize(, 43).Value = Sheets("DailyUpdateForm").Range("S2:BI2").Value
End With

Range("C2:D2").Select
Application.CutCopyMode = False

End Sub

 

[smd747]

thanks again for your help, that worked. ?normally I would create a user form but this time I had too much information to fit on a user form.
So I created a a spreadsheet with drop downs and calendar pop ups for date entry. This bit of code killed me, I could not get it. thanks again for walking me through.

Normally what I do is hide the coluums and rows in the spreadsheet to limit the scoll area. Unfortunately in excel when you set the scroll area in the VbA property, it losing the settings when you close. I tried the VbA code for the sheet module that i saw on the web but can never get it to work, so as the work around I hide the coluums and rows.

thanks again for all your help