Solved

Simple Excel VBA

Posted on 2014-04-02
14
280 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 IT Pros

Learn how to provision Office 365 tenants, synchronize your on-premise Active Directory, and implement Single Sign-On.

 
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 500 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

Increase your protection from Zero Day threats!

Running two Antivirus' is never a good idea.
Taking advantage of Multiple Security layers on the other hand can often save your hide.
See which top notch security software brands have been proven to happily coexist together.
Reduce your chances of becoming a statistic.

Question has a verified solution.

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

Outlook Free & Paid Tools
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

737 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