I am trying to copy and paste data from a range of columns VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2023755%22%20slang%3D%22en-US%22%3EI%20am%20trying%20to%20copy%20and%20paste%20data%20from%20a%20range%20of%20columns%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023755%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20so%20I%20am%20new%20to%20VBA%20and%20am%20struggling%20to%20perform%20a%20particular%20task.%20I%20have%20performed%20some%20machine%20learning%20on%20some%20stock%20data%20and%20have%20transferred%20them%20to%20excel.%20But%20the%20thing%20is%20this%20data%20can%20be%20buggy%20and%20sometimes%20not%20load.%20So%20I%20want%20to%20copy%20and%20paste%20the%20data%20onto%20another%20sheet%20in%20the%20same%20workbook.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20avoid%20certain%20columns%20and%20only%20copy%20others%20only%20due%20to%20the%20nature%20of%20feature%20engineering%20when%20I%20am%20building%20my%20machine%20learning%20model%2C%20I%20don't%20quite%20know%20which%20columns.%20But%20I%20am%20certain%20of%20two%20columns%20that%20will%20always%20be%20present.%201.%20the%20Date%2C%20(second%20to%20last%20column)%202.%20%22Fold%22%20(the%20last%20column).%20These%20headers%20will%20always%20be%20in%20place.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProblem%3C%2FP%3E%3CP%3EHow%20do%20I%20copy%20and%20paste%20the%20data%20from%20a%20range%20of%20sheets%20starting%20at%20date%20which%20will%20be%20in%20the%20second%20to%20last%20column%20on%20the%20right-hand%20side%3F%20And%20avoid%20the%20three%20columns%20before%20the%20last%20column%20(which%20is%20the%20Fold)%20last%20column%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20what%20I%20have%20so%20far%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-vbnet%22%3E%3CCODE%3ESub%20CopyColumnsSource()%0A'%20Define%20a%20variable%20called%20last%20row%20and%20the%20end%20row%0ADim%20lastrow%20As%20Long%2C%20erow%20As%20Long%0A%0A'%20Define%20the%20column%20headers%20that%20I%20want%20to%20copy%20by%20name%2C%20the%20first%20array%20and%20the%20second%20one%20I%20want%20to%20use%20as%20a%20meter%20to%20avoid%20the%20three%20columns%20before%20it%20(Date%20and%20Fold%20respectively)%0ADim%20ColumnHeaderArr(0%20To%201)%20As%20String%0AColumnHeaderArr(0)%20%3D%20%22Date%22%0AColumnHeaderArr(1)%20%3D%20%22Fold%22%0A%0AIf%20VerifyHeaders(ColumnHeaderArr)%20%3D%20True%20Then%0A%0A'%20find%20the%20last%20row%20in%20the%20source%20sheet%0Alastrow%20%3D%20Sheet1.Cells(Rows.Count%2C%201).End(xlUp).Row%0A'%20We%20start%20with%20the%20first%20row%20because%20the%20first%20row%20will%20be%20headers%0AFor%20i%20%3D%201%20To%20lastrow%0A'%20Copy%20data%20from%20the%20first%20row%20of%20sheet%201%0ASheet1.Cells(i%2C%200).Copy%0A'%20Go%20to%20sheet%202%2C%20look%20for%20the%20last%20row%20that%20is%20used%20then%20go%20to%20the%20last%20row%20below%20it%0Aerow%20%3D%20Sheet2.Cells(Rows.Count%2C%201).End(xlUp).Offset(1%2C%200).Row%0A'%20Copy%20data%20from%20the%20first%20column%20to%20the%20the%20end%20of%20the%20column%201%20but%20not%20column%201%20and%20skip%20the%20second%20column%20before%20it%0A'%20in%20sheet%201%20and%20paste%20it%20in%20the%20second%20column%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2023755%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2023791%22%20slang%3D%22en-US%22%3ERe%3A%20I%20am%20trying%20to%20copy%20and%20paste%20data%20from%20a%20range%20of%20columns%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2023791%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914722%22%20target%3D%22_blank%22%3E%40Desno13%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20have%20a%20defined%20function%20called%20VerifyHeaders%20in%20one%20of%20the%20modules%3F%20At%20a%20first%20glance%2C%20the%20VBA%20is%20a%20bit%20overcomplicated%20for%20a%20simple%20Copy%20and%20Paste%20function.%20I'm%20a%20bit%20confused%20by%20your%20last%20two%20notes%20in%20the%20macro%2C%20is%20there%20any%20data%20appending%3F%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-vbnet%22%3E%3CCODE%3E'%20Go%20to%20sheet%202%2C%20look%20for%20the%20last%20row%20that%20is%20used%20then%20go%20to%20the%20last%20row%20below%20it%0A%0A'%20Copy%20data%20from%20the%20first%20column%20to%20the%20the%20end%20of%20the%20column%201%20but%20not%20column%201%20and%20skip%20the%20second%20column%20before%20it%20in%20sheet%201%20and%20paste%20it%20in%20the%20second%20column%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegarding%20the%20column%20order%2C%20will%20the%20Date%20and%20Fold%20columns%20always%20be%20in%20the%20same%20place%20and%2For%20retain%20the%20header%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2024178%22%20slang%3D%22en-US%22%3ERe%3A%20I%20am%20trying%20to%20copy%20and%20paste%20data%20from%20a%20range%20of%20columns%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914722%22%20target%3D%22_blank%22%3E%40Desno13%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20is%20along%20the%20lines%20of%20what%20you're%20looking%20for%20(attached)%3F%20I'm%20assuming%20the%20headers%20are%20unique%20(if%20not%2C%20it%20can%20be%20modified).%20It%20sounds%20like%20you%20may%20want%20to%20add%20additional%20fields%20later%20on%2C%20so%20you%20should%20be%20able%20to%20just%20add%20the%20header%20captions%20to%20the%20headers%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2024874%22%20slang%3D%22en-US%22%3ERe%3A%20I%20am%20trying%20to%20copy%20and%20paste%20data%20from%20a%20range%20of%20columns%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024874%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20this.%20So%20yes%20this%20is%20what%20I%20want%20but%20with%20some%20adjustments.%20I%20have%20included%20the%20comments%20in%20the%20excel%20sheet%20and%20also%20a%20sample%20of%20my%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20thank%20you%20for%20this!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2031707%22%20slang%3D%22en-US%22%3ERe%3A%20I%20am%20trying%20to%20copy%20and%20paste%20data%20from%20a%20range%20of%20columns%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2031707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F914722%22%20target%3D%22_blank%22%3E%40Desno13%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20it%20took%20me%20a%20bit%20to%20get%20back%20to%20you.%20I%20think%20I%20understand%20better%20now%20what%20you%20want%20to%20do%20-%20the%20columns%20you%20want%20to%20copy%20are%20entirely%20based%20on%20position%20(relative%20to%20the%20date%20column).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20(Module%201).%20But%2C%20note%20that%20I'm%20making%20some%20assumptions%20about%20which%20row%20the%20%22Date%22%20header%20will%20appear%20in%20(I'm%20assuming%20Row%202%2C%20but%20change%20if%20necessary).%20Also%2C%20I'm%20assuming%20the%20macro%20should%20run%20on%20the%20active%20sheet%20and%20is%20copying%20the%20data%20to%20a%20new%20worksheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello, so I am new to VBA and am struggling to perform a particular task. I have performed some machine learning on some stock data and have transferred them to excel. But the thing is this data can be buggy and sometimes not load. So I want to copy and paste the data onto another sheet in the same workbook. 

 

I want to avoid certain columns and only copy others only due to the nature of feature engineering when I am building my machine learning model, I don't quite know which columns. But I am certain of two columns that will always be present. 1. the Date, (second to last column) 2. "Fold" (the last column). These headers will always be in place. 

 

Problem

How do I copy and paste the data from a range of sheets starting at date which will be in the second to last column on the right-hand side? And avoid the three columns before the last column (which is the Fold) last column

 

This is what I have so far

 

Sub CopyColumnsSource()
' Define a variable called last row and the end row
Dim lastrow As Long, erow As Long

' Define the column headers that I want to copy by name, the first array and the second one I want to use as a meter to avoid the three columns before it (Date and Fold respectively)
Dim ColumnHeaderArr(0 To 1) As String
ColumnHeaderArr(0) = "Date"
ColumnHeaderArr(1) = "Fold"

If VerifyHeaders(ColumnHeaderArr) = True Then

' find the last row in the source sheet
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
' We start with the first row because the first row will be headers
For i = 1 To lastrow
' Copy data from the first row of sheet 1
Sheet1.Cells(i, 0).Copy
' Go to sheet 2, look for the last row that is used then go to the last row below it
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
' Copy data from the first column to the the end of the column 1 but not column 1 and skip the second column before it
' in sheet 1 and paste it in the second column

 

5 Replies

@Desno13 

Do you have a defined function called VerifyHeaders in one of the modules? At a first glance, the VBA is a bit overcomplicated for a simple Copy and Paste function. I'm a bit confused by your last two notes in the macro, is there any data appending?:

 

' Go to sheet 2, look for the last row that is used then go to the last row below it

' Copy data from the first column to the the end of the column 1 but not column 1 and skip the second column before it in sheet 1 and paste it in the second column

 

Regarding the column order, will the Date and Fold columns always be in the same place and/or retain the header? 

@adversi Thanks for responding. 

 

First q - So with that, I just wanted to confirm their presence before running the script. I got this idea from a youtube video - she saved the column names as an array so I followed through on it.

 

Second q - yes, they will always be in the same position. The last two columns will always be Date and Fold will always be the last two columns in the data and will always have the same header title "Date" and "Fold". 

@Desno13 

 

Maybe this is along the lines of what you're looking for (attached)? I'm assuming the headers are unique (if not, it can be modified). It sounds like you may want to add additional fields later on, so you should be able to just add the header captions to the headers array.

 

@JMB17 

 

Thanks for this. So yes this is what I want but with some adjustments. I have included the comments in the excel sheet and also a sample of my data. 

 

But thank you for this!

 

 

@Desno13 

 

Sorry it took me a bit to get back to you. I think I understand better now what you want to do - the columns you want to copy are entirely based on position (relative to the date column).

 

See attached (Module 1). But, note that I'm making some assumptions about which row the "Date" header will appear in (I'm assuming Row 2, but change if necessary). Also, I'm assuming the macro should run on the active sheet and is copying the data to a new worksheet.