Making a macro linking two range more efficient.

soroush.kalantari · #1 03-04-2024, 09:04 PM

I have written a macro to link a “ destination range" to “ visible cells” of “source range”. see the attached Excel, in which, if I be in sheet1!A1 and the "source range" be “Sheet2'!A1:P43” I want the sheet1!A1 formula be “=Sheet2!A1” and sheet1!A2 formula be "=Sheet2!A13” (connecting 1th,2th and .. row of “destination range” to1th,2th and .. “visible rows” of source range. Run the macro assigned to the rectangular in sheet1 to see what I want. In the inputbox "source rage" should be selected.).
My macro does what is expected, but it is slow when dealing with large data and because of this, I am seeking some ways to make it more efficient.

Can you guide me on this issue?

p45cal · #2 03-13-2024, 08:26 AM

try:

Code:

Sub blah()
Dim rng0 As Range, cll0 As Range
Set cll0 = Selection
Set rng0 = Application.InputBox(Prompt:="Select source range", Type:=8)
rng0.SpecialCells(xlCellTypeVisible).Copy
Application.Goto cll0
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
End Sub

soroush.kalantari · #3 03-17-2024, 12:09 AM

Thank you very much. Your reply solved the problem and as always learned me new things.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Help making a Macro	Mullin4414	Word VBA	4	03-06-2023 09:17 AM
Problem with efficient organisation of pictures in my pub	Croitz	Publisher	0	12-22-2018 05:48 AM
Linking Range of Cells in a Row	friesjh	Excel	5	06-28-2016 10:22 AM
Most efficient way to store numerous charts?	RAB	PowerPoint	0	07-13-2015 07:17 AM
Macro Question: Need help making a macro to highlight the first word in every sentence	LadyAna	Word	1	12-06-2014 10:39 PM