• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy data under specific columns in sheet1 n paste to sheet2 under same headers

ShyamExcel

New Member
I'm pretty new to VBA and I'm trying to automate a few things and the task at hand is like this:


Two sheets in my workbook 'marketing.xlsm'. Sheet1 is the source sheet containing between 5 and 15 columns at different instances but they will always contain the 4 columns alpha, beta, gamma, and theta in no particular order but always on row 2.


Sheet2 is my destination sheet which will have only the 4 headers on row 2 for the four columns - alpha, beta, gamma and theta. Only headers on row 2. No data under them.


What I want to do is create a VBA code to look up each of header values in row 2 of sheet2, search sheet1 in row 2 for the cell text (say, alpha), copy the data from the corresponding column and paste the data without header into sheet 2. So in effect, we would have copied data underneath column 'alpha' in sheet1 into column 'alpha' in sheet2. (if alpha column was in B2 in sheet1 and had 100 data points, we would have copied B3:B103 into sheet 2 under column 'alpha'.


Alternative source:

Since the data for sheet1 comes from a web report as CSV, I'm also thinking, instead of importing the data into sheet1, if the VBA code can look up the file named 'data.csv' and sheet1 in it, extract the data and paste under the 4 columns listed in sheet2 of my destination workbook (marketing.xlsm) directly, that would be even better.


The critical points are

1. All column headers in sheet1 can occur anywhere in row 2, no fixed position. So alpha can be at C2, D2, or G2 or such.

2. The number of data points under each column can vary too (10 to 10000) but all columns will have the same number of data points as its a table.


Appreciate your suggestions and any assistance you can provide!


Shyam
 

SirJB7

Excel Rōnin
Hi, ShyamExcel!


If you want to consider a formula only option, you can type this in Sheet2!A3, and copy right and down as needed:

=SI(DESREF(Hoja1!$A$2;FILA()-2;COINCIDIR(C$2;Hoja1!$2:$2;0)-1)="";"";DESREF(Hoja1!$A$2;FILA()-2;COINCIDIR(C$2;Hoja1!$2:$2;0)-1)) -----> in english: =IF(OFFSET(Sheet1!$A$2,ROW()-2,MATCH(C$2,Sheet1!$2:$2,0)-1)="","",OFFSET(Sheet1!$A$2,ROW()-2,MATCH(C$2,Sheet1!$2:$2,0)-1)).

The only disadvantage is that you have to copy down the formulas to the maximum target area, but the pro of no-VBA/macro is a strong point, I guess. And I love VBA.


If you don't want or can't establish this area, you might either:


a) create a Sub procedure in any module with this code, and manually ran the macro or assign it to a button control (or command button)

-----

[pre]
Code:
Sub Anonymous()
' constants
' declarations
Dim I As Integer, J As Integer
' start
Sheets("Sheet2").Select
' process
'  loop
For I = 1 To 4
' clear
Cells(3, I).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
' point
For J = 1 To 1000 'update if needed
If Cells(2, I).Value = Worksheets("Sheet1").Cells(2, J).Value Then
Exit For
End If
Next J
' check if copy
If J <= 1000 Then
Sheets("Sheet1").Select
Cells(3, J).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Cells(3, I).Select
ActiveSheet.Paste
End If
Next I
'  deselect
Application.CutCopyMode = False
' end
Range("A3").Select
End Sub
[/pre]
-----


b) or define a Worksheet_Activate event for sheet Sheet2 and then call previous code from there


Hope it aids you.


Regards!
 

ShyamExcel

New Member
I now see the power of VBA and I'm going to allocate some time to learn. Any recommendations on how to learn fast? or study materials online?
 

SirJB7

Excel Rōnin
Hi, ShyamExcel!

One good and fast way to learn something programming-related is doing a course (on-line or presential, it depends on your location), having additional bibliography to consult (there are plenty of books or even google the web), and at last but not least -and I would say primarily- practicing what learned every day applying it to a project on which one can works without any job/school pressure.

Being chandoo.org a website where this tools (courses, bibliography) are provided, I think you should address yourself to http://chandoo.org/wp/contact/ or http://chandoo.org/wp/excel-school/ or http://chandoo.org/wp/excel-school/faqs/.

If nothing suitable found, well, then come back and I'll try to lead you towards other paths, but firstly it's ethical to walk thru these first.

Regards!
 

anjanbose

New Member
Code:
Dim i As Integer
Dim col As Integer
i = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To i
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'-----------------1
col = Application.Match("Defect ID", Sheets("Details1").Rows(1), 0)
Sheets("Details1").Application.Cells(1, col).Offset(1).Resize(i).Copy _
Sheets("Details").Range("a2")


col = Application.Match("Assigned To", Sheets("Details1").Rows(1), 0)
Sheets("Details1").Application.Cells(1, col).Offset(1).Resize(i).Copy _
Sheets("Details").Range("c2")
This code is taking lots of time while copying a column and pasting it to another sheet under specific column. pls help
 
Last edited by a moderator:

vletm

Excel Ninja
anjanbose
If Your above 'comment' has not connection with original thread which is almost eight years old,
You should open a new thread with needed information.
 
Top