-1

In my Workbook, I have multiple worksheets each containing 2 graphs – I want to loop through these sheets copying ChartObjects(1) and ChartObjects(2) side-by-side into another sheet named "Graphs".

To clarify, the worksheets that contain 2 graphs are named "John", "Paul", "George" and "Ringo". I want to first select sheet “John”, copy ChartObjects(1) into cell A3 of “Graphs” and then ChartObjects(2) into cell K3 of "Graphs", next I want to select “Paul” and copy ChartObjects(1) into cell A24 of “Graphs” and ChartObjects(2) into cell K24 of "Graphs", and so on for “George”, “Ringo” etc.

I have researched this problem but could not find a solution to copy 2 ChartObjects from one sheet into another sheet side-by-side, as such I am currently using a code that simply selects each sheet in turn and copy/pastes the graphs - I am sure there is a better approach unfortunately it's beyond my limited VBA skills.

Note

As requested, I have updated my original question, for which @Harassed Dad had kindly provided a solution.

halfer
  • 19,824
  • 17
  • 99
  • 186
TheEndUK
  • 33
  • 5

1 Answers1

0
Sub example()
Const offsetrows = 26 ' numbers of rows to move down between copies
Dim ws As Worksheet
Dim c As ChartObject
Dim target As Worksheet
Set target = Worksheets("graphs") 'sheet to copy to
Dim t As Range
Set t = target.Range("a1") 'first cell to copy to
For Each ws In Worksheets
     Select Case ws.Name
        Case "graphs"
           'skip this sheet
        Case Else
            For Each c In ws.ChartObjects
               c.Copy
               t.PasteSpecial xlPasteAll
               Set t = t.Offset(offsetrows, 0)
 'edited code here===============
                If t.column = 1 then     'if it was in A then
                   set t = t.offset(-offsetrows,4)    Go to D
                 else
                    set t = t.offset(0,-4)     'if D then A
                 end if
     '=======================================
            Next c
      End Select
Next ws
End Sub
Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
  • That was quick! – Hrothgar Mar 03 '20 at 16:41
  • @Harassed Dad Thank you so much for such a prompt response - this works perfectly. Might I add one other question? If I had 2 graphs per sheet, would there be a way of copying ChartObjects(1) to A1 and ChartObjects(2) to D1 and so on? Many thanks again, TE. – TheEndUK Mar 03 '20 at 17:08
  • @TheEndUK - if this answers your question, make sure to click the check mark to the left to accept ti... then, new questions get asked as a new question :-) – BigBen Mar 03 '20 at 18:00
  • @Harassed Dad Sorry, do you mean I should post this as a brand new question or continue my query within this same thread? Many thanks – TheEndUK Mar 04 '20 at 08:49