You are here:

Excel/Macro to copy cells

Advertisement


Question
Hi, I would like to write a macro that will copy 2 rows of cells from one spread sheet to another based on two other cells i.e. if D3 = F series and E3 = 50 copy cells A2:B36 to range G3.

I'm trying to build a front sheet that will return servicing information based on model and hours of operation.
The user will select the series and hours then use a Command button linked to a macro to return the information required.

Depending on the text in D3 and E3 combinations the macro will return different cells.

Answer
Hi Shaun,

Below is some code that does what you describe, and I added an ElseIf as an example of how to add additional test conditions and ranges to be copied.

Since you didn't specify which ranges are on which sheets, I made some assumptions:

I assumed D3, E3, and G3 are on the sheet containing the CommandButton. This means that this sheet will be the active sheet when the code executes.

I assumed that the source range A2:B36 is in the second sheet--I used "Sheet2" so you should change this according to your sheet name.

I also used CommandButton1 as the button object name.

Here is the code:
_______________________________________________________________________

Private Sub CommandButton1_Click()

  'returns data from Sheet2 to G3 on Sheet1 based on the values
  'in D3 and E3 in Sheet1.
  
  With Worksheets("Sheet2")
  
'     Note that ranges preceeded by "." refer to Sheet2, non-qualified ranges to the
'     sheet containing CommandButton1
     If Range("D3") = "F series" And Range("E3") = 50 Then
        .Range("A2:B36").Copy Destination:=Range("G3")
     ElseIf Range("D3") = "Ranger" And Range("E3") = 45 Then
        .Range("C2:D36").Copy Destination:=Range("G3")
     End If
     
  End With
  
End Sub
__________________________________________________

This code should be placed in the worksheet's code module.  To do this, right click on the worksheet's tab, select View Code, and paste this code in the Code pane.

I hope this does what you want.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.