copy and paste data from Master data based on specific sheet Cell value ( Condition based )

Guna13

Board Regular
Joined
Nov 22, 2019
Messages
65
Office Version
  1. 365
Platform
  1. Windows
Dear Masters,

This is one of my Pain Area Task now. More than 280+ Excel Files, I was applied this code action one by one by Loop condition. But here Just I copy and paste exact issue code.

Scanriao

In Activeworkbook I have below three sheets.
1.Baan ETB
2.Interim Master Data
3.Interim Acc Validation

I need to copy the data from "Interim Master Data" sheets and paste exact MEP Code Line Item in "Interim Acc Validation" sheets, in Range("B4:E" & Lastrow) .

( Cell value of MEP_Code available in ("Baan ETB") Sheets. (Range.P2.value).... Based on this cell value data only copy and paste it. from Master data to "Interim acc Validation" sheet by Loop condition

Unable to do this VBA code this task. kindly help me sir.. when i run this code. my MEP_Code Value shows "Blank instead of Value. Kindly help and fix this code sir.
VBA Code:
Sub Interim_Acc1()

Dim wss, wss1, wss2, wss3, wss4 As Worksheet
Dim rgdata As Range, rgcriteria As Range, rgoutput As Range
Dim rg As Range


Set wss1 = ThisWorkbook.Sheets("Interim Acc Validation")
Set wss2 = ThisWorkbook.Sheets("Baan ETB")
Set wss4 = ThisWorkbook.Sheets("Info")
Set Trg = ThisWorkbook.Sheets("Interim Master Data")
Set rrr = ThisWorkbook.Sheets("30 FDSS VALIDATION")

wss1.Unprotect password:="1234"

Set rg = ThisWorkbook.Sheets("Interim Acc Validation").Range("A4:H10000")
rg.Clear

wss2.Select
Set rng = wss2.Range("P2:P10000")
With rng
    .Value = Evaluate(Replace("If(@="""","""",Trim(@))", "@", .Address))
End With

mep = wss2.Range("P2").Value

Trg.AutoFilterMode = False
If mep <> "" Then

    Sheets("Interim Master Data").Range("A1:E10000").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Baan ETB").Range("P1:P2"), _
        CopyToRange:=Sheets("Interim Acc Validation").Range("A4"), Unique:=False

wss1.Activate
wss1.Rows(4).EntireRow.Delete
lr = wss1.Range("A" & Rows.Count).End(xlUp).Row


Sheets("Interim Acc Validation").Range("A1").Value = UCase(mep)[/B]

 With Sheets("Interim Acc Validation").Cells.Font
        .Name = "Calibri"
        .Size = 9
    End With
 
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
I am a bit unclear on what the code it not doing ?
Your comment seems to indicate that this line mep = wss2.Range("P2").Value is producing blank but that seems an easy thing to check, so it has to be something else.
 
Upvote 0
Sir
I am a bit unclear on what the code it not doing ?
Your comment seems to indicate that this line mep = wss2.Range("P2").Value is producing blank but that seems an easy thing to check, so it has to be something else.
Please ignore this code if you Not clear.
Just want to new Code require sir like array or advance filter code..

Example - Based on Cell value in "Sheet1 in A2.Value, I need copy the data from "Master data sheet" ( In Master data sheet I had 10 thousand Lines) and paste it in Target Sheet in Range.A2 same workbook.
How can I achieve this task. Incase if Cell value data is Not availabe in Master data. then need to Update No data Comments in Target Sheet
Now is this question Clear Sir...???
 
Upvote 0
Copy the data from Master data sheet. To Paste in Target Sheet. Based on Sheet1. Range.A2. Value Based. if No data in Master data Sheet. then Update. No data in Target Sheet. Range. A2.. Sir.. my communication little trouble. kindly accept my sorry. plz
 
Upvote 0
Please provide an XL2BB of your Master Data sheet and also of your Target sheet "Interim Acc Validation" and at least an image of P1:P2 in Baan assuming that is what you are using for the filter criteria.
Does your target sheet already have the heading, if so on what row ?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top