Copy-paste data using specific columns names

Question

I have a very basic knowledge and VBA but due to coding in other programming languages, I have a thought if I can copy-paste the data using specific column names in vba-excel. I use the following code till now but want to get over as I get stuck too often.

ThisWorkbook.Worksheets("Sheets2").Activate
lastrow = Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row + 1
Range("B2:B" & lastrow).Copy Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 2)
Range("A2:A" & lastrow).Copy Worksheets("Sheet1").Range("a65536").End(xlUp).Offset(1, 1)
Application.CutCopyMode = False

This will copy A and B column from Sheet2 to B and C column in Sheet1. Now can I do something like

Sheet2.colname.copy Sheet1.colname.offset

I am just curious and any help is appreciated. Thanks.

The code will copy the data in Sheet2 columns A and B beginning from row 2 to the last not empty row in column A to Sheet1 columns B and C beginning with the row after the last not empty row in column A. It will not simply copy Sheet2!A:B to Sheet1!B:C with some offset as you thought. Btw.: The code seems to be older, since it assumes Sheet1 contains only 65536 rows. — Axel Richter
– Axel Richter, Commented Mar 20, 2015 at 14:02

FreeMan · Accepted Answer · 2015-03-20 14:24:13Z

2

I often declare Const for the columns I'm referring to throughout complex code so I can refer to them by 'name'.

Const ItemNumCol = 1
Const DescCol = 2
Const CostCol = 3
etc...

It's not automatic, but by declaring them globally, it minimizes the pain of rearranging columns, and prevents me from having to go back to my spreadsheet to count every 2 minutes.

Usage Example

Dim ws1 as Worksheet
Dim ws2 as Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheets1")
Set ws2 = ThisWorkbook.Worksheets("Sheets2")
LastRow = ws2.UsedRange.Rows.Count + 1
ws.range(cells(2,DescCol),cells(LastRow,DescCol).copy ws1.range(ws1.UsedRange.Rows.Count + 1, DescCol)

You may have to flip the "Row, Col" references within Cells(), I can never remember which way they go, but IntelliSense in the VBE IDE will tell you.

edited Mar 20, 2015 at 14:24

answered Mar 20, 2015 at 14:06

FreeMan

5,7092 gold badges32 silver badges58 bronze badges

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

1 Comment

Jain Over a year ago

How do you use it then. Like how can I reference it in the above code. Thanks

R3uK · Accepted Answer · 2015-03-20 14:49:16Z

In facts if you want just to use names, you can't do it in general, maybe in tables (select your column and add an array) but not sure...

to use something like this :

Sheet2.colname.copy Sheet1.colname.offset

you can use :

Sheet2.columns(index).copy Sheet1.columns(index).offset

Index is a number starting at 1, and you can find it with a simple For structure and then inject your result in your copies instructions! ;)

Here is a way to do it :

Sub MasterCopy()

    Copy_Columns 0, 0

End Sub

Here is a routine that will help you use the function below (and you can reuse parameters if you want to unite them in only one routine)

Sub Copy_Columns(ByVal HorizontalOffset As Integer, ByVal VerticalOffset As Integer)

    Dim ColCopy As Integer
    Dim ColPaste As Integer


    ColCopy = GetColName("SheetCopy", "ColToCopy")
    ColPaste = GetColName("SheetPaste", "ColToPaste")

    Sheets("SheetCopy").Columns(ColCopy).Copy Sheets("SheetPaste").Columns(ColPaste).Offset(VerticalOffset, HorizontalOffset)

End Sub

And so you can use that function everywhere to find your columns by name in a specific sheet

Public Function GetColName(ByVal SheetName As String, ByVal ColName As String)

        For j = 1 To Sheets(SheetName).Cells(1, 1).End(xlToRight).Column
            If LCase(Sheets(SheetName).Cells(1, j)) <> LCase(Column_Name) Then

            Else
                GetColName = j
                Exit For
            End If
        Next j

            If IsEmpty(GetColName) Then GetColName = 0

End Function

Enjoy! ;)

Or you can be motivated to create a child class of Sheets and try to recreate an index of columns using Names, but I think it'll be painful! ;)

Collectives™ on Stack Overflow

Copy-paste data using specific columns names

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related