Code to find Blank or Used cell after Column header

[AmitSingh]

Hi All,

Need urgent help to find the blank or used cell just after the Column header and paste the value with column header name till last blank cell in another column.

There are two condition:-
-> If the rows is filled with data then copy the column the column with Header name and paste it to another column.
-> If the rows is not filled with data and check the next blank cell, then copy only header name of the column and paste to another column.

I have used below code to solve this but i can paste the data only if the cell is filled.

Code:-

Thisworkbook.Worksheets("Student").Activate
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Thisworkbook.Worksheets("Marks").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Any help is appreciated as this little urgent. Thanks in advance.

 

[vletm]

AmitSingh
Urgent ...
1) Column Header..
Do You mean gray range?
2) Which sheet?
Marks-sheet is empty and Students-sheet has something.
>> Marks-sheet's cell is A1 .. but there is no header!
>> Students-sheet's cell is E1 (if header is gray range)
3) paste the value with column header name till last blank cell in another column.
Could You explain/open that more? E1 has no header?
What to copy ... where to paste?
4) Two conditions ...
Show samples or something...
5) Your code
Which part finds? How/why You select C1? .. and so on.

> Need to know, what do You really would like to happen? <

ASAP, because You need this u...t​

 

[AmitSingh]

1) Column Header..
Yes the grey range.

2) Which sheet?
In Marks sheet, output should be paste, data needs to copy from Student Sheets.

3) paste the value with column header name till last blank cell in another column.
yes, i have created the Result sheet where i have pasted the sample result.
->Means if the source data column Maths(C1) has no data then only header will be pasted in Marks Sheet of Maths column A1.
-> And if source data column English (D1) has data including blank cells then it paste the data with header in Marks sheets of English column B1.

4) Two conditions ...
I have attached the updated file

5) Your code

Thisworkbook.Worksheets("Student").Activate
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Thisworkbook.Worksheets("Marks").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[vletm]

AmitSingh
1,2) Okay
3) Do You mean columns which has empty cells?
4) hmm..
5) Use code-tags.
You skipped my questions...
> Check this ... press [ copy ]-button (( Student!E1 ))

 

[AmitSingh]

3) Do You mean columns which has empty cells?
Yes, the column which have empty cells and also column which have filled and empty both.
4) hmm..
5) Use code-tags.

It will select and copy the column C1 range with header name(Maths) and paste to another sheet name Marks of Column A1(Maths).

In the same way it will select the column D1(English) range with header name and paste to another sheet name Marks of column B1(English) which has filled and blank cells.

Thisworkbook.Worksheets("Student").Activate
    Range("C1").Select
    Range(Selection, Selection.End(xlDown)).Select  'problem is here, it select the full column if it goes xlDown(when cells is blank, it is ok when cell is not blank)
  
Application.CutCopyMode = False
    Selection.Copy
    Thisworkbook.Worksheets("Marks").Activate
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Hope you have got my point, let me know if you required more information.
Thanks for assistance.

 

[vletm]

AmitSingh
3) Did You check my file? It would be Your hope.
4) hmm?
5) Use code-tags, while You paste some even same code to this forum!

 

[AmitSingh]

yes, checked the file and it is working.

Thanks a lot for your help.

Thank you.