Using Selection.Copy to transfer data to another sheet, starting from column C

[dimsums]

Hi all,
This might be a silly question but i am new at VBA so can't seem to figure out what the error in my code it.
I have two sheets, sht1 and sht2, from which a user is to highlight the row to transfer sht2 from the next empty row and starting from column C. My code is as follows

Sub NewMove()
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")

Selection.EntireRow.Select
Selection.Copy

sht2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValues

End Sub

Could someone please help me figure out where i am going wrong. Thank you

 

[Fluff]

You are trying to paste an entire row, starting in col D which means it will run of the edge of the sheet.
Do you want to paste into col A?

 

[dimsums]

Hi,
I want to paste it starting from Column C

 

[Fluff]

In that case which cells do you want to copy, as you cannot copy the entire row?

 

[dimsums]

From sht1, I want to be able to copy a row from Range A to Range J, the specific cells would be highlighted by the user, so it can be A2:J2 or A4:J4, this will be dynamic.
The selected range will then be pasted into another sheet starting from Column C, as Column A and B have ID numbers and are prefilled

 

[Fluff]

This assumes only one row will be selected

Sub NewMove()
Dim sht1 As Worksheet
Dim sht2 As Worksheet

Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("Sheet2")

Range("A" & Selection.Row).Resize(, 10).Copy

sht2.Cells(Rows.Count, 3).End(xlUp).Offset(1).PasteSpecial xlPasteValues

End Sub

 

[dimsums]

It Works!
Thank you so much! I see that you have used Resize which is new to me.
Thanks once again

 

[Fluff]

You're welcome & thanks for the feedback.

 

[dimsums]

Hi Fluff,
Really sorry to disturb you again, my manager wants the transfer to happen in a another workbook in a different location. I tried changing the code accordingly but the other workbook is not updating its links.

The code is as below
Sub NewMove1()
Dim sht1 As Worksheet
Dim wb1 As Workbook
Dim sht2 As Worksheet


Set wb1 = Workbooks.Open("T:\ROC-IT PROGAM\OFI Management\OFI Register.xlsm", UpdateLinks:=0)
Application.AskToUpdateLinks = False
Set sht1 = Sheets("Sheet1")
Set sht2 = wb1.Sheets("ALL OFIs")

Range("A" & Selection.Row).Resize(, 10).Copy

sht2.Cells(Rows.Count, 3).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Application.CutCopyMode = False
wb1.Close savechanges:=True
Application.ScreenUpdating = True

End Sub


Could you please see what the issue might be?

 

[Fluff]

How about

Sub NewMove1()
Dim sht1 As Worksheet
Dim wb1 As Workbook
Dim sht2 As Worksheet
Dim Rng As Range

Set Rng = Range("A" & Selection.Row).Resize(, 10)

Set wb1 = Workbooks.Open("T:\ROC-IT PROGAM\OFI Management\OFI Register.xlsm", UpdateLinks:=0)
Application.AskToUpdateLinks = False
Set sht1 = Sheets("Sheet1")
Set sht2 = wb1.Sheets("ALL OFIs")


sht2.Cells(Rows.Count, 3).End(xlUp).Offset(1).Resize(, 10).Value = Rng.Value

Application.CutCopyMode = False
wb1.Close savechanges:=True
Application.ScreenUpdating = True

End Sub