Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move and Link3
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move and Link3
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").Resiz e(.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 "MCheru" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel arrows don't move black box but move the window | Excel Discussion (Misc queries) | |||
Find value and move to end of data in same row then move values in | Excel Programming | |||
excel-how to get the spreadsheet to move as I move the scroll tab | Excel Discussion (Misc queries) | |||
How do I stop making the spreadsht move when I move up/dwn/lt/rt? | Excel Worksheet Functions | |||
When I move scroll bar in excell the contents do not move with it | Excel Discussion (Misc queries) |