+ Reply to Thread
Results 1 to 2 of 2

Find column name and copy used range under header

Hybrid View

  1. #1
    Forum Contributor Pierce Quality's Avatar
    Join Date
    07-29-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    221

    Find column name and copy used range under header

    Hi All,

    I'm attempting to copy data from one work book to another. The column names in the header (Row 1) that have the data I want will always be the same, but the column order will be variable. There are many columns in the data sheet but I'm only looking for a few columns out of many.
    What I have so far is this:

    Sub Test
    
    Dim ws As Worksheet
        
     Exam1 = Application.Match("Example 1", Rows("1:1"), 0)
     Exam2 = Application.Match("Example 2", Rows("1:1"), 0)
     Exam3 = Application.Match("Example 3", Rows("1:1"), 0)
          
     ws.Columns(Exam1).Copy _
     Destination:=ThisWorkbook.Worksheets("Sheet1").Range("A2")
     ws.Columns(Exam2).Copy _
     Destination:=ThisWorkbook.Worksheets("Sheet1").Range("B2")
     ws.Columns(Exam3).Copy _
     Destination:=ThisWorkbook.Worksheets("Sheet1").Range("C2")
        
    End Sub
    Which will copy the entire column, but I already have specific headers and formatting in the destination workbook, so I only want the variable data in each column to be copied.

    What I would normally use copy data out of a column is this:

    ws.Range("B2", Range("B" & Rows.count).End(xlUp)).Copy _
    ThisWorkbook.Worksheets("Sheet1")Range("A" & Rows.count).End(xlUp).Offset(1)
    Which is works great when I know which order the columns will be in (Column B for example), which is not the casecurrently.

    Any help would be greatly appreciated, thanks!

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Find column name and copy used range under header

    You could add this function I found into your module.
    Notice it's looking for iCol as an Integer. If your Exam numbers are declared as Long then you would need to either change them to integer type or iCol in the function to Long if that's what your Exam numbers are declared as.
    Function ConvertToLetter(iCol As Integer) As String
       Dim iAlpha As Integer
       Dim iRemainder As Integer
       iAlpha = Int(iCol / 27)
       iRemainder = iCol - (iAlpha * 26)
       If iAlpha > 0 Then
          ConvertToLetter = Chr(iAlpha + 64)
       End If
       If iRemainder > 0 Then
          ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
       End If
    End Function
    You could then use it by having something like this in your code.
    Dim colLetter As String
    colLetter = ConvertToLetter(Exam1)
    ws.Range(colLetter & 2, ws.Range(colLetter & Rows.Count).End(xlUp)).Copy

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find/copy/paste follow header of column
    By lamvienthien in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-13-2015, 02:31 AM
  2. [SOLVED] find header value in column, return cell next to it for copy/paste
    By julzhart in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-15-2014, 10:29 AM
  3. [SOLVED] Based on Column Header find and copy data from one sheet to another
    By Sachy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-31-2014, 10:00 AM
  4. Sort Select Range: Either Find Column Header or Last Column:Last Row
    By mbryson in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-02-2014, 08:03 AM
  5. Replies: 1
    Last Post: 10-05-2013, 03:35 PM
  6. .Find header and column in another workbook and copy the cell that matches both
    By Ariadust in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-06-2012, 12:15 AM
  7. Find last numerical value in row range and return the column header
    By Fidd$ in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-04-2011, 10:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1