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 -
>
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
