-1

I would like to paste directly below data I already have in a column starting at a specific cell however since there is already data in the column I will need to paste the data starting at cell A7.

Any suggestions?

Dim copyNames1 As Range, pasteNames1 As Range

Set copyNames1 = Workbooks("2019 11 November.xls").Worksheets(3).Columns("F")
Set pasteNames1 = Workbooks("VBA Workbook.xlsm").Worksheets(1).Columns("A").Offset(7, 0)

copyNames1.Copy Destination:=pasteNames1

End Sub

This code isn`t working as it returns an error and messes up the rest of the data by repositioning it.

EDIT: Here is another alternative I have tried:

'   Activate cell directly below pasted data

Range("A1").End(xlDown).Offset(1, 0).Activate

' CR CARDS
'
'   NAMES

Dim copyNamess As Range, pasteNamess As Range

Set copyNamess = Workbooks("2019 11 November.xls").Worksheets(3).Columns("F")
Set pasteNamess = Workbooks("VBA Workbook.xlsm").Worksheets(1).ActiveCell

copyNames1.Copy Destination:=pasteNames1

End Sub
patriciajlim
  • 53
  • 2
  • 3
  • 9
  • 2
    Instead of copying the entire column, [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and only copy the used portion of the column. – BigBen Jan 29 '20 at 18:23
  • Do you want the pasted data to start at Range("A7"), or after the last used row? – GMalc Jan 29 '20 at 18:31
  • The problem is in the destination. The column being copied is always going to be a range of the entire column, however pasting in the other workbook seems to be the issue. I am currently trying xldown and offsetting by 1 row, however .copy destination isn't supporting activecell as the destination. @Bigben – patriciajlim Jan 29 '20 at 18:32
  • 1
    You can't paste an *entire* column into row 7. It won't fit. – BigBen Jan 29 '20 at 18:33
  • @GMalc The last used row, so nth row :) – patriciajlim Jan 29 '20 at 18:33
  • @Bigben That's what I figured...How might I be able to approach this? I am looking for a way to compile a report from different sources where the data needs to fit under the previously pasted data in the column – patriciajlim Jan 29 '20 at 18:34
  • 1
    As I already mentioned, find the last row and only copy the used portion of the column. – BigBen Jan 29 '20 at 18:34
  • @Bigben Could you expand on copying the used portion of the column? - In the destination workbook or from the workbook being copied? The issue is in the pasting workbook – patriciajlim Jan 29 '20 at 18:36
  • Please click on the link I provided in my first comment, thanks. – BigBen Jan 29 '20 at 18:37
  • Thanks. I understand now, since ````activecell```` refers to the cell solely and the copied column is an entire column...however the range I am copying changes with each monthly report and cannot be sure which portion I need to copy every month. Is there a way I can paste the contents of the column copied to the worksheet indefinitely without the script thinking I am trying to paste in just one cell? – patriciajlim Jan 29 '20 at 18:57
  • That linked question should be doubly useful: 1) To find the last used row of the column you are copying from, and 2) To find the last used row of the column you are copying to. – BigBen Jan 29 '20 at 19:06
  • Great! So following copying the last row of the column copying from, is there a function that will copy only the portion of the column with the data? Ps. I am new to VBA, as many can probably guess - appreciate the help LOL – patriciajlim Jan 29 '20 at 19:28
  • What Bigben is saying is that you can't copy a full column containing 1048576 cells and paste into another column starting at row 7, because 1048576 cells will not fit into the available range. You will receive a `Run-time error 1004: Application-defined or object-defined error` So, you will have to define your copy range using last row and then paste to the empty cell after the last row on your paste worksheet. You need to identify the last row for both worksheet columns and incorporate into the macro. – GMalc Jan 29 '20 at 19:39
  • Could you provide some guidance as to what if I need to fill in something before ````.range````? [1]: https://i.stack.imgur.com/LhfE9.png – patriciajlim Jan 29 '20 at 19:56

1 Answers1

0

Try this... I added worksheet variables, used resize and incorporate the last row.

'Define your variables
Dim cpyws As Worksheet, pstws As Worksheet, copyNames1 As Range, pasteNames1 As Range

'Assign your worksheet variables
Set cpyws = Workbooks("2019 11 November.xls").Worksheets(3)
Set pstws = Workbooks("VBA Workbook.xlsm").Worksheets(1)

'Assign your copy/paste range variables
Set copyNames1 = cpyws.Cells(1, 6).Resize(cpyws.Cells(cpyws.Rows.Count, 6).End(xlUp).Row) 'I like to use resize and incorporate the last row
Set pasteNames1 = pstws.Cells(pstws.Rows.Count, "A").End(xlUp).Offset(1) 'Set start cell to paste your copied range to

    'Copy/Paste
    copyNames1.Copy Destination:=pasteNames1
GMalc
  • 2,608
  • 1
  • 9
  • 16