Change fixe ranges to dynamic from cell

[zealot777]

Hi,

I have this fabulous macro which copies a specified range after each iteration:

Sub ARUN()
Application.ScreenUpdating = False
Dim WorkingSht As Worksheet, i As Long, j As Long, k As Long, n As Long, newSht As Worksheet, DestnRow As Long
Dim SourceRng As Range, SourceWidth As Long, SourceHeight As Long, GapBetweenResults As Long
Set WorkingSht = ActiveSheet
'Set newSht = Sheets.Add(after:=Sheets(Sheets.Count))
Set newSht = Sheets("RESULTS")
newSht.Range("A:CG").ClearContents
GapBetweenResults = 0    'adjust to change the gap between results blocks.
DestnRow = 3
With WorkingSht
    .Activate  'optional
    Set SourceRng = .Range("A133:WVD160")
    SourceWidth = SourceRng.Columns.Count
    SourceHeight = SourceRng.Rows.Count
    GapBetweenResults = SourceHeight + GapBetweenResults
 
    For i = 170 To 309
         If i Mod 10 = 0 Then ActiveWorkbook.Save
        .Range("O39") = i
        For j = 1 To 24
            .Range("P39") = j
           
                    'do your result grabbing here:
                       Calculate
                    newSht.Cells(DestnRow, 1).Resize(SourceHeight, SourceWidth).Value = SourceRng.Value
 DestnRow = DestnRow + GapBetweenResults
            
        Next j
    Next i
 
End With
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub

I wonder if there is a way to change the code to copy dynamic range at each calculation?

can it be made to copy dynamic number of rows based on the text in a cell???

eg change A133:WVD160 to A133:b160 take it from cell a1

Dave

 

[Fluff]

Cross posted

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please provide the link(s)

 

[zealot777]

The only reason I posted here is beause it was not answered elsewhere - why punish someone for trying my luck on anothe rhopefully more helpful forum?

 

[zealot777]

here is my link - no replies at all there and NOW NOONE WILL HELP EM HERE becasue you labelled me as CROSS POSTER

Copy dynamic range

Hi, I have this fabulous macro which copies a specified range after each iteration: Sub ARUN() Application.ScreenUpdating = False Dim WorkingSht As Worksheet, i As Long, j As Long, k As Long, n As Long, newSht As Worksheet, DestnRow As Long Dim SourceRng As Range, SourceWidth As Long...

www.excelforum.com

 

[zealot777]

MAYBE THERE IS A SLIM CHNACE SOEMONE CAN HELP ME OUT WITH THESE DYNAMIC RANGES STUFF

 

[Joe4]

why punish someone for trying my luck on anothe rhopefully more helpful forum?

No one is punishing you, we are simply asking you to abide by the rules of the forum.
Please take a few minutes to read this, which explains the rationale for this policy on Cross-Posting, and why we have this policy.
When you read it, it should make perfect sense to anyone who values the efforts of those who volunteer their free time to help others.

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →

www.excelguru.ca