Copy selected cells from sheet 1 and paste as values to last row sheet 2

[benntw]

I have two sheets of information. Sheet 1 is a excel schedule. Sheet 2 is where our guys can put the initial scope of work on. There is a macro that copies & pastes as values on sheet 1 for the initial scope. I am trying to create another macro that they can use when they get added scope of work. I am having no luck. Both sheets are protected. Here is the code I am using to unprotect and protect my sheet.

Dim cell As Range
Dim myPassword As String
myPassword = "xxxxxxxxxx"

my code

ActiveSheet.Protect _
Password:=(myPassword), _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowFormattingCells:=True


What I want to do is select the row(s) that has a new entry and copy the range i:k and paste it as values on sheet one last row in column i . What I was trying to research was if there was a way to use a message box asking which row numbers I wanted to copy to sheet 1. Appreicate the help with this one.

 

[My Aswer Is This]

If I understand you correctly you want a Input Box to pop up asking you what row to copy from Sheet(1) to Sheet (2). Is that correct? Can we just copy the entire row or just Range("I:K") of that row. And you only want to copy and paste just the values.
And the new row will be appended to sheet(2)

 

[benntw]

Correct. I was looking for a message box asking for row number or numbers. Only to copy the range ("I:K") and paste as values. Sheet one has multiple columns with conditional formatting, & formulas. That is the reason can't copy the entire row from sheet 2 and paste as values on sheet 1.

 

[My Aswer Is This]

I'm a little confused because for Subject title says:
" Copy selected cells from sheet 1 and paste as values to last row sheet 2

But then in your comment section you say paste into Sheet(1)

So look at the script and modify to your needs

Select the range of data you want to copy and then run the script it will copy to Sheet(2) where you said

Sub Test()
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets(2).Cells(Rows.Count, "I").End(xlUp).Row + 1
Selection.Copy: Sheets(2).Range("I" & Lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[benntw]

Sorry about that. I did make that very confusing. Too much work on the brain when I wrote it. Here is a re-write of my scenario.

I built a spreadsheet with certain fields protected on Sheet 1 & Sheet 2. Both sheets have different areas to be filled out. There are 3 columns from Sheet 1 that match Sheet 2.

Sheet 2 from Columns C:E is unlocked to add new entries.
Sheet 1 from Columns I:K is where the values go. That part of the sheet is locked from editing. Sheet 1 is the destination sheet.

What I am trying to do is copy C:E from Sheet 2 and paste as values on sheet 1 from I:K.
I am not good at writing VBA , but still learning. My idea was to be able to either highlight new entries on sheet 2 or have a message box to type in what row numbers have new entries and take the entries in C:E and paste them as values on the last row on sheet 1 starting in Column " I ".

The reason I cannot copy and insert the rows from sheet 2 to sheet 1 is the only columns that have matching values are the ones I listed above. I have multiple formulas and conditional formatting from Column A:BK on sheet 1. Appreciate the responses & help.

 

[My Aswer Is This]

I really do not need to know about what cells are locked or not locked.
At the beginning of the script you should enter the code needed to unlock the sheet.
And the way I wrote the script you can select any area you want and the script will copy those values and paste them into column "I" of sheet(2). It gets more complicated when you want a inputbox to popup and then you enter the range you want copied. And your original ideal was to select the range not use a Input Box.

So tell me what did my script do wrong. Did you use it and what did it do wrong.

 

[benntw]

It did work perfectly and I really appreciate it. I was just trying to get fancier. Thank you for you help.

 

[My Aswer Is This]

Communications is always the problem with helping people.

See now in your second post you said:

"What I am trying to do is copy C:E from Sheet 2 and paste as values on sheet 1 from I:K.

Now if that is what you wanted it would not require a InputBox.

This is a static request. Every time copy the same range to the same range.

But You did not say that in your first post.

If that is what you want that can be done without a Input Box or any selecting.

Try using this

Sub CopyRange()
Application.ScreenUpdating = False
Sheets(2).Range("C:E").Copy
Sheets(1).Range("I:K").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub