VBA to copy the header and copy range

Topic

New Reply

Hi,

Can you please help me with below problem here.

1) “Main” sheet has header in row # 5.
2) “Sub” sheet has header in row # 5.
3) Header value in “Main” Sheet is something like “Apple”, “Mango”, “Peach” and so on.
4) Header value in “Sub” sheet is something like “John”, Steve”, Roler and so on.

What i am trying here is to VBA to search Main Sheet “apple and copy the entire column from Cell # 6 and below and paste it in Sub folder “John” column, row 6 below.
Likewise do for all other header i require.. Like Mango and Peach.

Please help

Thanks,
Shiva

Reply | Quote

Replies

That sounds like homework to me. Is it?
We don’t mind helping with homework, just like to know. 🙂

cheers, Paul

Reply | Quote

do you want to copy to the sub sheet (located where????? sub folder…… Not at all clear here)

Reply | Quote

Hi Don

I think there are two tab sheets, named [Main] and [Sub]

zeddy

Reply | Quote

Hi shiva

There are many ways of doing this, depending on your particular situation.
In the attached example, data from columns on sheet [Main] is copied to specified columns on sheet [Sub].
In my example, I used row1 on sheet [Main] to specify the particular destination column heading of sheet [Sub]
I used a formula in row2 to determine the corresponding column number to copy-to.
Then this vba routine to do the copying:

Code:

Sub copyData()

Sheets("Main").Select

'Find last data row, using column [A]..
zLastRow = Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In [A5:K5]
zCol = cell.Column

zCopyToCol = Cells(2, zCol)
If zCopyToCol  "" Then
Cells(6, zCol).Resize(zLastRow, 1).Copy Sheets("Sub").Cells(6, zCopyToCol)
End If
Next cell

[a1].Select

End Sub

see attached example file.

zeddy

Reply | Quote

Hi shiva

..you could easily modify the vba code to set the required column range e.g. from [A5:K5] to whatever you need.

zeddy

Reply | Quote

Wow, thats is something new I learned Zeddy,. “Putting Destination # as formula and then Use VBA to do its work. ”

My challenge is, ” “Sub” sheet John column can change its place in random file. So i would need to Find header and paste data.
Note: Its only 6 coloumn from “Main” sheet data will be copied, and find 6 Column of “Sub” sheet. Now Sub sheet has 25 columns and it may change its palce.

Reply | Quote

Hi shiva

In my example file, move column ‘John’ to any column in row6 of sheet [Sub]
The formula in row2 will then ‘find’ where the column ‘John’ is.
see attached example 2 file.

Note: Its only 6 coloumn from “Main” sheet data will be copied, and find 6 Column of “Sub” sheet.

I’m not sure I understand this.
Can you explain a little more please.

zeddy

Reply | Quote

I was just trying to be more clear by saying, it is just 6 items that it will be looking and copy pasting in main sheet.

Your code works like charm, but my problem here is, i cannot make changes in the Main sheet. Code has to be either in VBA or in Master sheet where I am going to put colorfull button to run various macros. Like in your attachement, I will be using Sheet 3 as my master sheet with macro button in it.

Regards,

Reply | Quote

Hi shiva

Thanks for clearing that up.
OK. So there are only 6 separate columns on sheet [Main] to be copied.
Are these 6 columns always in row 5?
Are these 6 column always together on sheet [Main] (for example, in [A5:F5])?

I have another method that will do this.

zeddy

Reply | Quote

Main file would always have 8 column from which I need to copy the data.. A5:O5. However, F,G,H column is hidden as it was not required.

Hope this helps.

Thanks a lot for your assistance.

Reply | Quote

Hi shiva

[A5:O5] is 15 columns; less 3 hidden columns (F,G,H) = 12 columns
Where are the 8 column from which I need to copy the data ?
I need to know, to give you the easiest solution.

And, just to confirm, you ‘know’ what the corresponding destination column ‘headings’ are on sheet [Sub] for each of the 8 source headings on sheet [Main] (but these destination columns could be in any order, on row 5 of sheet [Sub] ).

zeddy

Reply | Quote

my bad. J,K,L column is also hidden.

It can be anywhere in Sub sheet A5:X5.

Reply | Quote

Hi shiva

OK. Got it.
It’s a little late now, so I’ll test and post back my file tomorrow.

zeddy

Reply | Quote

Hi shiva

see attached file.

This method displays a Form to allow you to select the destination column heading on sheet [Sub] for each of the source heading column on sheet [Main].

This uses named ranges assigned to each of the headings on both sheets.
In this version, you can only copy to-and-from visible columns.

Technically, we don’t need a dropdown for the source column selection.
We could just use a pre-filled textbox for these values.
But I left them as dropdowns, for general use with this method.

Please let me know if this is does what you want.

zeddy

Reply | Quote

That is master piece… Help me out, how did you do that trick? I mean, I am not sure i would ever understand how to add or modify that combox which you created? Can show me the direction please? say I want to change name or add few more fields in the combobox?

Reply | Quote

Hi shiva

Thank you for the compliment. I had to think of a way to allow you to choose the destination columns.
I thought one way would be to use a Form.
The main ‘trick’ is to use range names assigned to the header rows for each of the sheets.
Although I set it up for 8 columns only, if you insert new columns (with new headers) within the range on sheet [Main], these will be picked up automatically for the Form combobox dropdowns. Similarly, if you hide any columns, they will be excluded from the dropdowns (for both sheets).
Also, if you change the text in any of the headers, this will be automatically shown in the Form.

zeddy

Reply | Quote