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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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
 
Upvote 0
@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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top