Copy & Paste based on value into other workbook

**KerahJoy** · 10-05-2023, 11:14 AM

I posted this before but modified it since my original ask had too many steps.

I have a sourcedata wb SourceData FY24 and a destination wb Destination FY24.
Within wb Destination FY24, ws Mission Control, and cell "B3" there is a name listed which I want to use to filter the range to copy. Steps wanted:

1. Clear all data from cells "AF2 to BW10" in ws Mission Control of wb Destination FY24. Code must be dynamic since table size can change.
2. Copy only rows from wb SourceData FY24 and ws SourceData that match value in "B3" of Destination FY24.
3. Paste copied data into Destination FY24 starting in cell "AF2".

My current code does not work fully and need feedback:

Please Login or Register  to view this content.

**MikeVol** · 10-05-2023, 03:46 PM

Hello @KerahJoy. Maybe so?

Please Login or Register  to view this content.

Good Luck.

**Marc L** · 10-07-2023, 11:55 AM

Originally Posted by KerahJoy

My current code does not work fully

Just well reading at least the error message you should have seen you have forgotten to unprotect the worksheet ‼

According to your attachment removing the useless and adding the missing a VBA demonstration for starters :

PHP Code:


Sub Demo1() 
  Const P = "1234" 
    Dim D$, S$, Ws As Worksheet 
        D = "D:\Tests4Noobs\KerahJoy\Destination FY24.xlsx" 
        S = Replace$(D, "Destination", "SourceData") 
        If Dir(D) = "" Or Dir(S) = "" Then Beep: Exit Sub 
        Application.ScreenUpdating = False 
        Set Ws = Workbooks.Open(D, 0).Worksheets(1) 
        Ws.Unprotect P 
        Ws.[AF1].CurrentRegion.Offset(1).ClearContents 
   With Workbooks.Open(S, 0) 
       .Worksheets(1).ListObjects(1).Range.AdvancedFilter 2, Ws.[B2:B3], Ws.[AF1:BW1] 
       .Close False 
   End With 
        Ws.Protect P 
        Ws.Parent.Close True 
        Application.ScreenUpdating = True 
        Set Ws = Nothing 
End Sub

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

**KerahJoy** · 10-09-2023, 03:59 PM

Originally Posted by MikeVol

Hello @KerahJoy. Maybe so?

Please Login or Register  to view this content.

Good Luck.

Worked perfectly…..thank you Mike!!!

Can you show me a slight modification to your code to not require the filter and copy all active data from source? I have 2 workbooks that require different kind of data pull……

**MikeVol** · 10-10-2023, 11:21 AM

Hello @KerahJoy. If the structure of the book does not differ from the current examples that you provided in the first post, then you can use the following code:

Please Login or Register  to view this content.

If the structure of the books is different, then this code will not work correctly. Good luck.

Copy & Paste based on value into other workbook

LinkBack

Thread Tools

Rate This Thread

Display

Copy & Paste based on value into other workbook

Re: Copy & Paste based on value into other workbook

Try this !

Re: Copy & Paste based on value into other workbook

Thread Information

Users Browsing this Thread

Similar Threads

Copy & Paste Data from workbook to various sheets of another workbook based on date

Copy & Paste from Workbook A to Workbook B Based on Date and value of cell

Copy and paste based on criteria in another workbook

Copy and Paste Data from one Sheet to Other on different workbook based on Criteria

[SOLVED] Copy column based on info in first row and paste into new workbook via macro

Copy and paste entire row based on text in row A to new workbook.

Copy a range based on dynamic row numbers and paste into another workbook

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions