Solved

Simple Excel VBA

Posted on 2014-04-02
17
268 Views
Last Modified: 2014-04-02
Hi Experts,

I have one task which I want to be automated by VBA. will you pls help me out on this?

I have three WB Data - Process - Result.

in WB Process I have button Calles "Activate" so by clicking that button. Code do below mention Steps.
1.Match 1st sheet name from data WB to Result WB.
2.If Match not found then copy range A1:F till end from WB Data & past to A1 in WB Process.
3.Pop box ask for values in Cell L3 & M3 & N3. which user manually fill in popup box.
4.End

Note:- only Process WB is open rest two are closed. path for there two WB are "C:\Users\Administrator\Desktop\Data.xlsx" & "C:\Users\Administrator\Desktop\Result.xlsx".

Thank You Awaiting your response.
0
Comment
Question by:Naresh Patel
[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
  • 10
  • 7
17 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974176
Try this:
Sub Demo()

    Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook
    
    Set wbCurrent = ActiveWorkbook
    Set wbData = Workbooks.Open("C:\Users\MacroShadow\Desktop\Data.xlsx")
    Set wbResult = Workbooks.Open("C:\Users\MacroShadow\Desktop\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
        wbResult.Activate
        Range("A1").PasteSpecial

        wbResult.Sheets(1).Range("L3") = InputBox("Please enter value for L3:", "Data input")
        wbResult.Sheets(1).Range("M3") = InputBox("Please enter value for M3:", "Data input")
        wbResult.Sheets(1).Range("N3") = InputBox("Please enter value for N3:", "Data input")

    End If
    
    wbData.Close False
    wbCurrent.Close False

End Sub

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974188
I guess
Sub Demo2()

    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

        wbCurrent.Sheets(1).Range("L3") = InputBox("Please enter value for L3:", "Data input")
        wbCurrent.Sheets(1).Range("M3") = InputBox("Please enter value for M3:", "Data input")
        wbCuurent.Sheets(1).Range("N3") = InputBox("Please enter value for N3:", "Data input")

    End If
    
    wbData.Close False
    wbCurrent.Close False

End Sub

Open in new window


But I got Error in
wbCuurent.Sheets(1).Range("N3") = InputBox("Please enter value for N3:", "Data input")

Open in new window


Thanks
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974194
Sorry, change:
        wbCurrent.Sheets(1).Range("L3") = InputBox("Please enter value for L3:", "Data input")
        wbCurrent.Sheets(1).Range("M3") = InputBox("Please enter value for M3:", "Data input")
        wbCuurent.Sheets(1).Range("N3") = InputBox("Please enter value for N3:", "Data input")

Open in new window

to:
        wbCurrent.Sheets(1).Range("L3").Value = InputBox("Please enter value for L3:", "Data input")
        wbCurrent.Sheets(1).Range("M3").Value = InputBox("Please enter value for M3:", "Data input")
        wbCuurent.Sheets(1).Range("N3").Value = InputBox("Please enter value for N3:", "Data input")

Open in new window

0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974195
cant we have only one input box for all three values & values separated by space?

Thanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974198
did it but Error
Thanks
0
 
LVL 27

Accepted Solution

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

    Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook
    Dim varData() As Variant
    
    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)
        wbCuurent.Sheets(1).Range("N3").Value = varData(2)

    End If
    
    wbData.Close False
    wbCurrent.Close False

End Sub

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974217
Error
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974223
Change line 4 to
Dim varData() As String

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974227
Error
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974235
there is a typo, change;
wbCuurent.Sheets(1).Range("N3").Value = varData(2)

Open in new window

to:
wbCurrent.Sheets(1).Range("N3").Value = varData(2)

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974241
Sorry that is my mistake. How dumb I am . but there is still errorErrorThanks
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974243
and I guess this is Error
wbResult.Close False

Open in new window

0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974245
That line should not produce an error. What error do you get (in the future please include the error so i don't have to guess what the problem is!)?
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974252
ok done may I ask new question on this context?
0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39974253
Thank you
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39974257
Sure.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974268
This is the link Merge this Two VBA Code in To One

Thanks
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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

691 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