Hi Rob,
Try this 2 code, first sub code just need a few line code, but it slower
than next sub code, i suggest second sub code if you have large data
list to process
Sub Simple()
Dim Rng As Range
For Each Rng In Sheet1.UsedRange.Offset(1)
If Not Trim(Rng) = "" Then
Sheet2.Range(Rng).Copy Sheet3.Range("A" & 2 ^
20).End(xlUp).Offset(1)
End If
Next
End Sub
Sub LittleBetter()
Dim Rng, Data, Result(), Arr
Dim i As Long, j As Long, k As Long
Rng = Sheet1.UsedRange.Offset(1)
Data = Sheet2.UsedRange.Offset(1)
ReDim Result(UBound(Data, 1), 2)
For i = 1 To UBound(Rng, 1)
If Not Trim(Rng(i, 1)) = "" Then
Arr = Split(Replace(Rng(i, 1), "$", ""), ":")
For j = Mid(Arr(0), 2) To Mid(Arr(1), 2)
Result(k, 0) = Data(j - 1, 1)
Result(k, 1) = Data(j - 1, 2)
k = k + 1
Next
End If
Next
Sheet3.Range("A5").Resize(k, 2) = Result
End Sub
Rgds,
[dp]
On 21-02-2014 5:10, Rob Flott wrote:
The attached workbook has 3 sheets; Data, List and Report. The 'Data'
tab consists of two columns (A & B) of numbers from Row 2 to Row
3652. The 'List' tab has about 20 cells (from A2:A25) each with a
unique Range of cells pertaining to the Data tab. So for instance on
the List tab in cell A2 there is a range (A52:B159). The next range
is found in cell A5 and it covers A378:B485, and so forth.
i am struggling to write the correct code that would Copy the range in
Sheets("List").Range("A2") and Paste onto
Sheets("Report").Range("A5"). This would result in a series of 107
(Rows 159 - Row 52) numbers pasted onto sheets("Report"). Range(A5)
and another 107 numbers pasted onto sheets("Report"). Range(B5) .
Then using a Do Loop or Do Until loop to repeat this copy & paste
procedure for over one hundred ranges found on List tab.
Can anyone point me in the right direction on how to write this in VBA
code?
Thank you very much... Rob Flott
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be?
It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like
Please Help, Urgent, Need Help, Formula Problem, Code Problem, and
Need Advice will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners
and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google
Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/groups/opt_out.
