Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Link Cells & Paste Special Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 121
Default Link Cells & Paste Special Macro
I want to create a code that will
1.) Link I3:I16, I20:I33, I37:I50 from Sheet 2 to B3:B16, B20:B33, B37:B50
from
Sheet 1.
2.) Copy H3:H16, H20:H33, H37:H50 from Sheet 2 and then past special values
into H3:H16, H20:H33, H37:H50 from Sheet 2.

Here is the code I currently have. But its not working correctly. Any
suggestions?

Column I

Range("I3").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I4").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I5").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I6").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I7").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I8").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I9").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I10").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I11").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I12").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I13").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I14").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I15").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I16").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I17").Select
ActiveWindow.SmallScroll Down:=6
Range("I20").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I21").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I22").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I23").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I24").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I25").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I26").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I27").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I28").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I29").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I30").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I31").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I32").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I33").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I34").Select
ActiveWindow.SmallScroll Down:=21
Range("I37").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I38").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I39").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I40").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I41").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I42").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I43").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I44").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I45").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I46").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I47").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I48").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I49").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("I50").Select
ActiveCell.FormulaR1C1 = "='Sheet 1'!RC[-6]"
Range("H3:H16").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Range("H20:H33").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=12
Range("H37:H50").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 222
Default Link Cells & Paste Special Macro
Sub copystuff()
Application.ScreenUpdating = False
Sheets("Sheet2").Range("B3:B16, B20:B33, B37:B50").Value = _
Sheets("Sheet1").Range("I3:I16, I20:I33, I37:I50").Value

With Sheets("Sheet2")
Range("H3:H16").Copy
Range("H3:H16").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("H20:H33").Copy
Range("H20:H33").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("H37:H50").Copy
Range("H37:H50").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default Link Cells & Paste Special Macro

Sub copystuff()
With Sheets("Sheet2")
.Range("H3:H16").Value = .Range("H3:H16").Value
.Range("H20:H33").Value = .Range("H20:H33").Value
.Range("H37:H50").Value = .Range("H37:H50").Value
End With
End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default Link Cells & Paste Special Macro
amended

Sub copystuff()
dim sh1 as worksheet
set sh1 = Sheets("Sheet1")
With Sheets("Sheet2")
.Range("H3:H16").Value = sh1.Range("H3:H16").Value
.Range("H20:H33").Value = sh1.Range("H20:H33").Value
.Range("H37:H50").Value = sh1.Range("H37:H50").Value
End With
End Sub

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 121
Default Link Cells & Paste Special Macro
Thank you for your feedback. I tried the following code as recommended, but
it did not work. Any thoughts?

Sub copystuff()
Application.ScreenUpdating = False
Sheets("Sheet2").Range("B3:B16, B20:B33, B37:B50").Value = _
Sheets("Sheet1").Range("I3:I16, I20:I33, I37:I50").Value

With Sheets("Sheet2")
Range("H3:H16").Copy
Range("H3:H16").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("H20:H33").Copy
Range("H20:H33").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("H37:H50").Copy
Range("H37:H50").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub




"JBeaucaire" wrote:

Sub copystuff()
Application.ScreenUpdating = False
Sheets("Sheet2").Range("B3:B16, B20:B33, B37:B50").Value = _
Sheets("Sheet1").Range("I3:I16, I20:I33, I37:I50").Value

With Sheets("Sheet2")
Range("H3:H16").Copy
Range("H3:H16").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("H20:H33").Copy
Range("H20:H33").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("H37:H50").Copy
Range("H37:H50").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 121
Default Link Cells & Paste Special Macro
I appreciate you're feedback. Thank you. I am using the following code, but
it is not working. Any thoughts?

Sub copystuff()
With Sheets("Sheet2")
.Range("H3:H16").Value = .Range("H3:H16").Value
.Range("H20:H33").Value = .Range("H20:H33").Value
.Range("H37:H50").Value = .Range("H37:H50").Value
End With
dim sh1 as worksheet
set sh1 = Sheets("Sheet1")
With Sheets("Sheet2")
.Range("H3:H16").Value = sh1.Range("H3:H16").Value
.Range("H20:H33").Value = sh1.Range("H20:H33").Value
.Range("H37:H50").Value = sh1.Range("H37:H50").Value
End With
End Sub


"Patrick Molloy" wrote:

amended

Sub copystuff()
dim sh1 as worksheet
set sh1 = Sheets("Sheet1")
With Sheets("Sheet2")
.Range("H3:H16").Value = sh1.Range("H3:H16").Value
.Range("H20:H33").Value = sh1.Range("H20:H33").Value
.Range("H37:H50").Value = sh1.Range("H37:H50").Value
End With
End Sub

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default Link Cells & Paste Special Macro
use my amended code --- you seem to have a combinmation of my old and my new


Sub copystuff()

dim sh1 as worksheet
set sh1 = Sheets("Sheet1")

With Sheets("Sheet2")
.Range("H3:H16").Value = sh1.Range("H3:H16").Value
.Range("H20:H33").Value = sh1.Range("H20:H33").Value
.Range("H37:H50").Value = sh1.Range("H37:H50").Value
End With
End Sub


"Patrick Molloy" wrote:

amended

Sub copystuff()
dim sh1 as worksheet
set sh1 = Sheets("Sheet1")
With Sheets("Sheet2")
.Range("H3:H16").Value = sh1.Range("H3:H16").Value
.Range("H20:H33").Value = sh1.Range("H20:H33").Value
.Range("H37:H50").Value = sh1.Range("H37:H50").Value
End With
End Sub

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 121
Default Link Cells & Paste Special Macro
Thank you. You were right. Also I figured out how to do the first part of
linking the cells.

Range("J3").Select
ActiveCell.FormulaR1C1 = "=€˜Sheet1 !RC[-8]"

For each column that I wanted to move to the right the code changed as follows

Range("J3").Select
ActiveCell.FormulaR1C1 = "=€˜Sheet1 !RC[-9]"
Range("J3").Select
ActiveCell.FormulaR1C1 = "=€˜Sheet1 !RC[-10]"

I was changing the wrong part of the code. That's why it wasn't working. I
needed to change the numbers.



"Patrick Molloy" wrote:

use my amended code --- you seem to have a combinmation of my old and my new


Sub copystuff()

dim sh1 as worksheet
set sh1 = Sheets("Sheet1")

With Sheets("Sheet2")
.Range("H3:H16").Value = sh1.Range("H3:H16").Value
.Range("H20:H33").Value = sh1.Range("H20:H33").Value
.Range("H37:H50").Value = sh1.Range("H37:H50").Value
End With
End Sub


"Patrick Molloy" wrote:

amended

Sub copystuff()
dim sh1 as worksheet
set sh1 = Sheets("Sheet1")
With Sheets("Sheet2")
.Range("H3:H16").Value = sh1.Range("H3:H16").Value
.Range("H20:H33").Value = sh1.Range("H20:H33").Value
.Range("H37:H50").Value = sh1.Range("H37:H50").Value
End With
End Sub

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
'paste special', 'paste link' formatting transfer jrebello Excel Discussion (Misc queries) 2 July 25th 07 08:46 AM
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. tln Links and Linking in Excel 0 April 22nd 07 04:28 PM
Excel Paste Special Link cpetta Excel Programming 0 June 30th 05 02:39 AM
Macro Paste Special Merged Cells KC[_5_] Excel Programming 0 November 2nd 04 02:09 AM
Paste Link is greyed out during Paste Special Lawrence M. Seldin, CMC, CPC Excel Programming 0 May 10th 04 08:25 PM


All times are GMT +1. The time now is 10:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement