Move and Link3

[MCheru]

I want to change this code so that the same thing that happens in Sheet 2
will also happen in a 3rd worksheet called Sheet 3. Might you be able to
help?

Sub ML()
Dim rw As Long
Dim rngSource As Range
Dim rngDest As Range
Dim rng As Range

Set rngSource = Worksheets("Sheet 1").Range("A4:H200")
Set rngDest = Worksheets("Sheet 2").Range("B4")

With rngSource
Set rngDest = rngDest.Resize(.Rows.Count * 2, .Columns.Count)
End With

For Each rng In rngSource.Rows
rw = rw + 2
rngDest.Rows(rw).Value = rng.Value
Next

Set rngSource = Worksheets("Sheet 1").Range("Ak4:AK200")
rw = -1
For Each rng In rngSource
rw = rw + 2
rngDest(rw, 1).Formula = "=" & rng.Address(external:=True)
Next

End Sub

 

[Ronald R. Dodge, Jr.]

2 things would be of help.

The short dirty way is to assign worksheet to a worksheet object like

Set wshDestination = Thisworkbook.Worksheets("Sheet2")

The better way would be to modulate the code meaning the portion of the code
you want to be performed the say way on Sheet 3 as you want on Sheet 2 would
be put into a different procedure.

Example:

Sub ML()
Dim rw As Long, l_lngWorksheetNumber as Long
Dim rngSource As Range
Dim rngDest As Range
Dim rng As Range

Set rngSource = Worksheets("Sheet 1").Range("A4:H200")
With rngSource
For l_lngWorksheetNumber = 2 to Worksheets("Sheet
1").Parent.Worksheets.Count Step 1
Set rngDest =
Worksheets(l_lngWorksheetNumber).Range("B4").Resize(.Rows.Count * 2,
..Columns.Count)
Destination rngDest, rngSource
Next l_lngWorksheetNumber
End With
End Sub

Sub Destination(ByVal rngDest as Excel.Worksheet, ByVal rngSource as
Excel.Worksheet)
Dim rng As Excel.Range

Set rngDest = Worksheets("Sheet 2").Range("B4")

With rngSource
Set rngDest = rngDest.Resize(.Rows.Count * 2, .Columns.Count)
End With

For Each rng In rngSource.Rows
rw = rw + 2
rngDest.Rows(rw).Value = rng.Value
Next

Set rngSource = Worksheets("Sheet 1").Range("Ak4:AK200")
rw = -1
For Each rng In rngSource
rw = rw + 2
rngDest(rw, 1).Formula = "=" & rng.Address(external:=True)
Next
End Sub


Now the above code does assume that "Sheet1" is the first worksheet within
the workbook.

Also, in cause you wondered why I used the l_ in front of
lngWorksheetNumber, it's just one of my programming rules that I use to
distinguish rather if it's procedure level ('l_' for a variable declared
within a method, which the l stands for local), module level ('m_' for a
variable declared in the module level declarations with either the keyword
of Dim or Private), or global level ('g_' for a variable declared in the
module level declarations with the keyword of Public).

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000