Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Simple Excel VBA

Posted on 2014-04-02
14
Medium Priority
?
294 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 found then copy range A1:F till end from WB Data & past to A1 in WB Process.
3.Copy Range A3:C3 from WB Result & Past to L3:N3 in WB Process.
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
  • 9
  • 5
14 Comments
 
LVL 28

Expert Comment

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

    Dim wbData As Workbook, wbResult As Workbook

    Set wbData = Workbooks.Open("C:\Users\Administrator\Desktop\Data.xlsx")
    Set wbResult = Workbooks.Open("C:\Users\Administrator\Desktop\Result.xlsx")

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

        wbResult.Range("A3:C3").Copy
        Range("L3:N3").PasteSpecial
        
    End If

End Sub

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39972175
sure I am on my way back to home. Revert you back in hour.

Thank You
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39972438
Getting ErrorError Message

just to inform you there are Three WB - Process.xlsm in which i had past this Code & Data.xlsx & Result.xlsx

Thanks
0
Independent Software Vendors: 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!

 
LVL 28

Expert Comment

by:MacroShadow
ID: 39972463
I'm sorry, replace all the 0's in that line with 1's.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39972950
Next Line Error, sorry for delay in reply, now i am online.Error Message
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39972979
This are the all three files. See Attached.


Thank You
Process.xlsm
data.xlsx
Result.xlsx
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39973335
This seems to work:
Sub Demo()

    Dim wbData As Workbook, wbResult As Workbook

    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
        Range("A1").PasteSpecial

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

    End If

End Sub

Open in new window

0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974087
Did you tried your code ? as I cant see any thing in WB Process.

1.Match 1st sheet name from data WB to Result WB.
2.If Match found then copy range A1:F till end from WB Data & past to A1 in WB Process.
3.Copy Range A3:C3 from WB Result & Past to L3:N3 in WB Process.
4.End


All things done in other WB. process WB remain As it is.

Thank You
0
 
LVL 28

Accepted Solution

by:
MacroShadow earned 2000 total points
ID: 39974136
This should do it:
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
        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

0
 
LVL 8

Author Closing Comment

by:Naresh Patel
ID: 39974142
Thank You
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974144
May I ask next question related to this context?
0
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39974150
Sure, as a new question.
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974154
One more thing I had done by my self which you rectify in 2nd line of code
Dim wbCurrent As Workbook, wbData As Workbook, wbResult As Workbook

Open in new window



it is like this but I got errors ...do u know the reasons ...I had done all activate steps too.
wbData As Workbook, wbResult As Workbook, wbProcess As Workbook

Open in new window



Thank You
0
 
LVL 8

Author Comment

by:Naresh Patel
ID: 39974160
here it is Simple Eecel VBA.


Thank you
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

571 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