+ Reply to Thread
Results 1 to 2 of 2

VBA to Copy certain columns of data and paste into a new sheet

Hybrid View

  1. #1
    Registered User
    Join Date
    02-23-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    50

    VBA to Copy certain columns of data and paste into a new sheet

    Hi

    I have a worksheet which contains basic data such as Name, ID number, etc in columns A-U. Then from column V onwards I have data pertaining to months and variances, i.e. column V is titled: JAN-13, W is titled: Adjustment and X is titled: Dfference, this is then repeated across the columns with only the month changing i.e. Y is FEB-13, Z is Adjustment, AB is Difference etc. I am hoping that someone will help provide me code to copy all data from columns A-U and then data from the columns titled "Months" only, so copy A:U aswell as column V, Y etc. The data is updated every month, so the number of rows also varies.

    I have the following code so far, which works, but is there a way I can refer to column heading i.e. "Jan-13" and ask it to select all popolated rows in the column instead of inserting all the ranges, as currently I have to update them everytime the rows# or columns change. Also, in the below code the copied column titles format as number rather than date.

    Sub CopyPasteValue()
        Sheets.Add().Name = "Template"
        Sheets("Original").Range("A1:V3657,Y1:Y3657,AB1:AB3657,AE1:AE3657,AH1:AH3657,AK1:AK3657,AN1:AN3657,AQ1:AQ3657,AT1:AT3657").Copy
        Sheets("Template").Range("A1").PasteSpecial _
        Paste:=xlPasteValues
        
    End Sub
    Thanks in advance
    Jen
    Last edited by tiger01; 02-06-2013 at 02:49 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: VBA to Copy certain columns of data and paste into a new sheet

    Yes, you could use WorksheetFunction.Match, have a play with match on the worksheet, and the same arguments will apply in VBA, you could then say assuming month headers are in row 1

    Dim rng as Excel.Range
    dim intColumn as integer
    intColumn = WorksheetFunction.Match("Jan 2013",range("1:1"),0)
    set rng = range(column found in match & ":" & (column found in match+2)
    rng.copy

    etc

    I havent done this in VBA, so may need tweaking, i wrote the above in notepad.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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