Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel 2007 - VBA Help.  Copy Rows between Tabs

Posted on 2012-04-01
10
357 Views
Last Modified: 2012-06-21
OK.  Checkboxes didn't work as it just crashes my Excel.  So trying again.  So here is what I need.

Have a spreadsheet (Attached).  This is only two tabs of the actual spreadsheet but hoping this can convert easily.  Tabs are Retrieval and Master.  Rows 1-15 are heading rows and should never be altered or moved or changed in any way.

Also why it asks for vba password is beyond me.  This is a brand new Excel sheet from scratch.  I never set a password.

If possible can we name modules something related to these buttons/macros?  

I really appreciate the help.  I tried the 'Check Box' way via another post and it just crashed my Excel as the file grew too big.  

I put notes on each tab in the file for details.  I will award a ton of points for this on separate questions as we go down each.
EE-Template-New.xlsm
0
Comment
Question by:elwayisgod
  • 6
  • 2
  • 2
10 Comments
 
LVL 17

Expert Comment

by:vb_elmar
ID: 37793679
@elwayisgod :

There are 2 different kinds of buttons in Excel:
1) "ActiveX"-Buttons and
2) "Form"-Buttons

Because the three buttons in your Excel file were "Form-Buttons", I inserted three
"ActiveX-Buttons", and if you insert some code, your code should work. See attached file ...
EE-Template-New.xlsm
0
 

Author Comment

by:elwayisgod
ID: 37793684
OK.. I have no idea what Active X means.  I need help with the code to perform the tasks too.  I'm not a VBA person at all.  I can attach the code to a button and do some minor tinkering but cannot actually write the code too.

I can't even get into the Visual Basic editor?  It asks for a password.  I never even set one.
0
 
LVL 17

Expert Comment

by:vb_elmar
ID: 37793694
>>I can't even get into the Visual Basic editor?
When Excel is running press [ALT + F11]. Then you can insert your VBA code.

>>It asks for a password.
On my computer it doesn't ask for a password, and I can insert some code.
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 33

Expert Comment

by:Norie
ID: 37793701
This code will copy the records from Retrieval as indicated, it uses Sheet3 as a kind of 'workings' sheet.
Option Explicit

Sub CopyFromRetrieval()
Dim rngData As Range
Dim rngResult As Range
Dim rngHeaders As Range
Dim NoCols As Long
Dim rngDst As Range
Dim rngCrit As Range
Dim LastRow As Long

    LastRow = Worksheets("Retrieval").Range("B" & Rows.Count).End(xlUp).Row

    Set rngData = Worksheets("Retrieval").Range("A14:U1" & LastRow)
    
    Set rngHeaders = rngData.Rows(1)
    
    NoCols = rngData.Columns.Count
    
    rngHeaders.Cells(1, 1).Value = "Field1"
    
    rngData.Cells(1, 1).AutoFill rngHeaders.Rows(1), xlFillDefault
    
    Set rngCrit = Worksheets("Sheet3").Range("A1:A2")
    
    rngCrit.Value = Application.Transpose(Array("Field1", "Copy"))
    
    Set rngDst = Worksheets("Master").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
    
    rngData.AdvancedFilter xlFilterCopy, rngCrit, rngCrit.Cells(1, 1).Offset(, 2), True
    
    rngHeaders.ClearContents
    
    rngCrit.Resize(, 2).EntireColumn.Delete
    
    LastRow = Worksheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Row
    
    Set rngResult = Worksheets("Sheet3").Range("A2:U" & LastRow)
    
    rngResult.Copy
    
    rngDst.PasteSpecial xlPasteValues
    
    rngResult.EntireColumn.Clear
    
    rngDst.EntireColumn.Clear
    
    Application.CutCopyMode = False
    
End Sub

Open in new window


If you right click one of the buttons, select Assign Macro.. you can assign this sub to the button's click event.

I've done that with the Copy to Master tab button in the file attached.

Not sure what the other 2 buttons are meant to do.
EE-Template-NewV2.xlsm
0
 

Author Comment

by:elwayisgod
ID: 37793736
This looks pretty good on the copy.  A few things.  Can it clear the 'Master' tab first?  That way everytime they hit the button, it's a fresh set of rows over there? Thats all I can think of on this button.  After we fix this can we move to the other buttons?
0
 

Author Comment

by:elwayisgod
ID: 37793748
I need a drop down box in Column A for Rows 15 to 3014.  Choices are 'Copy' and NULL or Blank.  That way only the ones with 'Copy' get copied when the button is pressed.

Also when I set one as Copy can it apply the shading like in the example?  There's potentially thousands of rows and shading the 'Copy' ones will help?

I can post new questions if you want more points.
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 37793758
Here's an update version that removes the existing data in the Master sheet.

As for the other buttons/questions, I suppose it's up to you - not sure if there any rules/etiquette.
EE-Template-NewV3.xlsm
0
 

Author Comment

by:elwayisgod
ID: 37793782
Excellent.  I'll post new question and will award another 500 pts.  You willing to keep helping me.  Only a few more to do.
0
 

Author Closing Comment

by:elwayisgod
ID: 37793784
Perfect
0
 

Author Comment

by:elwayisgod
ID: 37793798
New question is posted.  I appreciate help
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question