Solved

Simple Excel VBA

Posted on 2014-04-02
6
380 Views
Last Modified: 2014-04-03
Hi Experts,

I just need to Merge this two VBA Macro in to one.
Sub Demo()

    Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook
    
    Set wbCurrent = ActiveWorkbook
    Set wbData = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Data.xlsx")
    Set wbResult = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Result.xlsx")

    If wbData.Sheets(1).Name = wbResult.Sheets(1).Name Then
    
        wbData.Activate
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        wbResult.Activate
        wbResult.Sheets(1).Range("A3:C3").Copy
        wbCurrent.Activate
        Range("L3:N3").PasteSpecial

    End If
    
    wbData.Close False
    wbResult.Close False

End Sub

Open in new window


Sub Demo2()

    Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook
    Dim varData() As String
    
    Set wbCurrent = ActiveWorkbook
    Set wbData = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Data.xlsx")
    Set wbResult = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Result.xlsx")

    If wbData.Sheets(1).Name <> wbResult.Sheets(1).Name Then
    
        wbData.Activate
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        varData = Split(InputBox("Please enter value for L3:N3, seperated by a space.", "Data input"), " ")
        
        wbCurrent.Sheets(1).Range("L3").Value = varData(0)
        wbCurrent.Sheets(1).Range("M3").Value = varData(1)
        wbCurrent.Sheets(1).Range("N3").Value = varData(2)

    End If
    
    wbData.Close False
    wbResult.Close False

End Sub

Open in new window


Thank You
0
Comment
Question by:itjockey
  • 4
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39974272
Sub Demo()

    Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook

    Set wbCurrent = ActiveWorkbook
    Set wbData = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Data.xlsx")
    Set wbResult = Workbooks.Open("H:\4.Trading Master\Thunderbolt\Simple Excel Formula\Result.xlsx")

    wbData.Activate

    If wbData.Sheets(1).Name = wbResult.Sheets(1).Name Then
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        wbResult.Activate
        wbResult.Sheets(1).Range("A3:C3").Copy
        wbCurrent.Activate
        Range("L3:N3").PasteSpecial
    Else
        wbData.Sheets(1).Range("A1:F" & Range("F1048576").End(xlUp).Row).Copy
        wbCurrent.Activate
        Range("A1").PasteSpecial

        varData = Split(InputBox("Please enter value for L3:N3, seperated by a space.", "Data input"), " ")

        wbCurrent.Sheets(1).Range("L3").Value = varData(0)
        wbCurrent.Sheets(1).Range("M3").Value = varData(1)
        wbCurrent.Sheets(1).Range("N3").Value = varData(2)

    End If

    wbData.Close False
    wbResult.Close False

End Sub

Open in new window

0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 39974276
Thank You
0
 
LVL 8

Author Comment

by:itjockey
ID: 39974279
May I ask new question for same context?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 26

Expert Comment

by:MacroShadow
ID: 39974281
Go ahead.
0
 
LVL 8

Author Comment

by:itjockey
ID: 39974284
to frame it will take 5 min... pls Waite as I don't know which part of world you are living...may late there...
0
 
LVL 8

Author Comment

by:itjockey
ID: 39974303
Here it is New Link.

Thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now