Pasting from multiple ranges (union help)

Milbourn

New Member
Joined
May 27, 2014
Messages
44
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:









Code:
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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Milbourn

New Member
Joined
May 27, 2014
Messages
44
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?
 

Milbourn

New Member
Joined
May 27, 2014
Messages
44
Thank you for your response.

Is there a method to do this across multiple sheets?
 

Milbourn

New Member
Joined
May 27, 2014
Messages
44
Is there any other method anyone can suggest? Or any way of carrying out my second question? Any help is appreciated
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

New Member
Joined
May 27, 2014
Messages
44
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

New Member
Joined
May 27, 2014
Messages
44
(I say this as the first active workbook's name is variable and would be difficult to add)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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"?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,812
Messages
5,638,489
Members
417,028
Latest member
JFCLUK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top