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 <schreiner_p...@att.net> 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 <april.hil...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > 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
-~----------~----~----~----~------~----~------~--~---
