Macro - Copy various lines of data to another sheet

Rebeccahelp needed

New Member
Joined
Sep 7, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi,

I am doing a Macro which will copy data from one sheet to another. This is working if I have multiple rows to copy/paste, however if I have only 1 row of data to copy it tries to copy all rows in the worksheet and therefore there is not enough 'space' in the destination worksheet so nothing pastes.

I am using the below to select/copy the data I need:

Range("g2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

and the below to paste (this is working fine as long as I have more than 1 row of data to paste)

Sheets("Returns History").Select

Range("A1").Select
Range("A1").End(xlDown).Offset(1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Thanks all.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Sub Rebecca()
   Dim UsdCols As Long
   UsdCols = Cells(2, Columns.Count).End(xlToLeft).Column
   
   Range("G2", Range("G" & Rows.Count).End(xlUp)).Resize(, UsdCols - 6).Copy
   With Sheets("Returns History")
      .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
   End With
   Application.CutCopyMode = False
End Sub
 

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
399
try this

Public Sub copy()
Dim WBk As Workbook: Set WBk = ThisWorkbook
Dim Sht1 As Worksheet, Sht2 As Worksheet
Dim CopyRng As Range, PasteRng As Range

Set Sht1 = WBk.Worksheets("Sheet1")
Set Sht2 = WBk.Worksheets("Returns History")

LstRw = Sht1.Cells(Sht1.Rows.Count, "G").End(xlUp).Row

Set CopyRng = Sht1.Range("G2:G" & LstRw)
Set PasteRng = Sht2.Range("A2:A" & LstRw)

PasteRng.Value = CopyRng.Value
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
@Dossfm0q
You're code is only copying one column & is pasting to A2 every time, which is not what the OP's code is trying to do.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,111,701
Messages
5,541,300
Members
410,545
Latest member
Upsindustrial20
Top