how to Append selected columns to new work book according to workbook column names

tharusha · Dec 18th 2020

hi i have two excel files i need to append only selected columns to another wordbook sheet how can i do that (column name might be change but the data are same)

**alansidman** · Dec 18th 2020

Cannot manipulate data in a picture. Suggest you upload a sample file and a mocked up solution that we can work with and provide a solution.

tharusha · Dec 18th 2020

Hi alansidman

plz find the two attachment

**royUK** · Dec 18th 2020

It's much easier to provide code like this if a workbook is provided.

This is untested

Code

Sub CopyColumns()
Dim wbTo As Workbook
Dim DestSht As Worksheet
Dim rRng As Range
Dim lRw As Long


Set wbTo = Workbooks("other workbook name.xlsx")
Set DestSht = wbTo.Sheet1
With wbTo.Sheet1
lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End With
Set rRng = ActiveSheet.Range("A1").CurrentRegion
Set rRng = rRng.Offset(1).Resize(rRng.Rows.Count - 1, rRng.Columns.Count)
rRng.Columns(1).Copy DestSht.Cells(lRw, 1)
rRng.Columns(3).Copy DestSht.Cells(lRw, 2)
rRng.Columns(5).Copy DestSht.Cells(lRw, 3)
End Sub

Display More

tharusha · Dec 18th 2020

royUK if i run the code serveral times i need to append them from the last row of the target excel (mainfile.xlsx)

**royUK** · Dec 18th 2020

This line finds the last row

Code

lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

tharusha · Dec 18th 2020

royUK thanks

**royUK** · Dec 18th 2020

Pleased to help.

tharusha · Dec 18th 2020

Code

Set DestSht = wbTo.Sheet1

Hi royUK im facing an error in this line "object not support the property method"
are you able to check it

**royUK** · Dec 18th 2020

What's the name of the destination sheet?

tharusha · Dec 18th 2020

Thanks you i found it dim as worksheet was wrong

**royUK** · Dec 18th 2020

Just noticed it should be

Code

Option Explicit


Sub CopyColumns()
Dim wbTo As Workbook
Dim DestSht As Worksheet
Dim rRng As Range
Dim lRw As Long


Set wbTo = Workbooks("other workbook name.xlsx")
Set DestSht = wbTo.Sheet1
With DestSht
lRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End With
Set rRng = ActiveSheet.Range("A1").CurrentRegion
Set rRng = rRng.Offset(1).Resize(rRng.Rows.Count - 1, rRng.Columns.Count)
rRng.Columns(1).Copy DestSht.Cells(lRw, 1)
rRng.Columns(3).Copy DestSht.Cells(lRw, 2)
rRng.Columns(5).Copy DestSht.Cells(lRw, 3)
End Sub

Display More

tharusha · Dec 18th 2020

royUK error is still my my destination sheet is sheet1

**royUK** · Dec 18th 2020

What is the actual name, I've used Sheet1 below

Code

Set DestSht = wbTo.Sheets("Sheet1")

how to Append selected columns to new work book according to workbook column names

Participate now!

Share

Tags