Paul, I got sidetracked with another VB nightmare, but wanted to say
It worked (and taught me something)!
THANK YOU! THANK YOU!
On Jan 16, 10:08 am, Paul Schreiner <schreiner_p...@att.net> wrote:
> Let me put on my "teacher" hat and see if I can help.
> When you look in the VBA editor, in the Project Explorer, you see
> you workbook listed as well as all the sheets and one sheet called
> ThisWorkBook.
> Now, the thing to recognize is that macros, (or subs) written in any
> of these sheets (except for ThisworkBook) are normally available only
> to the sheet. Not to other sheets, hence the keyword "Private Sub".
>
> Subs written in ThisWorkBook or in a Module are normally available to
> all sheets.
>
> As in your example, the "click" event is only available to the sheet with
> the button, which makes sense, why would you want to execute a click event
> for a button you can't see...
>
> Now, the reason your macro only works if you have cell C39 selected is
> because you TOLD it to when you said:
> "Application"(excel)".ActiveCell"(the currently selected cell)
> you could change "Activecell" to "Cells(39,3)" (row 39, column 3)
> or even Range("C39") and it wouldn't care what cell you have selected.
>
> When you record a macro, Excel records that you changed sheets, but cannot
> anticipate what you're going to do when you get there, so it records each
> step.
> Since you DO know what you were intending to do, you can combine steps.
> For instance:
> Worksheets("Sheet1").Activate
> Range("A1:M7").Select
> Selection.Copy
> can be reduced to:
> Worksheets("Sheet1").Range("A1:M7").Copy
>
> Now, the Paste method is a little screwy.
> Even if you say:
> Worksheets("Performance Attribute Weights").Paste Destination:=Range("A7:M13")
> It will paste it to whatever sheet is currently active.
>
> so... you COULD reduce your macro to:
>
> Private Sub CommandButton1_Click()
> TestVal = True
> If sheets("Performance Attribute Weights").cells(39,3).value = 4 Then
> Worksheets("Sheet1").Range("A1:M7").Copy
> elseIf sheets("Performance Attribute Weights").cells(39,3).value = 5 Then
> Worksheets("Sheet1").Range("A9:N16").Copy
> elseIf sheets("Performance Attribute Weights").cells(39,3).value = 6 Then
>
> Worksheets("Sheet1").Range("A18:P26").Copy
> elseIf sheets("Performance Attribute Weights").cells(39,3).value = 7 Then
> Worksheets("Sheet1").Range("A28:R37").Copy
> else
> TestVal = False
> End If
>
> If (TestVal) then
> Worksheets("Performance Attribute Weights").Activate
> ActiveSheet.Paste Destination:=Range("A7")
> End If
> End Sub
>
> (I added TestVal so that if the value is not 4-7, then nothing happens)
>
> Now.. to your last comment:
> "I'm not even sure how to implement what you just wrote".
>
> What I wrote was a stand-alone "sub".
> Normally, I would put it in a Module.
> Then, to run it from your button, change the click event to:
>
> Private Sub CommandButton1_Click()
> Btn_Copy
> End Sub
>
> What you did by recording the macro and modifying it is a VERY good start.
> But to speed things up and to keep the screen from jumping back and forth...
> THAT's what makes the macros look GOOD....
>
> hope this helps.
>
> Paul
>
>
>
> ----- Original Message ----
> > From: Thunderwhelmed <april.hil...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thursday, January 15, 2009 11:41:13 AM
> > Subject: $$Excel-Macros$$ Re: IF-THEN macro
>
> > Hi Paul, Thanks so much! It's astonishing to me that this constitutes
> > a "quick" macro for you!
>
> > I guess to answer your questions... I'm using Excel 2007, and
> > shamefully, I really don't know how to write macros... I did come up
> > with the script below though it's in conflict with your warning
> > against activating sheets. Essentially, it's a hodgepodge of stuff I
> > figured out by using the "record macro" tool and stepping into it.
>
> > Private Sub CommandButton1_Click()
>
> > If Application.ActiveCell = 4 Then
> > Worksheets("Sheet1").Activate
> > Range("A1:M7").Select
> > Selection.Copy
> > Worksheets("Performance Attribute Weights").Activate
> > ActiveSheet.Paste Destination:=Range("A7:M13")
> > End If
>
> > If Application.ActiveCell = 5 Then
> > Worksheets("Sheet1").Activate
> > Range("A9:N16").Select
> > Selection.Copy
> > Worksheets("Performance Attribute Weights").Activate
> > ActiveSheet.Paste Destination:=Range("A7:N14")
> > End If
>
> > If Application.ActiveCell = 6 Then
> > Worksheets("Sheet1").Activate
> > Range("A18:P26").Select
> > Selection.Copy
> > Worksheets("Performance Attribute Weights").Activate
> > ActiveSheet.Paste Destination:=Range("A7:P15")
> > End If
>
> > If Application.ActiveCell = 7 Then
> > Worksheets("Sheet1").Activate
> > Range("A28:R37").Select
> > Selection.Copy
> > Worksheets("Performance Attribute Weights").Activate
> > ActiveSheet.Paste Destination:=Range("A7:R21")
> > End If
>
> > End Sub
>
> > As you can see, the ranges to be copied/pasted differ based on the
> > value of C39. The problem with this (it actually works) is that C39
> > must be active. Otherwise, it's a no-go.
>
> > ARGH, hate to be a pest, I'm not even sure how to implement what you
> > just wrote!
>
> > On Jan 14, 5:05 am, Paul Schreiner wrote:
> > > Not sure how much help you're looking for.
> > > You said that you "don't know the VB language".
> > > Does that mean that you don't know how to use the VB editor?
> > > do you know how to write a macro?
> > > Can you create your command button and assign a macro?
> > > What version of Excel are you using? It won't matter for the VB, but it
> > > will help in deciding how to describe it to you.
>
> > > Another "technique":
> > > You describe activating a sheet, copying a range, activating another
> > > sheet and
> > pasting the data.
> > > That is actually CPU intensive and causes a lot of screen flicker.
> > > I prefer to copy the data from one sheet to another without selecting
> > > them.
>
> > > I wrote this quick macro:
>
> > > Option Explicit
> > > Sub Btn_Copy()
> > > Dim FirstRow, LastRow, DestRow, R
> > > Dim FirstCol, LastCol, DestCol, C
> > > Select Case Sheets("Sheet3").Range("C39").Value
> > > Case 4
> > > FirstRow = 1
> > > LastRow = 7
> > > FirstCol = 1
> > > LastCol = 13
> > > Case 5
> > > FirstRow = 9
> > > LastRow = 15
> > > FirstCol = 1
> > > LastCol = 14
> > > Case 6
> > > FirstRow = 17
> > > LastRow = 23
> > > FirstCol = 0
> > > LastCol = 0
> > > Case 7
> > > FirstRow = 25
> > > LastRow = 31
> > > FirstCol = 0
> > > LastCol = 0
> > > Case 8
> > > FirstRow = 33
> > > LastRow = 39
> > > FirstCol = 0
> > > LastCol = 0
> > > Case Else
> > > FirstRow = 0
> > > LastRow = 0
> > > FirstCol = 0
> > > LastCol = 0
> > > End Select
> > > If (FirstRow > 0) Then
> > > DestRow = 7 - 1 ' (7) represents the first row in the
> > > destination,
> > (-1) prepares it for incrementing in the loop
> > > For R = FirstRow To LastRow
> > > DestRow = DestRow + 1
> > > DestCol = 1 - 1
> > > For C = FirstCol To LastCol
> > > DestCol = DestCol + 1
> > > Sheets("Sheet2").Cells(DestRow, DestCol) =
> > Sheets("Sheet3").Cells(R, C)
> > > Next C
> > > Next R
> > > End If
> > > End Sub
>
> > > If I KNEW that you were always going to copy 7 rows, then I could
> > > calculate it
> > mathematically.
> > > But your columns were different for C39=4 (M) and C39=5 (N).
> > > Therefore, I concluded that the rows/columns weren't consistent.
>
> > > If you need more help with putting the VBA code into a module and
> > > assigning it
> > to a button,
> > > then we've got a lot more work to do!
>
> > > hth,
>
> > > Paul
>
> > > ----- Original Message ----
> > > > From: Thunderwhelmed
> > > > To: MS EXCEL AND VBA MACROS
> > > > Sent: Tuesday, January 13, 2009 7:30:14 PM
> > > > Subject: $$Excel-Macros$$ IF-THEN macro
>
> > > > Hi all,
>
> > > > I have a worksheet that contains a column of 8 dropdown lists (combo-
> > > > boxes) from a range in a different sheet. I have the (numerical)
> > > > results of each selection set up to display in a hidden column. In
> > > > another cell, I used the COUNTIF function to count the results of all
> > > > selections >0 (0=no selection).
>
> > > > What I need to do is, based on that count, use a command button to
> > > > activate another sheet in the same workbook, select a range of cells,
> > > > then copy + paste into yet another sheet.
>
> > > > Basically:
>
> > > > if Cell C39=4, then
> > > > Activate Sheet3
> > > > Select range A1:M7
> > > > Copy range
> > > > Activate Sheet2
> > > > Paste range into Sheet2, starting at cell A7.
>
> > > > --OR--
>
> > > > if Cell C39=5, then
> > > > Activate Sheet3
> > > > Select range A9:N15
> > > > Copy range
> > > > Activate Sheet2
> > > > Paste range into Sheet2, starting at cell A7.
>
> > > > etc, etc, when C39 = 6, 7, or 8
>
> > > > I know it's all pretty much there, but I don't know the VB language.
>
> > > > I would really appreciate some help! Thank you in advance!
>
> > > > April
>
> > > > PS, does anyone know how to lock the contents of an entire sheet once
> > > > that command button has been pressed? thanks!- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
