Link Cells & Paste Special Macro

W

Workbook

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 it’s 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
 
J

JBeaucaire

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
 
P

Patrick Molloy

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
 
P

Patrick Molloy

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
 
W

Workbook

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
 
W

Workbook

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
 
P

Patrick Molloy

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
 
W

Workbook

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro 3
"=TEXT(RC[-1],"MMM")" 4
macro to copy rows that contain a cell with a certain number 3
how to use Solver in Excel VBA 1
Totals An Easier Way?? 5
smart tags 1
Macro that deletes and pastes filterd rows and then some 2
Different Results from the Same Macro 3

Top