copy-paste columns on excel VBA

Question

Been trying to copy-paste columns on excel VBA for days, code doesn't work no matter what

Sub CopyRangeofCellsanotherone()
    Dim x As Workbook
    Dim y As Workbook
    Dim LastRow As Long
    Dim LastRowToCopy As Long

    Set x = Workbooks.Open("C:\trabalho da DELL\source.xlsx")
    Set y = Workbooks.Open("C:\trabalho da DELL\destination.xlsx")

    LastRowToCopy = x.Sheets("sourcesheet").Cells(x.Sheets("sourcesheet").Rows.Count, "A").End(xlUp).Row

    x.Sheets("sourcesheet").Range("A" & LastRowToCopy).Copy 'copy from A1 to lastrow

    LastRow = y.Sheets("destsheet").Cells(y.Sheets("destsheet").Rows.Count, "A").End(xlUp).Row 'find the last row

    y.Sheets("destsheet").Range("A" & LastRow).PasteSpecial 'paste on the lastrow of destination + 1 (so next empty row)

    x.Close

End Sub

I think the code is mostly self-explanatory (or atleast, what I intend it to do) but it's not working!! How can I copy contents from column A - source sheet , to column A - destination sheet? And how can I then apply that , to a set of columns? (from A to G for example). I have under 5 hours of VBA under my belt so this might look really simple to some of you... edit: forgot to mention it gives me run-time error 91 on LastRowToCopy line.

Is the name "sourcesheet" correct? Otherwise, to help discover the issue, break that line into two or three statements. Declare a Worksheet reference and point it at x.Sheets("sourcesheet") first. Do the same for destsheet. — Andy G
– Andy G, Commented Dec 15, 2017 at 10:24
(I have amended your topic title, it should be brief and descriptive.) — Andy G
– Andy G, Commented Dec 15, 2017 at 10:26
@AndyG just did the source sheet reference, it pasted one value on the bottom of my destination sheet, which made me realise what the problem might be: i have a finite table on my destination worksheet, instead of just ....cells. Is that what's causing the problem? Because the table isn't long enough for all the values on my source sheet. — Miguel Sá Lemos
– Miguel Sá Lemos, Commented Dec 15, 2017 at 10:44
I don't follow. You say the code now works, as it pastes, but suggest there is a problem. What is this new problem? — Andy G
– Andy G, Commented Dec 15, 2017 at 10:48
it doesn't "work" to the full extent of what it should do: it just pasted one single value of that column , on the bottom of the source sheet. — Miguel Sá Lemos
– Miguel Sá Lemos, Commented Dec 15, 2017 at 10:50

QHarr · Accepted Answer · 2017-12-15 10:46:59Z

3

Try the code below.

Notes on what you have written:

Extend this to fully reference a range not just one cell

 sourceSheet.Range("A1:A" & sourceLastRow)

Add + 1 here to paste to the next available row

destSheet.Range("A" & destLastRow + 1)

Set up variables for the sheet themselves so code is more readable

Set sourceSheet = sourceBk.Sheets("sourcesheet")

Include more of a description with the lastRow variable so you know which workbook and worksheet is being referenced

sourceLastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

And of course, have Option Explicit at the top to check your variable declarations.

Full code:

Option Explicit

Sub CopyRangeofCellsanotherone()

    Dim sourceBk As Workbook
    Dim destBK As Workbook
    Dim sourceLastRow As Long
    Dim destLastRow As Long
    Dim sourceSheet As Worksheet
    Dim destSheet As Worksheet

    Set sourceBk = Workbooks.Open("C:\trabalho da DELL\source.xlsx")
    Set destBK = Workbooks.Open("C:\trabalho da DELL\destination.xlsx")

    Set sourceSheet = sourceBk.Sheets("sourcesheet")
    Set destSheet = destBK.Sheets("destsheet")

    sourceLastRow = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    destLastRow = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row 'find the last row

    sourceSheet.Range("A1:A" & sourceLastRow).Copy 'copy from A1 to lastrow

    destSheet.Range("A" & destLastRow + 1).PasteSpecial 'paste on the lastrow of destination + 1 (so next empty row)

    sourceBk.Close

End Sub

Extending the copy range:

There are a variety of ways to extend this to a range of columns (you will need to explain this further).

For example, if you know in advance the last column e.g. "C", you can amend the line for sourceSheet range as follows:

sourceSheet.Range("A1:C" & sourceLastRow)

If you don't know you can find the last column e.g. as per Ron De Bruin

 sourceLastCol =sourceSheet.Cells(1,sourceSheet.Columns.Count).End(xlToLeft).Column

Syntax for copying source range would then change to

sourceSheet.Range(sourceSheet.Cells(1, 1), sourceSheet.Cells(sourceLastRow, sourceLastCol)).Copy

edited Dec 15, 2017 at 10:46

answered Dec 15, 2017 at 10:33

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Miguel Sá Lemos Over a year ago

Thanks! Haven't tested yet but seems great! Forgot to ask, where can i make it so that it starts pasting on row 2 of the destination sheet, instead of row 1 ? I know i have to add something like StartRow = 2 or something but I forgot :/

Miguel Sá Lemos Over a year ago

Just tested, subscript out-of-range on set destsheet. I don't understand, it exists, it's not hidden, it has the same exact name "destsheet". Is there something i'm missing?

QHarr Over a year ago

check for any white space characters ?

Miguel Sá Lemos Over a year ago

just literally created a new empty "destsheet", nothing ... I'm losing my mind over this ._.

QHarr Over a year ago

Not sure most common issues are wrong workbook or sheet doesn't exist or sheet name has hidden/different characters. Was does ?Activesheet.Name give you in the immediate pane for the worksheet code window associated with destsheet

|

JayyM · Accepted Answer · 2017-12-15 11:08:17Z

2

Sub foo2()
Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("C:\trabalho da DELL\source.xlsx")
Set y = Workbooks.Open("C:\trabalho da DELL\destination.xlsx")

y.Sheets("destsheet").Range("A:A").Value = x.Sheets("sourcesheet").Range("A:A").Value

x.Close

End Sub

Check if the above code copies column A successfully.
If it does, then repeat the line
y.Sheets("destsheet").Range("A:A").Value = x.Sheets("sourcesheet").Range("A:A").Value
replacing Column A with other columns.

answered Dec 15, 2017 at 11:08

JayyM

1651 gold badge4 silver badges13 bronze badges

Collectives™ on Stack Overflow

copy-paste columns on excel VBA

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related