Simple Loop question

[fedude]

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

 

[Don Guillett]

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

 

[Nigel]

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

 

[fedude]

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

 

[fedude]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

Rick

 

[fedude]

Rick,

Essentially everything you see in the layout of SHEET1 comes from a single
row in SHEET2. The problem is that the mapping is rather haphazard. Below
is the code I use to copy the cells one at a time.

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("DG75")

'starting hole
sh1.Range("BF" & RowCount).Copy _
Destination:=sh2.Range("DG76")

'starting order
sh1.Range("BG" & RowCount).Copy _
Destination:=sh2.Range("DG77")

'fileame
sh1.Range("BI" & RowCount).Copy _
Destination:=sh2.Range("DG78")

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.

 

[Nigel]

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
(e-mail address removed)