Export Specific Cells from another workbook into 2 sheets based on set criteria

mightyG123

New member
Joined
Jul 11, 2019
Messages
3
Reaction score
0
Points
0
Excel Version(s)
Office 365 Pro Plus
Hello,

I'm a newbie to VBA within excel and I'm struggling to be able to pull data from the Source Workbook into the Destination Workbook. The codes I've been using haven't worked between workbooks and thus far I have only been able to copy data between sheets but I've not been able to narrow down the cells being copied (so I keep getting the full row).



What I want to achieve:
1. Export specific data based set criteria ("x" in Column A and "West" in Column B) from "SourceWorkbook.xlsx" (Sheet1) into "DestinationWorkbook.xlsm" (Sheet1)
*Specific data1* is highlighted in yellow in SourceWorkbook in Sheet1

2. Ideally I'd like a new RCA Ref (Column A in Destination Workbook Sheet1) to auto-populate then

3. Copy different specific data from DestinationWorkbook (Sheet1) into DestinationWorkbook (Sheet2)
*Specific data2* is highlighted in orange in Destination Workbook in Sheet1


Ideally I'd like both 1, 2 and 3 to be done at the same time (but I understand that might be too much to ask so at worst, step 1 is automated, step 2 manual and step 3 automated). I would just like to prevent me having multiple different buttons but the main thing is to ensure that no data is written over in the DestinationWorkbook. (In the future I am looking to change the destination workbook so it's only 1 sheet to export into but as it's not my workbook and links to several external dashboards, I'm not able to change this currently).

Please note:
The SourceWorkbook is stored in onedrive under this file location - C:\Users\OneDrive - Utilities Limited\Desktop\SourceWorkbook.xlsx
The DestinationWorkbook is stored in onedrive under this file location - C:\Users\OneDrive - Utilities Limited\Desktop\DestinationWorkbook.xlsm





I have tried several codes which I have attached in a word doc. The only one that works (which copies between sheets within 1 workbook) is below:
Code:
Private Sub commandButton_Click()
'Updated by Extendoffice 2017/11/10
    Dim xRg As Range
    Dim xCell As Range
    Dim I As Long
    Dim J As Long
    Dim K As Long
    I = Worksheets("Copy & Paste Updates Here").UsedRange.Rows.Count
    J = Worksheets("Sheet4").UsedRange.Rows.Count
    If J = 1 Then
    If Application.WorksheetFunction.CountA(Worksheets("Sheet4").UsedRange) = 0 Then J = 0
    End If
    Set xRg = Worksheets("Copy & Paste Updates Here").Range("a1:C" & I)
    On Error Resume Next
    Application.ScreenUpdating = False
    For K = 1 To xRg.Count
        If CStr(xRg(K).Value) = "x" Then
            xRg(K).EntireRow.Copy Destination:=Worksheets("Sheet4").Range("A" & J + 1)
            J = J + 1
            
        End If
    Next
    Application.ScreenUpdating = True
End Sub




I've attached Dummy spreadsheets and the word doc with the codes I've attempted to use (I couldn't attach a macro version as the file size was too big). Any help would be amazing!

Thank you,
G
 

Attachments

  • SourceWorkbook.xlsx
    12.3 KB · Views: 11
  • VBA codes Used.docx
    14.9 KB · Views: 7
  • DestinationWorkbook.xlsx
    13.2 KB · Views: 12
Last edited by a moderator:
How is the RCA reference determined?
 
Hi, the RCA ref is currently manually chosen but in an ideal scenario this will become automated based on when each line of data is chosen to be verified.
 
Yes, but what is the rule for the RCA. Code needs to be explicit, rules-based.
 
There currently isn't a rule, they are a rolling reference that's manually typed in (i.e. 22.8, 22.9, 23.0, 23.1). Sorry I'm not sure how to best describe it.
 
Back
Top