asked on

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.

Joe Howard

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

Naresh Patel

ASKER

sure I am on my way back to home. Revert you back in hour.

Thank You

Naresh Patel

ASKER

Getting Error

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

Thanks

Joe Howard

I'm sorry, replace all the 0's in that line with 1's.

Naresh Patel

ASKER

Next Line Error, sorry for delay in reply, now i am online.

Naresh Patel

ASKER

This are the all three files. See Attached.

Thank You
Process.xlsm
data.xlsx
Result.xlsx

Joe Howard

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

Naresh Patel

ASKER

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

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naresh Patel

ASKER

Thank You

Naresh Patel

ASKER

May I ask next question related to this context?

Joe Howard

Sure, as a new question.

Naresh Patel

ASKER

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

Naresh Patel

ASKER

here it is Simple Eecel VBA.

Thank you