Selecting ranges in a workbook and pasting into another 
Author Message
 Selecting ranges in a workbook and pasting into another

Example:
workbook A Sheet1:
R1C1   A          B          C
1       50         175        350  
2      100         225        175  
3      125          50         75
4      275         450        600
5      ====        ====       ====
6       50         125         125
7      100         100         125
8      150         225         250  
9      ====        ====        ====  
What is needed is VBA code that will be able to select
range A1:C5 based on the value of C5 equal to =  and then
cut and paste into workbook A Sheet2.  Then select
workbook A cell A6:C9 based on the value of C9 equal to =
and performing the same cut and paste into workbook A
sheet3.  The code needs to loop thru all data in workbook
A sheet1.
Thanks in advance
Harvey


Wed, 14 Apr 2004 03:42:09 GMT  
 Selecting ranges in a workbook and pasting into another
The following code will copy each segment to a new sheet, adding sheets
where necessary (The CreateSheet function is from a Chip Pearson posting
of Sept 3/01.)

Sub CopyRows()
Dim c As Range
Dim cStart As String
Dim cEnd As String
Dim i As Integer

cStart = Range("A1").Address
i = 2
Worksheets("Sheet1").Activate
For Each c In Range("C1:C" & _
  ActiveSheet.UsedRange.Rows.Count)
  If Left(c.Value, 1) = "=" Then
    cEnd = c.Offset(-1, 0).Address
    CreateSheet ("Sheet" & i)
    Worksheets("Sheet1").Range(cStart & ":" & cEnd) _
      .Copy Destination:=Worksheets("Sheet" & i) _
        .Range("A1")
   i = i + 1
    cStart = c.Offset(1, -2).Address
  End If
Next
End Sub

Function CreateSheet(SheetName As String) As Excel.Worksheet
    On Error GoTo ErrH:
    Set CreateSheet = ThisWorkbook.Worksheets(SheetName)
    Exit Function
ErrH:
    Set CreateSheet = ThisWorkbook.Worksheets.Add
    CreateSheet.Name = SheetName
End Function

Quote:

> Example:
> workbook A Sheet1:
> R1C1   A          B          C
> 1       50         175        350
> 2      100         225        175
> 3      125          50         75
> 4      275         450        600
> 5      ====        ====       ====
> 6       50         125         125
> 7      100         100         125
> 8      150         225         250
> 9      ====        ====        ====
> What is needed is VBA code that will be able to select
> range A1:C5 based on the value of C5 equal to =  and then
> cut and paste into workbook A Sheet2.  Then select
> workbook A cell A6:C9 based on the value of C9 equal to =
> and performing the same cut and paste into workbook A
> sheet3.  The code needs to loop thru all data in workbook
> A sheet1.
> Thanks in advance
> Harvey



Wed, 14 Apr 2004 05:22:39 GMT  
 Selecting ranges in a workbook and pasting into another
Thanks a bundle!! Solved my problem.
Harvey
Quote:
>-----Original Message-----
>The following code will copy each segment to a new sheet,
adding sheets
>where necessary (The CreateSheet function is from a Chip
Pearson posting
>of Sept 3/01.)

>Sub CopyRows()
>Dim c As Range
>Dim cStart As String
>Dim cEnd As String
>Dim i As Integer

>cStart = Range("A1").Address
>i = 2
>Worksheets("Sheet1").Activate
>For Each c In Range("C1:C" & _
>  ActiveSheet.UsedRange.Rows.Count)
>  If Left(c.Value, 1) = "=" Then
>    cEnd = c.Offset(-1, 0).Address
>    CreateSheet ("Sheet" & i)
>    Worksheets("Sheet1").Range(cStart & ":" & cEnd) _
>      .Copy Destination:=Worksheets("Sheet" & i) _
>        .Range("A1")
>   i = i + 1
>    cStart = c.Offset(1, -2).Address
>  End If
>Next
>End Sub

>Function CreateSheet(SheetName As String) As
Excel.Worksheet
>    On Error GoTo ErrH:
>    Set CreateSheet = ThisWorkbook.Worksheets(SheetName)
>    Exit Function
>ErrH:
>    Set CreateSheet = ThisWorkbook.Worksheets.Add
>    CreateSheet.Name = SheetName
>End Function


>> Example:
>> workbook A Sheet1:
>> R1C1   A          B          C
>> 1       50         175        350
>> 2      100         225        175
>> 3      125          50         75
>> 4      275         450        600
>> 5      ====        ====       ====
>> 6       50         125         125
>> 7      100         100         125
>> 8      150         225         250
>> 9      ====        ====        ====
>> What is needed is VBA code that will be able to select
>> range A1:C5 based on the value of C5 equal to =  and
then
>> cut and paste into workbook A Sheet2.  Then select
>> workbook A cell A6:C9 based on the value of C9 equal to
=
>> and performing the same cut and paste into workbook A
>> sheet3.  The code needs to loop thru all data in
workbook
>> A sheet1.
>> Thanks in advance
>> Harvey
>.



Thu, 15 Apr 2004 00:53:14 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Using VBA to select a highlighted range and paste values to another workbook

2. Pasting Range from Excel 2002 via VBA to Word causes a New Workbook to open

3. Macro to Open Workbook, Copy/Paste Selected Text

4. Select Range to paste- looking for easier method

5. Paste Special w/o Selecting Range

6. vba-select multiple disconnected ranges - to copy paste special values in place

7. VBA print code to select range + pasted chart

8. How to select range from another workbook

9. Can REFEDIT select range from different workbook?

10. Select range in another workbook


 
Powered by phpBB® Forum Software © phpBB Group