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

dimsums

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,403
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi,
I want to paste it starting from Column C
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,403
Office Version
  1. 365
Platform
  1. Windows
In that case which cells do you want to copy, as you cannot copy the entire row?
 

dimsums

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,403
Office Version
  1. 365
Platform
  1. Windows
This assumes only one row will be selected
VBA Code:
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

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,403
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

dimsums

New Member
Joined
Jun 29, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,403
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,472
Messages
5,548,228
Members
410,824
Latest member
Bobmn4
Top