?
Solved

macro to extract section to output tab

Posted on 2011-03-11
7
Medium Priority
?
436 Views
Last Modified: 2012-05-11
I need a macro that will run against either tab (pm or rm) and extract the Trade History section into an output tab as shown.
trade-macro-v2.1c.xlsm
0
Comment
Question by:rtod2
[X]
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
  • 3
  • 3
7 Comments
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 35116307
Please try this:

Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim vSheets() As Variant
  Dim vSheetname As Variant
  Dim rngSrc As Range
  vSheets = Array("pm", "rm")
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  For Each vSheetname In vSheets
    Set wks = ActiveWorkbook.Worksheets(vSheetname)
    Set rngSrc = wks.Cells.Find("Account Trade History")
    Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
    ActiveWorkbook.Worksheets(vSheetname & "_output").Delete
    Set wks = ActiveWorkbook.Worksheets.Add
    wks.Name = vSheetname & "_output"
    rngSrc.Copy wks.Range("A1")
  Next
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:rtod2
ID: 35116327
Perfect!  Thank you.

I was only wanting it to run against one or the other though.  I want to be 'able' to run against either sheet.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35116332
you can change it to do whatever you need.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rtod2
ID: 35116366
How would I get it to give only one output depending on which sheet it was run against?
0
 
LVL 46

Expert Comment

by:aikimark
ID: 35117851
* You would query the user for a sheet name (Inputbox
* You would assume that the activesheet.name is the sheet to be processed -- warning: you need to check to see that the activesheet.name is PM or RM.
* It shouldn't matter.  Let the code create both outputs and delete the one you don't want.
0
 

Author Comment

by:rtod2
ID: 35117904
Actually it really does matter for this.  I was asked to break it down into small pieces so to simplify what I am asking, I will just start referring to a single tab.  Here is the new question >>>  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26882640.html
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

762 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