CODEWITHSUNDEEP
vbaexcel
user1040563
user1040563

Reputation: 5261

copying different ranges of cells in excel according to styling

Im using a pivot table to arrange my data. when I copy my data to another sheet its looks like that: enter image description here

but I want the data to be one by the other like that: enter image description here

how can I use the bold lines as headers and cut only the required ranges which are not fixed? I tried using macros but I could only copy fixed ranges so I'm doing it manually each time (and some om the sheets are pretty big). please help, thank you.

Upvotes: 1

Views: 95

Answers (1)

user2480047
user2480047

Reputation:

You have full control on all the cells in the PivotTable. Sample code:

Dim curpivottable As PivotTable
Set curpivottable = ActiveSheet.PivotTables(1)
Dim curRange As Range
Set curRange = curpivottable.TableRange1

For Each cell In curRange.Cells
    If (cell.Font.Bold) Then
       'This is a bold cell
    End If
Next cell

Bear in mind that curRange includes all the contents in curpivottable, from the first row (where the title is).

-- UPDATE

My answer above was the generic way to deal with ranges in PivotTables. If all what you want is just copying/pasting a range, you can do it directly (is the same if the range is in the PivotTable or not). Example:

Dim origRange As Range
Set origRange = Sheets("Sheet1").Range("A2:A500")
Dim destRange As Range
Set destRange = Sheets("Sheet2").Range("A2")
origRange.Copy
destRange.PasteSpecial xlPasteValues

Equivalently, you can check the format of the given cells (being bold or not) independently upon being inside a PivotTable or not.

Upvotes: 1

Related Questions