I have 2 sheets in a workbook. Sheet1 contains 25 rows of data. Sheet2
contains a column of 5 cells that are the basis for a large calculation
within that sheet. Today I'm manually loading up the 5 cells and then
running a macro to print out the results.

I want a routine that loops through the 25 rows of Sheet1 and for each row,
copies 5 of the cells (in that row) into the appropriate cells in Sheet2 and
then run the print macro. I'd like to run this routine from Sheet2 if that
matters.

The data in Sheet1 is in rows and the destination of the values in Sheet2 is
in a column

The macro recorder is your friend. Show us your efforts for comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fedude" wrote in message
...
I have 2 sheets in a workbook. Sheet1 contains 25 rows of data. Sheet2
contains a column of 5 cells that are the basis for a large calculation
within that sheet. Today I'm manually loading up the 5 cells and then
running a macro to print out the results.

I want a routine that loops through the 25 rows of Sheet1 and for each
row,
copies 5 of the cells (in that row) into the appropriate cells in Sheet2
and
then run the print macro. I'd like to run this routine from Sheet2 if
that
matters.

The data in Sheet1 is in rows and the destination of the values in Sheet2
is
in a column

OK, I get the hint. Here is what I have. It seems overly complicated, but
unfortunately, the rows don't match up for a nice "transpose". I'd
appreciate any help in simplifying the COPY:

--------------------------------------------------------------------------
'--------------------------------------------------
' Routine to print off all 32 double-sided
' scorecards in a single pdf file
'--------------------------------------------------
Sub PrintCards()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim RowCount As Integer

Set sh1 = Worksheets("Sheet2") 'source
Set sh2 = Worksheets("Sheet1") 'destination

For RowCount = 4 To 40
If sh1.Range("AS" & RowCount) < "" Then
'copy the names
sh1.Range("AS" & RowCount).Copy _
Destination:=sh2.Range("DG71")
sh1.Range("AT" & RowCount).Copy _
Destination:=sh2.Range("DH71")
sh1.Range("AV" & RowCount).Copy _
Destination:=sh2.Range("DG72")
sh1.Range("AW" & RowCount).Copy _
Destination:=sh2.Range("DH72")
sh1.Range("AY" & RowCount).Copy _
Destination:=sh2.Range("DG73")
sh1.Range("AZ" & RowCount).Copy _
Destination:=sh2.Range("DH73")
sh1.Range("BB" & RowCount).Copy _
Destination:=sh2.Range("DG74")
sh1.Range("BC" & RowCount).Copy _
Destination:=sh2.Range("DH74")
'copy the handicaps
sh1.Range("AU" & RowCount).Copy _
Destination:=sh2.Range("DK71")
sh1.Range("AX" & RowCount).Copy _
Destination:=sh2.Range("DK72")
sh1.Range("BA" & RowCount).Copy _
Destination:=sh2.Range("DK73")
sh1.Range("BD" & RowCount).Copy _
Destination:=sh2.Range("DK74")
'course
sh1.Range("BE" & RowCount).Copy _
Destination:=sh2.Range("DE75")
'starting hole
sh1.Range("BF" & RowCount).Copy _
Destination:=sh2.Range("DE76")
'starting order
sh1.Range("BG" & RowCount).Copy _
Destination:=sh2.Range("DE77")
'fileame
sh1.Range("BI" & RowCount).Copy _
Destination:=sh2.Range("DE78")

'code to print scoresheet

End If

Next

End Sub
--------------------------------------------------------------


"Don Guillett" wrote:

The macro recorder is your friend. Show us your efforts for comments

I actually thought I had a handle on things from your first posting; but am
totally confused after reading this one. Your first post said you have 25
rows of data on Sheet1 of which 5 of its cells per row need to be placed in
a column on Sheet2. However, in your posted code, I can't 5 or 25 of
anything. Can you clarify your Sheet1 data layout and where in Sheet2 what
cells go where? Perhaps if you show us a couple of rows of data from Sheet 1
and show us what Column/Row you want them to go in, maybe your question
would become clearer.

Rick


"fedude" wrote in message
...
OK, I get the hint. Here is what I have. It seems overly complicated,
but
unfortunately, the rows don't match up for a nice "transpose". I'd
appreciate any help in simplifying the COPY:

--------------------------------------------------------------------------
'--------------------------------------------------
' Routine to print off all 32 double-sided
' scorecards in a single pdf file
'--------------------------------------------------
Sub PrintCards()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim RowCount As Integer

Set sh1 = Worksheets("Sheet2") 'source
Set sh2 = Worksheets("Sheet1") 'destination

For RowCount = 4 To 40
If sh1.Range("AS" & RowCount) < "" Then
'copy the names
sh1.Range("AS" & RowCount).Copy _
Destination:=sh2.Range("DG71")
sh1.Range("AT" & RowCount).Copy _
Destination:=sh2.Range("DH71")
sh1.Range("AV" & RowCount).Copy _
Destination:=sh2.Range("DG72")
sh1.Range("AW" & RowCount).Copy _
Destination:=sh2.Range("DH72")
sh1.Range("AY" & RowCount).Copy _
Destination:=sh2.Range("DG73")
sh1.Range("AZ" & RowCount).Copy _
Destination:=sh2.Range("DH73")
sh1.Range("BB" & RowCount).Copy _
Destination:=sh2.Range("DG74")
sh1.Range("BC" & RowCount).Copy _
Destination:=sh2.Range("DH74")
'copy the handicaps
sh1.Range("AU" & RowCount).Copy _
Destination:=sh2.Range("DK71")
sh1.Range("AX" & RowCount).Copy _
Destination:=sh2.Range("DK72")
sh1.Range("BA" & RowCount).Copy _
Destination:=sh2.Range("DK73")
sh1.Range("BD" & RowCount).Copy _
Destination:=sh2.Range("DK74")
'course
sh1.Range("BE" & RowCount).Copy _
Destination:=sh2.Range("DE75")
'starting hole
sh1.Range("BF" & RowCount).Copy _
Destination:=sh2.Range("DE76")
'starting order
sh1.Range("BG" & RowCount).Copy _
Destination:=sh2.Range("DE77")
'fileame
sh1.Range("BI" & RowCount).Copy _
Destination:=sh2.Range("DE78")

'code to print scoresheet

End If

Next

End Sub
--------------------------------------------------------------


"Don Guillett" wrote:

The macro recorder is your friend. Show us your efforts for comments

Sorry Rick, I was stupidly trying to simplify the question. The code
represents the real problem. The application is a scoring spreadsheet for a
golf league. We have 36 members (stored in rows in sheet2). Sheet1 is a
scorecard for the next week. The print range makes the scorecard
double-sided. I want to pre-populate the scorecard with the players names,
handicap, starting hole, etc. The end result is a PDF file with all 36
scorecards. The PDF file will be printed later.

The problem is that the data in the rows do not correlate 1:1 with the
entries in Sheet1. S I had to resort to individual COPY operations. Plus,
I'm a VB noob.

Here is the layout of SHEET1:
................................[DG]..........[DH]............[DK]
[70]...........................Last...........First... ......Handicap
[71]Player 1A:...............Bohme..........Bob.............. ......8
]72]Player1B:................Quester........John...... ............19
[73]Player2A:...............Robinson.......Steve...... ............19
[74]Player 2B................Cosgrove.......Bob.............. ....22
[75]Course:......................1
[76]Starting Hole:.............10
[77Order........................A
[78]Filename:..........Bohme-Quester.pdf

Is there a better way to do the copy of individual cells from one sheet to
another. I'm not opposed to changing the layout of SHEET1 if it will make
the code a lot simpler.

HTH.

Okay, can you now show how the data you just showed us for Sheet1 would look
over in Sheet2 (what goes where)?

Rick


"fedude" wrote in message
...
Sorry Rick, I was stupidly trying to simplify the question. The code
represents the real problem. The application is a scoring spreadsheet for
a
golf league. We have 36 members (stored in rows in sheet2). Sheet1 is a
scorecard for the next week. The print range makes the scorecard
double-sided. I want to pre-populate the scorecard with the players
names,
handicap, starting hole, etc. The end result is a PDF file with all 36
scorecards. The PDF file will be printed later.

The problem is that the data in the rows do not correlate 1:1 with the
entries in Sheet1. S I had to resort to individual COPY operations.
Plus,
I'm a VB noob.

Here is the layout of SHEET1:
...............................[DG]..........[DH]............[DK]
[70]...........................Last...........First... ......Handicap
[71]Player 1A:...............Bohme..........Bob.............. ......8
]72]Player1B:................Quester........John...... ............19
[73]Player2A:...............Robinson.......Steve...... ............19
[74]Player 2B................Cosgrove.......Bob.............. ....22
[75]Course:......................1
[76]Starting Hole:.............10
[77Order........................A
[78]Filename:..........Bohme-Quester.pdf

Is there a better way to do the copy of individual cells from one sheet to
another. I'm not opposed to changing the layout of SHEET1 if it will make
the code a lot simpler.

HTH.

Sub CopyCalc()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, c1 As Range, c As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

' set top left cell for range on sheet1 and sheet2
Set r1 = sh1.Range("A1")
Set c1 = sh2.Range("A1")

For c = r1.Row - 1 To r1.Row + 23
Range(r1.Offset(c, 0), r1.Offset(c, 4)).Copy
c1.PasteSpecial Transpose:=True

'code to print you output

Next
End Sub

--

Regards,
Nigel




"fedude" wrote in message
...
I have 2 sheets in a workbook. Sheet1 contains 25 rows of data. Sheet2
contains a column of 5 cells that are the basis for a large calculation
within that sheet. Today I'm manually loading up the 5 cells and then
running a macro to print out the results.

I want a routine that loops through the 25 rows of Sheet1 and for each
row,
copies 5 of the cells (in that row) into the appropriate cells in Sheet2
and
then run the print macro. I'd like to run this routine from Sheet2 if
that
matters.

The data in Sheet1 is in rows and the destination of the values in Sheet2
is
in a column

Thanks Nigel. This got me off to a good start. Unfortunately the rows and
column don't match up for a nice "transpose".

"Nigel" wrote:

Sub CopyCalc()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, c1 As Range, c As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

' set top left cell for range on sheet1 and sheet2
Set r1 = sh1.Range("A1")
Set c1 = sh2.Range("A1")

For c = r1.Row - 1 To r1.Row + 23
Range(r1.Offset(c, 0), r1.Offset(c, 4)).Copy
c1.PasteSpecial Transpose:=True

'code to print you output

Next
End Sub

--

Regards,
Nigel




"fedude" wrote in message
...
I have 2 sheets in a workbook. Sheet1 contains 25 rows of data. Sheet2
contains a column of 5 cells that are the basis for a large calculation
within that sheet. Today I'm manually loading up the 5 cells and then
running a macro to print out the results.

I want a routine that loops through the 25 rows of Sheet1 and for each
row,
copies 5 of the cells (in that row) into the appropriate cells in Sheet2
and
then run the print macro. I'd like to run this routine from Sheet2 if
that
matters.

The data in Sheet1 is in rows and the destination of the values in Sheet2
is
in a column

Hmm, I suppose the other responders to your OP are finding your requirement
difficult to interpret. You get what you ask for.

Good luck

Cheers

--

Regards,
Nigel




"fedude" wrote in message
...
Thanks Nigel. This got me off to a good start. Unfortunately the rows
and
column don't match up for a nice "transpose".

"Nigel" wrote:

Sub CopyCalc()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim r1 As Range, c1 As Range, c As Long

Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet2")

' set top left cell for range on sheet1 and sheet2
Set r1 = sh1.Range("A1")
Set c1 = sh2.Range("A1")

For c = r1.Row - 1 To r1.Row + 23
Range(r1.Offset(c, 0), r1.Offset(c, 4)).Copy
c1.PasteSpecial Transpose:=True

'code to print you output

Next
End Sub

--

Regards,
Nigel




"fedude" wrote in message
...
I have 2 sheets in a workbook. Sheet1 contains 25 rows of data.
Sheet2
contains a column of 5 cells that are the basis for a large calculation
within that sheet. Today I'm manually loading up the 5 cells and then
running a macro to print out the results.

I want a routine that loops through the 25 rows of Sheet1 and for each
row,
copies 5 of the cells (in that row) into the appropriate cells in
Sheet2
and
then run the print macro. I'd like to run this routine from Sheet2 if
that
matters.

The data in Sheet1 is in rows and the destination of the values in
Sheet2
is
in a column