?
Solved

Simple Excel VBA

Posted on 2014-04-02
14
Medium Priority
?
284 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
[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
  • 9
  • 5
14 Comments
 
LVL 27

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
Office 365 Training for Admins - 7 Day Trial

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 27

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 27

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 27

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 27

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 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