Pasting from multiple ranges (union help)

[Milbourn]

Hi All,

Starter here, trying to get a macro to paste a number of cells from 2 different sheets onto a summary page.

I've looked into the Union method however I can't find any previous answers that show what to do when taking information from two different sheets. I was wondering if anyone could help? Here is what I have so far:

Sub SummaryMacro()


Sheets("SHEET 1 WORKBOOK 1").Range("c6,c9,c10,c12").Select

Selection.Copy

Workbooks.Open Filename:="C:\USER\SUMMARY PAGE"

Sheets("SUMMARY PAGE 1").Range("c7").Select

lastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row

Range("c" & Rows.Count).End(xlUp).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False

'Requires back to primary workbook or just sheet selection?

Sheets("SHEET 2 WORKBOOK 1").Activate

Range("E10,F10,H10,J10,K10").Select

Selection.Copy

Sheets("SUMMARY PAGE 1").Range("g7").Select

lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Range("g" & Rows.Count).End(xlUp).Offset(1, 0).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

End Sub

 

[Milbourn]

In addition to the above so I don't spam the forum with threads: I have been looking for a method of inserting a hyperlink on the summary page to the active sheet that I am copying/pasting the data in addition to the above. Is this possible through VBA or would it have to be done manually?

 

[Andrew Poulsom]

The Union method requires that all the ranges are on the same worksheet.

 

[Milbourn]

Thank you for your response.

Is there a method to do this across multiple sheets?

 

[Andrew Poulsom]

No, sorry.

 

[Milbourn]

Is there any other method anyone can suggest? Or any way of carrying out my second question? Any help is appreciated

 

[Andrew Poulsom]

You can't copy multiple selections either in the user interface or in VBA. So you current code is fine apart from all the unnecessary Activates/Selects.

To insert a Hyperlink you can use Hyperlinks.Add. Check it out in VBA Help.

 

[Milbourn]

Again, thank you for your reply. I think my issue which is causing all the errors is the switch over back onto WORKBOOK 1 SHEET 2 (Specified dimension not valid err).

Would this be solved by adding Activate.Workbook in the middle?

 

[Milbourn]

(I say this as the first active workbook's name is variable and would be difficult to add)

 

[Andrew Poulsom]

Sorry, I didn't realise you were getting errors. What are the names of the worksheets whose data you want to copy? Or are they really "SHEET 1 WORKBOOK 1" and "SHEET 2 WORKBOOK 1"?