CurrentRegion set

[hamking01]

I have an excel sheet that contains a form that keeps repeating itself in the same format vertically. I'm trying to copy each of the forms data onto a seperate sheet listing each form my row.

Will I be able to the the following via VBA:
If I click on the top left corner of each form I click a button that sets that cell as the Current Region (There a 5 blank rows between each form). From the clicked cell I need it to move 5 columns to the right and 2 rows down and copy the data from the cell to cell A100. It will then move from starting point cell of form and move to different cell and copy data to cell B100.

Basically I'm trying to copy info from each form into a row format, where each row will have the info for one form. Can anyone tell me how this can be done?

*I've also posted this in Access modules VBA coding earlier, but found this forum better suited.

 

[SkipVought]

Hi,

   rIn = 1
   With Sheet1
      Do
         With .Cells(rIn, 1)
            If .Value > 0 Then
               Set rng = .CurrentRegion
               With Sheet2
                  If .[A1] > 0 Then
                     If .[A1].CurrentRegion.Rows.Count > 1 Then
                        rOut = .[A1].CurrentRegion.Rows.Count + 1
                     Else
                        rOut = 2
                     End If
                  Else
                     rOut = 1
                  End If
                  c = 1
                  For Each r In rng
                     .Cells(rOut, c).Value = r.Value
                     c = c + 1
                  Next
               End With
            End If
            rIn = .End(xlDown).End(xlDown).Row
         End With
      Loop Until rIn > .UsedRange.Row + .UsedRange.Rows.Count - 1
   End With

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[hamking01]

Kinda new at this, doesn't the above just take the from of the currentregion and copy it to sheet2 in a row format.

I'm trying to set a form as current region and have top left cell set as "point". In the form, the data I need are in specific cells. Such as, if [A1] were set as "point" I neede cells offset (5,1) and (9, 3) from A1. The offset cells need to be copied to new sheet in row format.

 

[hamking01]

This is what I've tried so far:

'Item_NOtitle
Range("E1").Select
ActiveCell.Offset(1, 0).Select
Selection.Copy
Range("A21").Select
ActiveSheet.Paste
'Item_value
Range("E1").Select
ActiveCell.Offset(1, 1).Select
Selection.Copy
Range("A22").Select
ActiveSheet.Paste
'DescrTitle
Range("E1").Select
ActiveCell.Offset(2, 0).Select
Selection.Copy
Range("B21").Select
ActiveSheet.Paste
'DescrValue
Range("E1").Select
ActiveCell.Offset(2, 1).Select
Selection.Copy
Range("B22").Select
ActiveSheet.Paste

Currently E1 would be set as the starting point for this form. The titles and values are always the same points from the starting points. So for example the next form in the sheet would have starting point at E50. Is there a way to click a cell then a button to set the cell as starting point. So I don't have to keep typing 'Range("E1").Select'. The form has 15 titles and values, after awhile it get redundant.

Currently, I'm must pasting the data into a row just below the form. I'll most likely change it to Skip's suggestion into Sheet2 with A1].CurrentRegion.Rows.Count + 1. Before than could someone tell me if set a cell as a "base" cell is possible and if my method above is efficient, could there be a better way?

 

[SkipVought]

OK Maybe i misunderstood.

Here's my scenerio...

Sheet1: has 3 forms, each 2 rows by 2 columns (could be any number of rows & columns as long as they have CONTIGUOUS DATA...

A4:b5

A9:b10

A13:b14

The Output is on Sheet2
3 Rows of data, one row for each form; 4 columns of data one column for each cell in each form

Sheet1:
[tt]
1 4
2 3


2 3
4 5




3 4
5 6
[/tt]

Sheet2:
[tt]
1 4 2 3
2 3 4 5
3 4 5 6
[/tt]
What am I missing?

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[hamking01]

Something similar, but each form does not have continuous data. Layout for each form something like below:

D E F G
1 |---------------------------------
2 |picture | item# | 3340| |
3 | | descr | ****| |
4 |--------| | price| 3.5 |
5
6
7 |---------------------------------
another form continues here

Each form does have the same format but they are not continuous data. So in my VBA code i've set E1 as "point" copy cell to A5 "offset" by (0, 1) to F1 same for descr and price. So would this still be viable?

 

[SkipVought]

CONTIGUOUS means that I can select any cell with data, hit the [Current Region] icon and the entire data area will be selected for a single form.

It is not CONTINUOUS.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[hamking01]

Sorry, then i guess it is contiguous. So how would I go about selecting specific cells to copy.

 

[SkipVought]

The code I posted "finds" each form on Sheet1, sets the current region for the form and then writes each value in the current region to the next row on Sheet2.

Set up a test sheet, run the code and observe what happens. Then we can modify to suite.

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[hamking01]

k, tried but. put the code you provide in a macro. ran it but nothing happens on sheet2. Any ideas?

 

[SkipVought]

Please post the code that you are using.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[hamking01]

i just copied everything in ur code, put it between the subs of a macro and ran it.

 

[SkipVought]

In the VB Editor, Project Explorer

the sheet OBJECT (NOT what's in the PARENTHESES)

Sheet1 should be the "copy from" sheet when running
Sheet2 will be the "copy to" sheet

regardless what your Sheet Tab names are.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}