Solved

Copy range into clipboard and paste it above rows with bold cell entries

Posted on 2012-03-28
2
310 Views
Last Modified: 2012-03-29
Dear Experts:

I would like to copy and paste a selected range as follows:

This range (B2:G2) on the active worksheet is selected manually.

The macro is to copy the selection into the clipboard and paste it
above each row that has bold cell entries.

I have attached a sample file with detailed explanations for your convenience.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas


Inserting-Headers-Before-Rows-Co.xlsm
0
Comment
Question by:AndreasHermle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37779647
Here's your code which starts at row 4, then looks for Bold cells in column B, pasting the header on row 2 one row above the found bold row.

Option Explicit

Sub pasteHeaders()
Dim wkb As Workbook
Dim wks As Worksheet
Dim rng As Range
Dim r As Range
Dim rCopyRange As Range

    Set wkb = ThisWorkbook
    Set wks = wkb.ActiveSheet
    
    Set rCopyRange = wks.Range("B2:G2")
    
    Set rng = wks.Range("B4", wks.Range("B" & wks.Rows.Count).End(xlUp))
    
    For Each r In rng
        If r.Value <> vbNullString Then
            If r.Font.Bold = True Then
                rCopyRange.Copy r.Offset(-1, 0)
            End If
        End If
    Next r
End Sub

Open in new window


PS - if you anticipate a wider header range and want to use SELECTION to dictate the header, just change line 13 to:

Set rCopyRange = Selection 'as opposed to wks.Range("B2:G2")

Cheers,

Dave

See attached.

Enjoy!

Dave
Inserting-Headers-Before-Rows-Co.xlsm
0
 

Author Closing Comment

by:AndreasHermle
ID: 37782819
Hi Dave,

great! Works like a charm.

Thank you very much for your swift and professional help.

Regards, Andreas
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question