Copy Common Columns to separate sheet

Question

I tried the below code to copy entire column from one page to other page.

I've common header that is June scattered in the sheet, I want to copy only the columns, with header name as "June" in the separate sheet one after the other.

EG if Col A, C,L,M has column header as June, then on the next sheet these should be only copied as A,B,C,D.

Sheets("sheet1").Select 
June = WorksheetFunction.Match("Description", Rows("1:1"), 0) 

Sheets("sheet1").Columns(June).Copy Destination:=Sheets("sheet2").Range("A1")

use the `Find` function in row 1, set a `Range`, and if `Find` is successful, get the `Column` property — Shai Rado, Jul 28 '17 at 08:16

Mrig · Accepted Answer · 2017-07-28T09:00:24.487

1

Following might help:

Option Explicit

Sub Demo()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim headerRng As Range, cel As Range, copyRng As Range
    Dim lastCol As Long, col As Long

    Set srcSht = ThisWorkbook.Sheets("Sheet1")      'Sheet1
    Set destSht = ThisWorkbook.Sheets("Sheet2")     'Sheet2

    lastCol = srcSht.Cells(1, srcSht.Columns.Count).End(xlToLeft).Column    'last column of Sheet1

    With srcSht
        For Each cel In .Range(.Cells(1, 1), .Cells(1, lastCol))  'loop through each header name in Sheet1
            If cel = "June" Then                            'check header is "June"
                If copyRng Is Nothing Then                  'assign range to copy
                    Set copyRng = .Columns(cel.Column)
                Else
                    Set copyRng = Union(copyRng, .Columns(cel.Column))
                End If
            End If
        Next cel
    End With
    copyRng.Copy destSht.Range("A1")                'copy and paste desired columns
End Sub

edited Jul 28 '17 at 09:00

answered Jul 28 '17 at 08:17

Mrig

11,612
2
13
27

1

Nice + you can use `With srcSht` to shorten and clean up your code – Shai Rado Jul 28 '17 at 08:57
It shows an error in copyRng.Copy destSht.Range("A1"). – Neha Kohli Jul 28 '17 at 11:26
Run time error – 91 Object variable or With block variable not set – Neha Kohli Jul 28 '17 at 11:32
@NehaKohli - Can you debug and check `copyRng.address` – Mrig Jul 28 '17 at 11:36
@NehaKohli - In which row do you have headers in `Sheet1`? And make sure you have at least one column with header June. – Mrig Jul 28 '17 at 11:38
Sorry, I didn't got that.. how to do.. can you help ? – Neha Kohli Jul 28 '17 at 11:42
Row 1 I have the header as June – Neha Kohli Jul 28 '17 at 11:43
you can try the working solution in this post: https://stackoverflow.com/questions/45371362/excel-merge-worksheets-with-different-structure/45372405#45372405 let me know if this is of help. – Roan Jul 28 '17 at 11:47
@NehaKohli - Put a breakpoint at the error line then execute the code and in immediate window type `?copyRng.address` See [this](http://www.excel-easy.com/vba/examples/debugging.html) for how to debug. – Mrig Jul 28 '17 at 11:47
I think its in infinite loop, its not coming out of Next Cell – Neha Kohli Jul 28 '17 at 11:55
@NehaKohli - If you are getting error at line `copyRng.Copy destSht.Range("A1")` then it is obvious that it is coming out of loop. Still check the value of `lastCol` – Mrig Jul 28 '17 at 11:57
Sorry, the lastCol is 12 – Neha Kohli Jul 28 '17 at 12:18
@NehaKohli - Put the break point at `copyRng.Copy destSht.Range("A1")` this line. For loop will be executed for 16379 times and check `copyRng.Address` – Mrig Jul 28 '17 at 12:19
Done, now ? It says, "Run time error – 91 Object variable or With block variable not set" – Neha Kohli Jul 28 '17 at 12:31
@NehaKohli - Check `copyRng.Address` – Mrig Jul 28 '17 at 12:34
I don't know how to check copyRng.Address – Neha Kohli Jul 28 '17 at 12:40
@NehaKohli - Put cursor at error line `copyRng.Copy destSht.Range("A1")` and press `F9` to put break point. Then press `F5` to run the code. Then type `?copyRng.Address` in immediate window and press enter and check the value. Press `Ctrl+G` to open immediate window. – Mrig Jul 28 '17 at 12:44
I am not getting any immediate window where I can place ?copyRng.Address – Neha Kohli Jul 28 '17 at 12:50
@NehaKohli - Press Ctrl+G to get immediate window in VBA Editor. – Mrig Jul 28 '17 at 12:51
after entering ?copyRng.Address in the immediate window – Neha Kohli Jul 28 '17 at 12:55
@NehaKohli - Probably there's nothing in `copyRng`. Are you sure there is a column with header "June" in row 1 with no spaces around? – Mrig Jul 28 '17 at 12:58
Yes.. I am very much sure. – Neha Kohli Jul 28 '17 at 12:59
Hey.. it works.. Thank you very much..I must say you are most patient person . – Neha Kohli Jul 28 '17 at 13:01
Thanks a lot Mrig :) – Neha Kohli Jul 28 '17 at 13:01
@NehaKohli - What was the issue? – Mrig Jul 28 '17 at 13:01
June was written in capital.. excel is case sensitive as well – Neha Kohli Jul 28 '17 at 13:07
@NehaKohli - If you want code to do non case sensitive match, you can use `If UCase(cel) = "JUNE" Then` instead of `If cel = "June" Then` – Mrig Jul 28 '17 at 13:11

Copy Common Columns to separate sheet

1 Answers1