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]

for the purpose of this yes there are their names.

Thank you mentioning Hyperlink.Add I believe I have found the answer for my second point.

 

[Andrew Poulsom]

Try:

Sub SummaryMacro()
    Dim wb As Workbook
    Set wb = Workbooks.Open(Filename:="C:\USER\SUMMARY PAGE")
    With wb.Sheets("SUMMARY PAGE 1")
        ThisWorkbook.Sheets("SHEET 1 WORKBOOK 1").Range("c6,c9,c10,c12").Copy
        .Range("c" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        ThisWorkbook.Sheets("SHEET 2 WORKBOOK 1").Range("E10,F10,H10,J10,K10").Copy
        .Range("g" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End With
    Application.CutCopyMode = False
End Sub

 

[Milbourn]

That is very useful thank you, I need to learn how to code without the use of unnecessary activates and selects.

Thank you very much