Simple Excel VBA

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.
LVL 8
Naresh PatelTraderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
MacroShadowCommented:
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
 
Naresh PatelTraderAuthor Commented:
sure I am on my way back to home. Revert you back in hour.

Thank You
0
 
Naresh PatelTraderAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MacroShadowCommented:
I'm sorry, replace all the 0's in that line with 1's.
0
 
Naresh PatelTraderAuthor Commented:
Next Line Error, sorry for delay in reply, now i am online.Error Message
0
 
Naresh PatelTraderAuthor Commented:
This are the all three files. See Attached.


Thank You
Process.xlsm
data.xlsx
Result.xlsx
0
 
MacroShadowCommented:
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
 
Naresh PatelTraderAuthor Commented:
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
 
MacroShadowCommented:
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

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Naresh PatelTraderAuthor Commented:
Thank You
0
 
Naresh PatelTraderAuthor Commented:
May I ask next question related to this context?
0
 
MacroShadowCommented:
Sure, as a new question.
0
 
Naresh PatelTraderAuthor Commented:
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
 
Naresh PatelTraderAuthor Commented:
here it is Simple Eecel VBA.


Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.