Copy paste data from another workbook

DreyFox

Board Regular
Joined
Nov 25, 2020
Messages
60
Office Version
  1. 2016
Platform
  1. Windows
I have the following sheet known as MASTER_CONFIGURATOR.xlsm:
1628262800341.png

When the "Feed Size BoM's" button is clicked, I would like for it to look for a workbook based on the cell value of A6. For example, the MASTER_CONFIGURATOR file is in a folder known as "Source Files".

The value of A6 right now is BOM1 of F10. When the button Feed Size BoM's is clicked, a new sheet known as BOM1 of F10 is created, then the data from another workbook known as BOM1 of F10 is copied
and pasted into the sheet within the MASTER_CONFIGURATOR known as BOM1 of F10.

The workbook BOM1 of F10 is in the same folder as MASTER_CONFIGURATOR:
1628263138289.png

I was previously using this set of codes to copy and paste from hard coded excel file names:
VBA Code:
Sub Clear_Existing_Data_Before_Paste()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

  Set wsCopy = Workbooks("New Data.xlsx").Worksheets("Export 2")
  Set wsDest = Workbooks("Reports.xlsm").Worksheets("All Data")
   
    '1. Find last used row in the copy range based on data in column A
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
     
    '2. Find first blank row in the destination range based on data in column A
    'Offset property moves down 1 row
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row
   
    '3. Clear contents of existing data range
    wsDest.Range("A2:D" & lDestLastRow).ClearContents
    '4. Copy & Paste Data
    wsCopy.Range("A2:D" & lCopyLastRow).Copy _
      wsDest.Range("A2")
End Sub

I tried to do the following, but it didn't work:
VBA Code:
Sub copypaste_feedsize_bom1()
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

Set wsCopy = Workbooks(Range("A6").Value).Worksheets(1)
Set wsDest = Workbooks("MASTER_CONFIGURATOR.xlsm").Worksheets(Range("A6").Value)

lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(2).Row

wsCopy.Range("A1:G" & lCopyLastRow).Copy _
    wsDest.Range("A" & lDestLastRow)

End Sub

Any advice or help would be greatly appreciated. Thank you very much.
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Forum statistics

Threads
1,147,960
Messages
5,744,047
Members
423,843
Latest member
alex2022

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
Top