+ Reply to Thread
Results 1 to 4 of 4

Copy Paste Special Using Column Headers

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-18-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    197

    Copy Paste Special Using Column Headers

    I'm using the following code to copy/paste data from one workbook to another using the header values. It works, but I'm needing it to paste special. Any ideas?


        Dim ws As Worksheet, ws2 As Worksheet, x As Range, i As Long, y As Long
        Set ws = Workbooks("ManagerWorkbook.xlsm").Sheets("CurrentPayroll")
        Set ws2 = Workbooks("Import Education Timesheets.xlsm").Sheets("Import")
        
        With ws
            For i = 1 To .UsedRange.Columns.Count
            Set x = ws2.Rows(5).Find(ws.Cells(1, i).Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not x Is Nothing Then
                    y = .Cells(Rows.Count, i).End(3).Row
                    .Range(.Cells(2, i), .Cells(y, i)).Copy ws2.Cells(6, x.Column)
                End If
            Set x = Nothing
            Next i
        End With

    Thanks!!

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy Paste Special Using Column Headers

    Change
    .Range(.Cells(2, i), .Cells(y, i)).Copy ws2.Cells(6, x.Column)
    INTO


    .Range(.Cells(2, i), .Cells(y, i)).Copy
    ws2.Cells(6, x.Column).PasteSpecial xlValues

  3. #3
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Copy Paste Special Using Column Headers

    Maybe:

    Sub phelbinz()
        Dim ws As Worksheet, ws2 As Worksheet, x As Range, i As Long, y As Long
        Set ws = Workbooks("ManagerWorkbook.xlsm").Sheets("CurrentPayroll")
        Set ws2 = Workbooks("Import Education Timesheets.xlsm").Sheets("Import")
        
        With ws
            For i = 1 To .UsedRange.Columns.Count
            Set x = ws2.Rows(5).Find(ws.Cells(1, i).Value, LookIn:=xlValues, lookat:=xlWhole)
                If Not x Is Nothing Then
                    y = .Cells(Rows.Count, i).End(3).row
                    .Range(.Cells(2, i), .Cells(y, i)).Copy
                    ws2.Cells(6, x.Column).PasteSpecial xlPasteValues
                End If
            Set x = Nothing
            Next i
        End With
    End Sub

  4. #4
    Forum Contributor
    Join Date
    02-18-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    197

    Re: Copy Paste Special Using Column Headers

    It works! Thanks so much for your help.

+ 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. VBA Excel: Copy/Paste a column range to another sheet IF column headers match
    By hwatson86 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-07-2016, 08:05 AM
  2. column E copy/paste special values
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2015, 06:03 PM
  3. copy and paste columns based on column headers
    By patwary786 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-24-2015, 11:43 PM
  4. Replies: 2
    Last Post: 03-18-2015, 05:31 AM
  5. [SOLVED] No experience, special copy and paste column macro
    By shuynh84 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 10-31-2014, 01:10 PM
  6. [SOLVED] Match, Copy and Paste Column Headers - varying number of columns
    By itcher in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2013, 10:49 AM
  7. [SOLVED] Copy Special Paste in the same column
    By iwilli in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2012, 03:38 PM

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