VBA macro to copy/paste data from sheet to another

[banneduser123]

so let's say I have to worksheets, Sheet1 and Sheet2.

I would like to search for a column header in Sheet2, and if found, copy all the data in that column (except for the header) and paste it into a particular column in Sheet1 beginning from row 2 (to account for the header).

I would then want to do this a couple times over

any help would be appreciated

 

[JLGWhiz]

Sub t()
Dim col As String, col2 As String, fn As Range
col1 = InputBox("Enter the header of column to copy. ", "COLUMN TO COPY")
col2 = InputBox("Enter the alpha letter for the destination column on sheet 1.", "DESTINATION COLUMN")
Set fn = Sheets(2).Range("1:1").Find(col1, , xlValues)
    If Not fn Is Nothing Then
        Intersect(Sheets(2).UsedRange.Offset(1), fn.EntireColumn).Copy Sheets(1).Range(col2 & 2)
    End If
End Sub

 

[banneduser123]

thanks for the help JLG.

how would I amend this code so that I could copy/paste additional columns?

 

[JLGWhiz]

thanks for the help JLG.

how would I amend this code so that I could copy/paste additional columns?

Explain 'additional columns'

 

[banneduser123]

so after it search and copy/pastes the first column, I want to be able to look for another particular column and copy/paste that, and repeat the process a number of times...was that clear?

 

[JLGWhiz]

Then just run the macro again and change the parameters in the input boxes. The macro does not hold the values you place in those pop-up boxes once the macro completes. The values have to be entered each time the macro runs.

Just a side note: It makes working with data a lot easier if worksheets are designed in consistent formats, i.e. the same headers in the same columns and rows between sheets. That way, you don't have to search for headers, you can just use the regular column and row references of the worksheets to locate and manipulate data.

 

[banneduser123]

that wouldn't be efficient to keep running the macro and changing parameters....there's gotta be a better macro to write

the task is fairly simple, there are two files - one file has 10 columns of data, the second file has 5 columns of pre-assigned fields...
I have to take the data from the 1st file and paste it into the proper column in the 2nd file.

having consistent formats between the two files isn't an option...that's why I was trying to write macro

 

[JLGWhiz]

that wouldn't be efficient to keep running the macro and changing parameters....there's gotta be a better macro to write

the task is fairly simple, there are two files - one file has 10 columns of data, the second file has 5 columns of pre-assigned fields...
I have to take the data from the 1st file and paste it into the proper column in the 2nd file.

having consistent formats between the two files isn't an option...that's why I was trying to write macro

Based on the criteria posted thus far, I don't know what else to offer. Computers are really dumb animals with a capability to prodcess data rapidly if given the right logical instructions. They cannot second guess the intent of the user and neither can the programmer in most cases. I understand why you need to use the 'Find' mentod to locate your columns to copy, but unless you have a specific repetitive process that you follow in copying, eg. a consistent order in which the headers will be searched, then the inputbox is the best method to handle variations in copying requirements. If you do have a consistent order of headers which columns are repetively copied, They they can be placed in an array and the Find and Copy porcess can be done in a loop. But that type of information was not furnished in any of the above posts. The destination to paste the copied columns to is also needed for each column to be copied.

 

[JLGWhiz]

If the headers in the destination (paste) sheet are the same as the headers in the source (copy) sheet, then a list of those headers would be all that is needed to writh a macro that would copy and paste your columns. Actually, I would only need the cell references (A1, B1, etcc.) for the destination headers, if they are identical to the source headers.