How to select a range relative to the current cell 2

[kwbMitel]

I'm not very proficient at macro's and the help files are not helpful.

I'm trying to select cells in the same row relative to the current cell for the purpose of copying and transposing onto a new sheet but all entered into 1 column.

I've figured it out longhand but I don't want to adjust my ranges manually for the 50 rows that I am copying.

Here is what I am trying to do:

Range("A1").Select:

For X = 0 to 49

ActiveCell.Offset(X, 0).Activate
Range ???????? (need range command to select 200 cells wide starting with current cell)
Copy (I know how)
Select Sheet2 (I know How)
Range("A1").Select:
Paste and transpose (I know how)
Insert 200 Rows (this works)
Back to Sheet1
Next X

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[macropod]

Hi kwbMitel,

You should learn to use range objects instead of selections - such code runs far more efficiently.

If I understand the problem correctly, you're trying to copy & transpose a range of values from Sheet1 onto Sheet2. In that case, try:

Sub Demo()
Dim MyRange As Range, x As Integer
For x = 0 To 49
  Set MyRange = Range(Range("A1").Offset(x, 0).Address, Range("A1").Offset(x, 200).Address)
  MyRange.Copy
  Worksheets("Sheet2").Cells(1, x + 1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Next x
End Sub

Cheers
[MS MVP - Word]

 

[SkipVought]

Or this...

Sub Demo()
Dim r As Range

For Each r in Range([A1], [A1].end(xldown))
  r.Copy
  Worksheets("Sheet2").[A1].end(xldown).offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
Next 
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

Macropod: That looks like what I'm looking for. Can you elaborate on why Selecting is undesirable?

Range(Range("A1").Offset(x, 0).Address, Range("A1").Offset(x, 200).Address).select

SkipVought: If I understand your version it looks like it requires all rows and cells be populated with Data. This is not the case for me.

Here is a visual of what I am trying to do:

Sheet1
[tt]
Nov-18 Nov-19 Nov-20 Nov-21 Nov-22 Nov-23 Nov-24
Team 1 Game Practice Game Practice
Team 2 Game Practice Practice Game
Team 3 Practice Game Game Practice
Team 4 Game Practice Game Practice
Team 5 Game Practice Game Practice


Sheet2
Team 1 Nov-18 Game
Team 1 Nov-20 Practice
Team 1 Nov-22 Game
Team 1 Nov-24 Practice
Team 2 Nov-18 Game
Team 2 Nov-19 Practice
Team 2 Nov-21 Practice
Team 2 Nov-22 Game
Team 3 Nov-18 Practice
Team 3 Nov-19 Game
Team 3 Nov-21 Game
Team 3 Nov-22 Practice
Team 4 Nov-18 Game
Team 4 Nov-19 Practice
Team 4 Nov-21 Game
Team 4 Nov-23 Practice
Team 5 Nov-18 Game
Team 5 Nov-20 Practice
Team 5 Nov-22 Game
Team 5 Nov-24 Practice


[/tt]

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

really no need for VBA, using sheet functions.

1. Use named ranges

[tt]
Team EventDte EventTyp
Team 1 18-Nov Game
Team 1 20-Nov Practice
Team 1 22-Nov Game
Team 1 24-Nov Practice
Team 2 18-Nov Game
Team 2 19-Nov Practice
Team 2 21-Nov Practice
Team 2 22-Nov Game
Team 3 18-Nov Practice
Team 3 19-Nov Game
Team 3 21-Nov Game
Team 3 22-Nov Practice
Team 4 18-Nov Game
Team 4 19-Nov Practice
Team 4 21-Nov Game
Team 4 23-Nov Practice
Team 5 18-Nov Game
Team 5 20-Nov Practice
Team 5 22-Nov Game
Team 5 24-Nov Practice
[/tt]
on sheet 1
[tt]
=INDEX(OFFSET(Sheet2!$A$1,MATCH($A2,Team,0),1,COUNTIF(Team,$A2),2),MATCH(B$1,OFFSET(Sheet2!$A$1,MATCH($A2,Team,0),1,COUNTIF(Team,$A2),1),0),2)
[/tt]
Sheet1 results
[tt]
TM 18-Nov 19-Nov 20-Nov 21-Nov 22-Nov 23-Nov 24-Nov
Team 1 Game #N/A Practice #N/A Game #N/A Practice
Team 2 Game Practice #N/A Practice Game #N/A #N/A
Team 3 Practice Game #N/A Game Practice #N/A #N/A
Team 4 Game Practice #N/A Game #N/A Practice #N/A
[/tt]
You can code to show the #N/A as BLANK or use a conditional format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[macropod]

Hi kwbMitel,

Selecting ranges is undesirable because it's inefficient. One reason for that inefficency is the selection's need to work through the UI, which you'll often see in the form of screen flickering (unless you turn off screen updating). All that UI processing takes time. If you compare the run times for a long procedure involving selections and the same procedure using ranges, you'll find out just how much faster using ranges is.

I'm sure Skip and others can give more reasons for not using selections too.


Cheers
[MS MVP - Word]

 

[kwbMitel]

Skip, Sorry, I think you went the wrong Direction

Sheet 1 with Dates across the top is what I have, Sheet 2 is what I am converting to.

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[kwbMitel]

MacroPod: Thanks, I thought it would be an efficiency thing but your description is bang on. For the time being, I kinda like the flashy screen thing as I can somewhat follow the steps and it looks damn impressive to the uninitiated. Crude and ugly to skilled programmers granted.

Before I grant the star, can you help me find a seach term for the help files that would have led me to the command provided. I can't find it even knowing what it is. Nothing I have found shows the offset as you have provided it.

P.S. I'm using Excel 2003 if it matters



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[macropod]

Hi kwbMitel,

can you help me find a seach term for the help files that would have led me to the command provided

To which command do you refer?


Cheers
[MS MVP - Word]

 

[kwbMitel]

Range and Offset combined into 1

Set MyRange = Range(Range("A1").Offset(x, 0).Address, Range("A1").Offset(x, 200).Address)



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[macropod]

Hi kwbMitel,

If you look in the vba help file for 'Offset' or 'Offset Property' and choose 'Range', you'll be taken to the 'Offset Property (Range Object)' entry where you'll see its use explained. You could also search for 'Offset Property (Range Object)', but it'd be a bit much expecting someone not fully conversant with the vba help file to know what to look for in such detail. The second entry in the 'Example' link that goes with the help entry also shows the offset's use with a range. There's a link from the main article to the Range Object entry as well.

In the code I posted, I used the Range Object in two ways: one way was to specify a single cell address ("A1"); the other was to specify a cell range (which is what combining the "A1" references with the Offsets returned).


Cheers
[MS MVP - Word]

 

[kwbMitel]

Thanks again Macropod, this time with a well deserved star.

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

OK, easier yet.

faq68-5287.

My results from your example done in 2 minutes...
[tt]
Row Column Value
Team 1 11/18/2009 Game
Team 1 11/22/2009 Game
Team 2 11/18/2009 Game
Team 2 11/22/2009 Game
Team 3 11/19/2009 Game
Team 3 11/21/2009 Game
Team 4 11/18/2009 Game
Team 4 11/21/2009 Game
Team 1 11/20/2009 Practice
Team 1 11/24/2009 Practice
Team 2 11/19/2009 Practice
Team 2 11/21/2009 Practice
Team 3 11/18/2009 Practice
Team 3 11/22/2009 Practice
Team 4 11/19/2009 Practice
Team 4 11/23/2009 Practice
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

Skip, what's easy for you might not be so easy for me.

That being said, I'll give it a look.

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

The first time is often not easy, but its a handy tool to have in your bag or tricks.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

Skip: I think I'm getting a handle on using the pivot table.

Not enough to deviate much from the instructions but I am getting useful results.

The problem right now is the creation of new sheets. I can figure out how to place the pivot table within an existing sheet but the last step creates a new sheet. Normally this might not be a problem but the purpose this exercise is for error checking. If errors are found I correct them and regenerate the pivot table. Each time it re-generates it creates new sheets with incremental names to the last time.

I have a macro that generates the table, and copys to a sheet where the macro performs the error checking. The macro references multiple other sheets so names matter.

Can I control the name of the sheet generated or have some way of determining the sheet name so that the date can be copied and then the sheet deleted?


*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

It is a feature of Excel PivotTables that double-clicking on a data element, puts a 'snapshot' of the related source data on a separate sheet.

I have done this in the past: In the ThisWorkbook Object Code Sheet...

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Application.DisplayAlerts = False
    If Left(Sh.Name, 5) = "Sheet" Then Sh.Delete
    Application.DisplayAlerts = True
End Sub

CAVEAT: Sheets you want to keep must not begin with Sheet

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

All my Sheets are specifically named, no worries.

unfortunately Finding the "ThisWorkbook Object Code Sheet..." is another matter.

I'm so close it's very frustrating...



*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.

 

[SkipVought]

alt+F11 toggles between the sheet and VB Editor.

ctr+R displays VBA Project

In VBA Project double-click the ThisWorkbook Object for your workbook.

Paste the code in this Code Window, where in the Window Title, you see displayed Microsoft Visual Basic - Your Workbook Name - [ThisWorkbook (Code)].

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kwbMitel]

DOUBLE CLICK!

soooo close...

Done, It created an issue with the way I copied the data that I resolved using a Copy Destination syntax instead of Copy Range Select and Paste

Skip, this exercise greatly expands my toolbox.

You're a Star

*******************************************************
Occam's Razor - All things being equal, the simplest solution is the right one.