Copy Data from Workbook in same directory to active workbook

[weilerc]

I have a program that generates an excel file under the name "cutlist.xlsx" consistently, that I require 2 sheets from named "SheetComponentListing" and "SheetStockSummary". I am generating these "cutlist.xlsx" files on a per job basis, and each job has its own folder with a file called "cutlist.xlsx"
Basically, I have created a spreadsheet that is intended to be printed for our shop floor, to display what is in this cutlist.xlsx file in a way that is readable, and also add additional information.
Currently, I have all my formulas linked to sheet names "PH1" and "PH2" which are just being used as placeholders to avoid a #REF error when the sheet doesn't exist. I am manually copying the "SheetComponentListing" and "SheetStockSummary" from the "cutlist.xlsx" workbook to the new one, and then doing a find and replace so PH1 = "SheetComponentListing" and PH2 = "SheetStockSummary".
All this works well, however I am needing to pass this off to other people in my workplace who will not be able to complete that task manually.
How would I go about creating a macro that would do this:

1. Search the directory of the current opened workbook and open "cutlist.xlsx"
2. Copy "SheetComponentListing" and "SheetStockSummary" from the "cutlist.xlsx" workbook and paste in the original workbook
3. Find and replace so PH1 = "SheetComponentListing" on sheet titled "Data" and Find and Replace so PH2 = "SheetStockSummary" on sheet titled "Main"

If anyone is able to give me some tips with this I would greatly appreciate it.

 

[gravanoc]

Hi, use this in your destination workbook that has the code in it (although I'd probably recommend using a "bridge" workbook so you don't have to constant repaste the code into a new workbook, if I'm understanding right):

Option Explicit

Sub FindAndReplace()

    Dim wbOrigin As Workbook
    Dim wbDest As Workbook
    Dim wksMain As Worksheet
    Dim path As String
    Dim fileName As String
   
    Set wbDest = ThisWorkbook
    path = wbDest.path
    fileName = "cutlist.xlsx"

    Set wbOrigin = Workbooks.Open(path & Application.PathSeparator & fileName)
    wbOrigin.Sheets("SheetComponentListing").Copy Before:=wbDest.Sheets("Main")
    wbOrigin.Sheets("SheetStockSummary").Copy Before:=wbDest.Sheets("Main")
   
    Set wksMain = wbDest.Sheets("Main")
   
    wksMain.Cells.Replace What:="PH1", Replacement:="SheetComponentListing"
    wksMain.Cells.Replace What:="PH2", Replacement:="SheetStockSummary"
   
    Application.DisplayAlerts = False
    wbOrigin.Close
    Application.DisplayAlerts = True
    wksMain.Activate
   
End Sub

 

[weilerc]

Hi, use this in your destination workbook that has the code in it (although I'd probably recommend using a "bridge" workbook so you don't have to constant repaste the code into a new workbook, if I'm understanding right):

Option Explicit

Sub FindAndReplace()

    Dim wbOrigin As Workbook
    Dim wbDest As Workbook
    Dim wksMain As Worksheet
    Dim path As String
    Dim fileName As String
  
    Set wbDest = ThisWorkbook
    path = wbDest.path
    fileName = "cutlist.xlsx"

    Set wbOrigin = Workbooks.Open(path & Application.PathSeparator & fileName)
    wbOrigin.Sheets("SheetComponentListing").Copy Before:=wbDest.Sheets("Main")
    wbOrigin.Sheets("SheetStockSummary").Copy Before:=wbDest.Sheets("Main")
  
    Set wksMain = wbDest.Sheets("Main")
  
    wksMain.Cells.Replace What:="PH1", Replacement:="SheetComponentListing"
    wksMain.Cells.Replace What:="PH2", Replacement:="SheetStockSummary"
  
    Application.DisplayAlerts = False
    wbOrigin.Close
    Application.DisplayAlerts = True
    wksMain.Activate
  
End Sub

Thank you very much, works very well however with one issue.
For some reason, the Data page formula now has an @ symbol infront, causing the formula to not spill the data correctly, showing only one row.
Is there a way to automatically remove the @ symbol that is being added into cell A2 on the Data tab?


Thanks again

 

[gravanoc]

Thank you very much, works very well however with one issue.
For some reason, the Data page formula now has an @ symbol infront, causing the formula to not spill the data correctly, showing only one row.
Is there a way to automatically remove the @ symbol that is being added into cell A2 on the Data tab?


Thanks again

I'm not at my computer to test, but you could try something like this:

Range("B2").Formula2 = Replace(Range("B2").Formula, "@", "")

I know that sometimes Formula causes that operator to appear. I think it's a legacy item showing implicit intersection. Formula2 usually avoids it, but this case might be different. Substitute the problem cells for B2.