Results 1 to 8 of 8

VBA to Copy Columns Using Headers but Ignore Columns without Data

Threaded View

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

    VBA to Copy Columns Using Headers but Ignore Columns without Data

    Hi!

    I use the following code to move data from one worksheet to another using column headers (see attached workbook). My issue is that if there isn't data in the column it copies the header into the second row.

    Is it possible to ignore columns without data?


    
    Sub Copy1()
    
        Dim ws As Worksheet
        Dim ws2 As Worksheet
        Set ws = Worksheets("Sheet1")
        Set ws2 = Worksheets("Timesheet1")
    
            With ws
        
                For i = 1 To .UsedRange.Columns.Count
                    Set x = ws2.Rows(4).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(5, x.Column).PasteSpecial xlValues
                    End If
        
                    Set x = Nothing
                Next i
            End With
    
    End Sub

    Thanks!!
    Kate
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy columns to a new workbook if the headers row matched
    By haitran in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-13-2017, 06:32 PM
  2. [SOLVED] Copy columns of different headers
    By SBBmaster09 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-30-2016, 04:10 PM
  3. Copy columns to other worksheet with same headers
    By etaver87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-09-2016, 11:25 AM
  4. macro to add columns after existing columns and copy/paste headers and formulas
    By rhybeka in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-21-2013, 02:35 PM
  5. [SOLVED] Copy pasting columns based on headers
    By krash297 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-14-2013, 02:13 PM
  6. Copy data under specific columns in sheet1 n paste to sheet2 under the same headers
    By ShyamEXcel in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2012, 02:15 PM
  7. Excel 2007 : Macro to copy columns based headers
    By rls1316 in forum Excel General
    Replies: 2
    Last Post: 04-25-2011, 10:22 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