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:
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
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
Last edited by a moderator: