VBA solution to copy sheets from one excel workbook and past them into my current open workbook

russelldt

Board Regular
Joined
Feb 27, 2021
Messages
156
Office Version
  1. 365
Platform
  1. MacOS
I hope someone can help me. Here is the scenario. When I open a new Job File, I need to add (or copy) 2 specific sheets (Work and Detail) to the new job file. These sheets are in the quote file (Quote Number). The options for the Quote and sheets are in dropdown menu’s, and I need to select from 2 dropdown menus to get the sheet (i.e Quote Number and Work, or Quote Number and Detail). My thinking is that there could be a macro button (shown on the attached screenshot) that is pressed once the Quote number and scope are selected. The same would apply for the Detail.

Both files reside in the same directory (Call it 2021, but in separate sub directories ( Quote subdirectory and Job subdirectory)

So, in short, I want to copy 2 sheets from a Quote file, and add them to a job file, without having to open the quote file and using the "move" option, and I am looking to a VBA solution for this. There is a thread that i have followed

VBA; Copy a whole work sheet from another workbook and have it paste into a sheet in current workbook​

and I have tried the solution that was posted:

OpenBook.Sheets("Details").UsedRange.Copy
ThisWorkbook.Worksheets("Copy of Detail").Range("A1").PasteSpecial xlPasteValues,

...without any success, as I am using 2 dropdown menus

Both files reside in the same directory (Call it 2021, but in separate sub directories ( Quote subdirectory and Job subdirectory)

I would appreciate some help






Thanks
 

Attachments

  • Screen Shot 2021-09-10 at 12.39.11.png
    Screen Shot 2021-09-10 at 12.39.11.png
    117.6 KB · Views: 13

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
If I follow your post correctly, add a module with this code to your current open workbook (saved as .xslm) and assign the 2 macros to the respective Go buttons (form controls). The first macro looks at cell F4 dropdown and the second F12. Assumes your current open workbook resides in the 2021 folder.
VBA Code:
Public Sub Copy_Work_Sheet_From_Workbook()

    Dim fromFile As String, fromWorkbook As Workbook
    Dim currentSheet As Worksheet
    
    If Range("F4").Value <> "" Then
        fromFile = ThisWorkbook.Path & "\Quote\" & Range("F4").Value & ".xlsx"
        If Dir(fromFile) <> vbNullString Then
            Set currentSheet = ActiveSheet
            Set fromWorkbook = Workbooks.Open(fromFile)
            fromWorkbook.Worksheets("Work").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
            fromWorkbook.Close False
            currentSheet.Activate
        Else
            MsgBox "File not found: " & vbCrLf & fromFile, vbExclamation, "Copy Work sheet"
        End If
    End If

End Sub


Public Sub Copy_Detail_Sheet_From_Workbook()

    Dim fromFile As String, fromWorkbook As Workbook
    Dim currentSheet As Worksheet
    
    If Range("F12").Value <> "" Then
        fromFile = ThisWorkbook.Path & "\Job\" & Range("F12").Value & ".xlsx"
        If Dir(fromFile) <> vbNullString Then
            Set currentSheet = ActiveSheet
            Set fromWorkbook = Workbooks.Open(fromFile)
            fromWorkbook.Worksheets("Detail").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
            fromWorkbook.Close False
            currentSheet.Activate
        Else
            MsgBox "File not found: " & vbCrLf & fromFile, vbExclamation, "Copy Detail Sheet"
        End If
    End If

End Sub
 
Upvote 0
Thanks John, I will give this a shot. Yes, all workbooks reside in the same folder, but in different sub-folders.

Just to clarify,

1. I add a VBA module with all the text following "VBA Code"
2. Do I assign the same Macro to each "Go" button, or end at the "end Sub" command
3. Do these macros have specific names i should use when I assign them to the control


Thank you
 
Upvote 0
1. Yes.
2. No - assign the first macro (Copy_Work_Sheet_From_Workbook) to the first Go button and the second macro (Copy_Detail_Sheet_From_Workbook) to the second Go button.
3. Yes - see answer 2.
 
Upvote 0
1. Yes.
2. No - assign the first macro (Copy_Work_Sheet_From_Workbook) to the first Go button and the second macro (Copy_Detail_Sheet_From_Workbook) to the second Go button.
3. Yes - see answer 2.
Ta, Thanks John. I will test later today
 
Upvote 0
The Quote workbooks have 20+ sheets, and i only want the 1 or 2 sheets that I select, to copy to the new (Job) file.

Looking at the macro, will it prompt me to specify the sheet (dropdown menu selection)?
 
Upvote 0
The Quote workbooks have 20+ sheets, and i only want the 1 or 2 sheets that I select, to copy to the new (Job) file.
The first macro copies the "Work" sheet from the workbook file name in F4 (with ".xlsx" appended) in the "Quote" subfolder, if it exists, for example "\Quote\Quote 1.xlsx".

The second macro copies the "Detail" sheet from the workbook file name in F12 (with ".xlsx" appended) in the "Job" subfolder, if it exists, for example "\Job\Quote 2.xlsx".

Looking at the macro, will it prompt me to specify the sheet (dropdown menu selection)?
Neither macro will prompt you. You select the required Quote via the dropdown in F4 or F12 then click the appropriate Go button. The macros assume the dropdowns are Data validation in-cell dropdowns.
 
Upvote 0
The first macro copies the "Work" sheet from the workbook file name in F4 (with ".xlsx" appended) in the "Quote" subfolder, if it exists, for example "\Quote\Quote 1.xlsx".

The second macro copies the "Detail" sheet from the workbook file name in F12 (with ".xlsx" appended) in the "Job" subfolder, if it exists, for example "\Job\Quote 2.xlsx".


Neither macro will prompt you. You select the required Quote via the dropdown in F4 or F12 then click the appropriate Go button. The macros assume the dropdowns are Data validation in-cell dropdowns.
The "detail" sheets are in the Quote file. So for example the quote file has 20 detail sheets, and i only need 1 or 2 of them copied into the new Job file that i am creating.
 
Upvote 0
The "detail" sheets are in the Quote file. So for example the quote file has 20 detail sheets, and i only need 1 or 2 of them copied into the new Job file that i am creating.
I understood from your OP that you wanted the "Detail" sheet (exact name) copied.

I think it's best to start again with some questions to clarify what you want.

1. Which cells contain the 2 dropdowns (Data validation in-cell dropdowns)?
2. Which cell(s) should the macro look at to determine which workbook to open in the Quote subfolder and which sheet to copy from that workbook?
3. Which cell(s) should the macro look at to determine which workbook to open in the Job subfolder and which sheet to copy from that workbook?
 
Upvote 0
OK, here goes. My initial use of "detail: was misleading, so here goes a (hopefully better) explanation of what I want to achieve. Please refer to the screen shot.

1. I am creating a new job in the sub folder - Job Sheets
2. The quote for this job is in the subfolder - Quote Sheets.
3. The process i would want to follow is to open the new job file, and copy 2 sheets from the Quote file, into the job file - at the press of a button (so to speak)
4. I would select the quote number from the dropdown menu in cell A3, and the sheet names from the menus in cells C3 and D3.
5. I would press the Button 20 ( changed name to "Go"), and these sheets (all the content) would then be added to the job folder.
6. All the excel files are .xlsm format

I have included the directory structure fir reference.

I know that the simplest solution would be for me to open both workbooks, and move/copy the sheets i need from the Quote file to the Job file, but i am looking for a more elegant way of doing this.

Thank you
 

Attachments

  • Screen Shot 2021-09-15 at 22.21.46.png
    Screen Shot 2021-09-15 at 22.21.46.png
    53.3 KB · Views: 10
  • Screen Shot 2021-09-15 at 22.23.41.png
    Screen Shot 2021-09-15 at 22.23.41.png
    49.4 KB · Views: 12
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top