Results 1 to 5 of 5

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

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Neophyte mightyG123's Avatar
    Join Date
    Jul 2019
    Excel Version
    Office 365 Pro Plus

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


    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:
    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
        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,
    Attached Files Attached Files
    Last edited by AliGW; 2019-07-11 at 04:46 PM. Reason: Code tags added

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts