[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

macro to extract section to output tab

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
rtod2
Asked:
rtod2
  • 3
  • 3
1 Solution
 
aikimarkCommented:
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
 
rtod2Author Commented:
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
 
aikimarkCommented:
you can change it to do whatever you need.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
rtod2Author Commented:
How would I get it to give only one output depending on which sheet it was run against?
0
 
aikimarkCommented:
* 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
 
rtod2Author Commented:
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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now