Linked cells

S

Seaside Mark

I have two spreadsheets. Spreadsheet One has a list of names.
Spreadsheet Two is formatted to print the first four names on a form.
I am trying to set things up so that I don't have to copy the name
into the cells when I want to print the next form.

I have linked each of the cells in Spreadsheet Two to a cells A1, A2
A3, and A4 in Spreadsheet One. I have tried this using both absolut
addresses and relative addresses.

What I want to do is delete cells A1 through A4 in Spreadsheet One
thereby moving the names in cells A5 through A 9 into cells A1 throug
A4, so that the four cells in Spreadsheet Two contain the next set o
names, allowing me to print the second form. When I delete cells A
through A4 in Spreadsheet One, I get #REF! in each of the cells i
Spreadsheet Two. The same thing happens when I delete Rows 1, 2, 3
and 4 instead of just the cells.

The brute force method is to create a macro where I:

Copy cells A1 through A4 in Spreadsheet One
Toggle over to Spreadsheet Two
Paste Special, Transpose, Values Only (to preserve the formatting o
Spreadsheet Two)
Print Spreadsheet Two
Toggle back to Spreadsheet One
Select the next four names
Repeat

Do you know of a way that I can do this without creating a macro
 
J

jeff

Hi,

Why not just slide the numbers up, replacing rows 1-4?

this little macro does it.

Sub SlideUP()
lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A5:A" & lastrow).Select
Selection.Cut
Range("A1").Select
ActiveSheet.Paste
End Sub

jeff
 
S

Seaside Mark

Thanks for this, but it still gives me the error on the
linked cells. I think the macro needs to do a copy and
paste instead of sliding it up.

Any thoughts?

I really appreciate the help.
 
S

Seaside Mark

Jeff,

Thanks for your help, but I just ended up writing a macro
to copy, paste special, print, delete, and repeat. I
figured that the time to do this was much less than
screwing around with a more "elegant" solution. Sorry to
take up your time. Here's the macro I wrote:

Sub Scorecard()
'
' Scorecard Macro
' Macro recorded 9/27/2004 by Mark Rosenberg
'
' Keyboard Shortcut: Ctrl+s
'
Range("A5:A8").Select
Selection.Copy
Windows("Scorecard.xls").Activate
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
Windows("Pairings 9-27-04 rev 2.xls").Activate
Rows("5:9").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A5").Select
End Sub

Thanks again,

Mark
 
D

Dave Peterson

Where in worksheet2? I chose A1.

Option Explicit
Sub testme()

Dim wksFrom As Worksheet
Dim wksTo As Worksheet
Dim rCtr As Long
Dim DestCell As Range

Set wksFrom = Workbooks("book1.xls").Worksheets("sheet1")
Set wksTo = Workbooks("book2.xls").Worksheets("sheet1")

Set DestCell = wksTo.Range("a1") 'where in your "To" worksheet???

With wksFrom
For rCtr = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row Step 4
'copy 4 cells at a time
.Cells(rCtr, "A").Resize(4, 1).Copy
DestCell.PasteSpecial Transpose:=True, Paste:=xlPasteValues
Application.Calculate 'just in case
wksTo.PrintOut preview:=True 'remove preview:=true after testing!
Next rCtr
End With
End Sub
 
Top