want to save the D1 Column Header and place it in all rows of column G (1 Viewer)

[dcavaiani]

KALINE	No	Card No. and Manufacturer	GOOD 2	12-LOW	12-HIGH	Grade	Select
KALINE	201​	1954 Topps - ROOKIE CARD	25​	$400.00​	$700.00​	Place GOOD 2	Select
KALINE	23​	1955 Bowman	30​	$90.00​	$150.00​	Place GOOD 2	x
KALINE	4​	1955 Topps	120​	$90.00​	$150.00​	Place GOOD 2	x

ETC
This also changes throughout the sheet as lower on, the grade NAME in D column eventually changes to a "higher" named grade 'header' row, and that higher grade name has to be SAVED and pasted into Column G. Could be 4 to 5 K rows in the sheet. Happy New Year to all of our experts. Drink up if u wish and forget about responding for now ...

 

[Gasman]

You post a thread like this, which make no sense to anyone who speak english?
I can understand that English is not your first language, but feel assured Google trranslate would do a better job?

As you deleted your last post which I responded to, that is me out of it.
You are also on my IL..
I am done.

 

[dcavaiani]

You post a thread like this, which make no sense to anyone who speak english?
I can understand that English is not your first language, but feel assured Google trranslate would do a better job?

As you deleted your last post which I responded to, that is me out of it.
You are also on my IL..
I am done.

I think a deleted a post in the Excel forum a few minutes after I found my own solution. I would hate to lose your expertise because I have posted a question which essentially is poorly created. Never saw your response b4 I deleted my prior post as I would not have ignored you!

 

[dcavaiani]

Sub KalineMacro()
'
' Macro recorded 1/1/2024 by dcava

    Range("D1").Select
    Selection.Copy
    Range("G2:G34").Select
    ActiveSheet.Paste
    
        Range("D35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G36:G68").Select
    ActiveSheet.Paste
    
     Range("D69").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G70:G102").Select
    ActiveSheet.Paste
    
Range("D103").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G104:G136").Select
    ActiveSheet.Paste
    
    Range("D137").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G138:G170").Select
    ActiveSheet.Paste
    
    Range("D171").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G172:G204").Select
    ActiveSheet.Paste
    
    Range("D205").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G206:G238").Select
    ActiveSheet.Paste
    
Range("D239").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G240:G272").Select
    ActiveSheet.Paste

End Sub

I recorded the above macro to generate the VB. It is cumbersome because of the hard coded row numbers. The selected Column D hard coded row numbers HAVE a VALUE < 0. Wish it could be coded without the hard coding of row numbers - for both columns.

 

[dcavaiani]

Must say, it did not feel good to get Mr. Gasman's response yesterday. Clearly, he is probably the most active and knowledgeable responder on this whole site! I thank him for all he has done for me in the past!! I was able to set variables and use them for OFFSETS. If Gasman had not informed me that I could record a Macro and then inspect the VBA code, I would never have found that helpful tip!

 

[Gasman]

How did you record that macro?
You went to each header line and copied and pasted from that header line cell to all the rows up to the next header line or when you find the next blank row.?

You just need to do the same with automation.
I have already told you you do not need to select cells to copy and paste, you can do it just by referrring to them, but if you are more comfortable doing it that way, then keep doing it, until it slows the process down.

I did the same when I started with Excel automation.
Here is some code that I used to bring an excel sheet, that was exported from Quicken as a csv.
It tidied it up ready for Access import.

'   Find top BALANCE row and delete
    ActiveSheet.Cells(1, 1).Select
    Cells.Find(What:="BALANCE ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Range(ActiveCell.Row & ":" & ActiveCell.Row - 1).Select
    Selection.Delete Shift:=xlUp
'   Find TOTAL field, select extra rows and delete
    Cells.Find(What:="TOTAL ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Range(ActiveCell.Row & ":" & ActiveCell.Row + 10).Select
    Selection.Delete Shift:=xlUp
'   Now format Amount column so no commas present
    Columns("I:I").Select
    Selection.NumberFormat = "0.00"
'   Now remove empty column A
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft

You need to do the same for your header row or when not found the next empty row.
All that again is within a loop.

Re your deleted post.
If you found a solution, you should have posted it. That could help others in the future. After all, that is what these forums are all about?

 

[dcavaiani]

I did the copy and paste to record macro as you described. I only did 2 of the 8 iterations and then I inspected the VB. After copying code and hard coding the column numbers, I found that cumbersome, as I have another 11 sets of cards from other years. That's when I found how the Offset works and then I set 2 variables and substituted them for the hard coded numbers - which are different but constant (values) for each card collection
I am going to study that code you sent. I originally searched for a similar example, but did not see one that seemed to match up well to my issue. Your code should match well as you understand my issue! I am trying to remember what my question was that I deleted so quickly. I cannot remember what that was ?? Interestingly, like you, once I get all 12 Collections of cards formatted, I combine them all and import into Access when I do most of the further analysis! While I have stayed away from doing so, I wonder what you think of the 'linking' option which connects Access table back Excel ?
Just remembered, I had thought that there was an Excel command =fontcol which I could access to post a constant value into a 2nd field based on a background Color. Kept getting a NAME error, then I realize that I had a module to help with that, and the module had to have the exact same name, i.e., fontcol

 

[Pat Hartman]

Must say, it did not feel good to get Mr. Gasman's response yesterday.

You've been a member of the forum for 10 years. You should have an understanding of how forums work by now. You do realize that none of us gets paid to help you, right? So, why do we bother to answer your questions without any payment? Because we have nothing better to do? No, the answer is because it gives back to the community. When you deleted the post Gasman referred to, you discarded his answer as if it had no meaning and his work and knowledge he shared with you was worthless.

Do not delete posts which have replies. If you have an answer, post it so we all benefit and mark the question solved. If no one replied, then it is ok to delete the post. But otherwise, only a moderator should delete a post with responses.

 

[dcavaiani]

You've been a member of the forum for 10 years. You should have an understanding of how forums work by now. You do realize that none of us gets paid to help you, right? So, why do we bother to answer your questions without any payment? Because we have nothing better to do? No, the answer is because it gives back to the community. When you deleted the post Gasman referred to, you discarded his answer as if it had no meaning and his work and knowledge he shared with you was worthless.

Do not delete posts which have replies. If you have an answer, post it so we all benefit and mark the question solved. If no one replied, then it is ok to delete the post. But otherwise, only a moderator should delete a post with responses.

The simple answer is that I recklessly deleted the post within minutes WITHOUT looking for Gasman's (or any other) prompt response. Sorry for the mix up!! I hate to dominate/garbage up the awesome forum with a bunch of rash questions ... I regularly LIKE each and every post that ANY member puts forth, whether it really helps me or not. Anyone who takes even a minute to answer needs to be praised, in my opinion! Maybe you can put a caveat on the deletion option, in addition to asking for a reason why, stating that there are already responses entered? Or, just don't permit a deletion which already HAS responses? Would have saved me a lot of embarrassment.

 

[Pat Hartman]

I regularly LIKE each and every post that ANY member puts forth, whether it really helps me or not.

Those of us who answer questions are not children. We don't keep score with "likes". In fact, I wish the forum didn't even have a "like" feature because "liking" a post has no meaning. What has meaning is - did the post solve your problem? did the post put you on the right path to solving the problem. These are the things that make "answers" like gold. When people find a question that is similar to their own, they don't care about "likes", they care - did this work?????

Sorry to keep harping on this. Now that you understand the purpose of answers, you won't make a similar mistake again. The slight was inadvertent. Gasman is an adult, he won't hold a grudge It isn't your place to "clean up". You "clean up" by saying whether something worked or didn't work. That makes the thread valuable even when you didn't actually get an answer because it gives the seeker one more thing that "didn't work" so they can keep seeking. And help us all by saving your "thanks" for actual solutions.

 

[dcavaiani]

"But otherwise, only a moderator should delete a post with responses."

Those of us who answer questions are not children. We don't keep score with "likes". In fact, I wish the forum didn't even have a "like" feature because "liking" a post has no meaning. What has meaning is - did the post solve your problem? did the post put you on the right path to solving the problem. These are the things that make "answers" like gold. When people find a question that is similar to their own, they don't care about "likes", they care - did this work?????

Sorry to keep harping on this. Now that you understand the purpose of answers, you won't make a similar mistake again. The slight was inadvertent. Gasman is an adult, he won't hold a grudge It isn't your place to "clean up". You "clean up" by saying whether something worked or didn't work. That makes the thread valuable even when you didn't actually get an answer because it gives the seeker one more thing that "didn't work" so they can keep seeking. And help us all by saving your "thanks" for actual solutions.

"But otherwise, only a moderator should delete a post with responses."

 

[dcavaiani]

Dear Super Moderator:

"... you won't make a similar mistake again."

IF anyone ever makes that same kind of a "mistake" going forward, that must blamed on the Forum - for such a grave System/Programming omission, especially considering all things promoted on a Site of this nature! 99.9 % of all using this forum know that a 'SIMPLE IF .... THEN ... ELSE' would prevent any further deletion issues, plus then you will not have to respond with rhetoric even again!

Nor will any one of your main responders ever have to revert back to such childish, yet demeaning, behavior.

I'm guessing that you are not responding to my prior post, 'also quoting you', because you have come to a realization of the Forum's System shortcoming in Programming logic. Don't bother telling me it is a "packaged application" and that you have no influence nor method to elicit the correction of this snafu. No more responses are needed. Just move on and get this issue resolved in the proper manner.

 

[Pat Hartman]

IF anyone ever makes that same kind of a "mistake" going forward, that must blamed on the Forum - for such a grave System/Programming omission, especially considering all things promoted on a Site of this nature! 99.9 % of all using this forum know that a 'SIMPLE IF .... THEN ... ELSE' would prevent any further deletion issues, plus then you will not have to respond with rhetoric even again!

Only a person has the intelligence to know if a question has had an answer. The forum software isn't that smart and if there is a setting that controls this, the forum rule would have to be arbitrary which wouldn't make any sense. Random posts don't constitute an answer and so Threads with posts should be allowed to be deleted by the originator because the originator has the intelligence to determine if the thread has any future value.

Just move on and get this issue resolved in the proper manner.

I'm also not at all sure why you think that the code that runs this site can be modified. There's some changes I would dearly love to make to Outlook but I don't have access to the code to make the change and MS didn't give me an option to use.

You are now in a "huff" and are considering yourself the victim here so you are right, the conversation is over.