PDA

View Full Version : [SOLVED] Need help enlarging a range of cells..



Michael 514
07-01-2005, 11:04 PM
Hi guys!!

LONG time no post!!

I have a question, that I am sure is soooo basic, it's taught in the first day of a VBA lesson.

But, seeing as how I have never coded in my life (and rely on keystroke macros!), I need some help!

I made a keystroke macro in Excel that will take the current selection, copy it to a new sheet, and then manipulate it.

It works well.

Here's my simple question:

How can I select a few more columns to the right?

Instead of selecting the entire selection by hand, I would like to select just the number of cells I need down a column (ie. B4 to B8), and then, I would like to have VBA not just copy the straight line B4 to B8, but rather, B4 to I8.

ie. the vertical group of cells, PLUS seven more to the right, to make a rectangle.

I have experimented with ActiveCells, Offsets, and similar things, and I can get it (in the example above) to jump to I8, and select just that one cell... But not the rectangle of B4 to I8.

I know this is so simple, but I have no idea how to do it.

In summary:
=========
Take the current bunch of selected cells in my row, and stretch the selection seven to the right. Then Copy.


Make sense?

Thank you!!

Mike

Jacob Hilderbrand
07-02-2005, 12:19 AM
If the range is always the same then:



Range("B4:I8").Copy

Bob Phillips
07-02-2005, 02:11 AM
Selection.Resize(,8).Copy

Michael 514
07-02-2005, 08:39 AM
Thanks guys.. (Girls)!

Selection.Resize(,8).Copy.... Worked!

Excellent.

Very cool!

I am going to have fun automating this spreadsheet!

Now, I have another question in this little project of mine, but I am not sure if I should keep asking in the same thread, or start a new one.

DRJ, please let me know the best protocol.

What I am doing is copying the selection to another sheet in my workbook.

At the end of my macro, I want to jump back to the originating sheet.

Right now, I hardcoded in the name. (ie. Copy the cells, move to my new tab, move back to the worksheet "June".

Wonderful.

But that means that in July, I'll have to recode the macro! :)

Is there a way to store the current tab name in a variable within the macro, so that I can then do:
Sheets(variable name in here).Select
instead of
Sheets("June").Select?

...this is going to be a fun weekend!

Thanks!

Mike

Norie
07-02-2005, 08:50 AM
Mike

Can we see your current code?

You don't actually need to select/activate sheets/ranges to copy/paste to/from them.

Michael 514
07-02-2005, 09:07 AM
Hi Norie!

The code goes to my temporary worksheet and clears it.
It then goes back to the originating sheet (June).
It takes that selection that the user chose.
Enlarges it by 8 columns, pastes in a blank worksheets.
Deletes 3 columns, resizes two.
Copies it to the clipboard (so the user can paste it into Word)
Goes back to the originating sheet (June)

I hope I remember how to use the VBA tags here... it's been a while...!


Sheets("Info").Select
Range("A1").Select
ActiveCell.Range("A1:AA151").Select
Selection.ClearContents
Range("A1").Select
Sheets("June").Select
Selection.Resize(, 11).Copy
'Selection.Copy
Sheets("Info").Select
ActiveSheet.Paste
ActiveCell.Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
Selection.ColumnWidth = 19.71
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 19
ActiveCell.Offset(0, -3).Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Sheets("June").Select
End Sub

Bob Phillips
07-02-2005, 09:56 AM
How about


Worksheets(Format(DateSerial(Year(Date), Month(Date), 0), "mmmm")).Activate

Michael 514
07-02-2005, 10:20 AM
Hi xld,

This is very creative!

The only problem is that it appears to me that this will select only the current month's tab.

Therefore, as we can see by today's example, even though it's July, our scheduler is still playing around in the June tab.

Hence, that will not work.

I believe I'll truly have to store that active worksheet somewhere, do my stuff, then go back to same active worksheet.

One day I should take a lesson on this stuff!!

Thank you!

Mike

Norie
07-02-2005, 11:14 AM
Mike

Like I said you don't need to activate/select a sheet/range to work with it.

So you could do what you want to do without moving sheets.

I took a look at the code and changed the first part of it. I couldn't work out the rest though.


Dim rng As Range
Sheets("Info").Range("A1:AA151").ClearContents
Set rng = Sheets("June").Selection.Resize(, 11)
rng.Copy Sheets("Info").Range("A1")

Sheets("Info").Columns("A:A").Delete Shift:=xlToLeft
' not sure exactly what the following is doing so have left it
ActiveCell.Offset(0, 2).Columns("A:A").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
ActiveCell.Offset(0, 1).Columns("A:B").EntireColumn.Select
Selection.Delete Shift:=xlToLeft
Selection.ColumnWidth = 19.71
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 19
ActiveCell.Offset(0, -3).Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy

Michael 514
07-02-2005, 11:24 AM
Hi Norie,

Oh, that is interesting.

I will have to check that out.

The part of the code that you didn't understand, simply takes three of the ten columns, and deletes them. Then it resizes two of them. Then it copies them back to the clipboard.

I'll check it out!

Thanks!

Mike

Bob Phillips
07-02-2005, 11:30 AM
The only problem is that it appears to me that this will select only the current month's tab.

Therefore, as we can see by today's example, even though it's July, our scheduler is still playing around in the June tab.

Hence, that will not work.

Becaiuse it uses the day of 0, it will always go back to last month's tab. So taday in July, it will go back to June.

Michael 514
07-02-2005, 11:33 AM
Interesting... but 95% of the time, the scheduler will be in the proper month.

And I was just looking at our other schedulers' tabs, and they are labelled even more weird. Some go by week. Some are in French(!) Etc., etc.

So, it really has to be able to return from the sheet from where they came...!

Thank you!

Bob Phillips
07-02-2005, 11:36 AM
Anotrher option.

Add this code



Public LastSheet As String

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
LastSheet = Sh.Name
End Sub

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code.

Then in your macro, add



Worksheets(ThisWorkbook.LastSheet).Activate

Norie
07-02-2005, 12:10 PM
Hi Norie,

Oh, that is interesting.

I will have to check that out.

The part of the code that you didn't understand, simply takes three of the ten columns, and deletes them. Then it resizes two of them. Then it copies them back to the clipboard.

I'll check it out!

Thanks!

Mike

Which columns?

Michael 514
07-02-2005, 02:49 PM
Thanks xld, that looks like it should do the trick!

Norie - What I am doing is taking columns 1-10, and deleting 2, 4, 6 and 7 (or something like that).

The reason is that I am taking a master schedule that has lots of details across each row, and only copying the important rows that will be broadcast in an E-mail.

At present (pre-macro days), the scheduler would copy the selection to a blank sheet, and then manually delete the 3 rows, and then re-copy it, ready for pasting..

I hope that makes sense!

My next big question is yet to come up :) .... and that entails setting up Excel so the darn button bars don't keep messing up everytime we change the name of the file. I know there is some information here somewhere...

Thanks all!

Mike