Middle man workbook to Copy paste values from one workbook to another

drop05

New Member
Joined
Mar 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hello,

I was wondering if there is a way to have three workbooks. Workbook1 has multiple sheets, example sheet1, sheet2, ect, with tables with cells containing questions and then the cell next to it where values can be typed into. Workbook3 has the a similar format but the cells that need values are blank. Is there VBA to be able to map those imported values from workbook1 to get into the blank cells in workbook3. Workbook 2, the middle one, will contain the vba to run this, inside has columns with the sheet names from both workbook1 and 3, the rows and columns to copy from workbook1 and the ones for workbook3.

Any help would be appreciated! New to VBA so playing around with it.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,302
Office Version
  1. 2010
Platform
  1. Windows
hi and welcome to MrExcel
why do you need this middle workbook?
 

drop05

New Member
Joined
Mar 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
hi and welcome to MrExcel
why do you need this middle workbook?
The middle one has for example, workbook 1 question description, the tab name call, and also has workbook 3 tab name which is the same as work book 1s but slightly different, question description which same but slightly different but also has workbooks 1 row and column to get the data from
It will be the middle man to show the differences/similarities then run it
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You do not need the middle workbook2 to transfer data from workbook1 and then make another transfer to workbook3. I guess you also do not want to have macro in either workbook1 and workbook3.

I envisioned that you have workbook2 as a tool which can do whatever you like between 2 workbooks. For example, you run workbook2. The macro will ask for source workbook where you can select workbook1. Then the macro will ask for destination workbook where you select workbook3. Then macro will ask which data you wanted to copy from workbook1 and where you want the data to copy to in workbook3.

The workbook2 is just like tool. It does not need to store and transfer data to its sheet at all. To make it interactive it will involve userforms to ask you to select which sheet from workbook1 and also workbook3. The complexity depends on how extensive the function you want to have. It could be complicated project but it is possible to do ;)

Imagine workbook2 as having many collection of macros to perform many kind of tasks onto one, two or more workbooks.
 

drop05

New Member
Joined
Mar 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
You do not need the middle workbook2 to transfer data from workbook1 and then make another transfer to workbook3. I guess you also do not want to have macro in either workbook1 and workbook3.

I envisioned that you have workbook2 as a tool which can do whatever you like between 2 workbooks. For example, you run workbook2. The macro will ask for source workbook where you can select workbook1. Then the macro will ask for destination workbook where you select workbook3. Then macro will ask which data you wanted to copy from workbook1 and where you want the data to copy to in workbook3.

The workbook2 is just like tool. It does not need to store and transfer data to its sheet at all. To make it interactive it will involve userforms to ask you to select which sheet from workbook1 and also workbook3. The complexity depends on how extensive the function you want to have. It could be complicated project but it is possible to do ;)

Imagine workbook2 as having many collection of macros to perform many kind of tasks onto one, two or more workbooks.
Yes exactly! No macros in workbook 1 or 3, just workbook 2 has the vba code to select the file to copy data from and the select the file to paste to. But mainly looking for a way to find the cells to pull from and the put them in a selected destination.
Example workbook 1 and 4 has 5 sheets which match in a way but Workbook 1 has
sheet 1 from workbook 1
Cells: A B
1. Test 1
2. Test 2
3. Test 3

work3 book has the same type of landing spots but different columns and rows it needs those values pasted, like I need A1 Value to paste in sheet 1 of workbook 3 in A6 and the value from B1 to paste in B6 and so on so forth
But trying to think and research of a way and wanted others input
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

This is a simple demo on how to do. I don't bother about workbook1, workbook2 or any workbook. The workbook where the macro resides can be just name as WorkbookProgram whatever.

In this demo the source and destination range is hard coded. To make the tool more flexible, you can create a userform asking user to enter copy range and destination range. Upon opening the workbook, you can make macro check for total sheets in workbook and if more than 1, pop-up button asking you to select by clicking sheet tab and press continue which I did not include here except for basic operation.

Not tested

VBA Code:
Sub Test()

Dim Fname As Variant
Dim wsA As Worksheet, wsB As Worksheet
Dim wbA As Workbook, wbB As Workbook
Dim rngA As Range, rngB As Range

' Select workbook A and define as wbA
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbA = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbA. Change sheet name accordingly
Set wsA = wbA.Sheets("Sheet1")

' Select workbook B and define as wbB
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbB. Change sheet name accordingly
Set wsB = wbB.Sheets("Sheet1")

' Copy frm wsA to wsB
wsA.Range("A1").Copy wsB.Range("A6")
wsA.Range("B1").Copy wsB.Range("B6")

End Sub
 

drop05

New Member
Joined
Mar 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
This is a simple demo on how to do. I don't bother about workbook1, workbook2 or any workbook. The workbook where the macro resides can be just name as WorkbookProgram whatever.

In this demo the source and destination range is hard coded. To make the tool more flexible, you can create a userform asking user to enter copy range and destination range. Upon opening the workbook, you can make macro check for total sheets in workbook and if more than 1, pop-up button asking you to select by clicking sheet tab and press continue which I did not include here except for basic operation.

Not tested

VBA Code:
Sub Test()

Dim Fname As Variant
Dim wsA As Worksheet, wsB As Worksheet
Dim wbA As Workbook, wbB As Workbook
Dim rngA As Range, rngB As Range

' Select workbook A and define as wbA
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbA = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbA. Change sheet name accordingly
Set wsA = wbA.Sheets("Sheet1")

' Select workbook B and define as wbB
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbB. Change sheet name accordingly
Set wsB = wbB.Sheets("Sheet1")

' Copy frm wsA to wsB
wsA.Range("A1").Copy wsB.Range("A6")
wsA.Range("B1").Copy wsB.Range("B6")

End Sub
This is awesome! Thank you so much. Question, say I need a larger scale range
This is a simple demo on how to do. I don't bother about workbook1, workbook2 or any workbook. The workbook where the macro resides can be just name as WorkbookProgram whatever.

In this demo the source and destination range is hard coded. To make the tool more flexible, you can create a userform asking user to enter copy range and destination range. Upon opening the workbook, you can make macro check for total sheets in workbook and if more than 1, pop-up button asking you to select by clicking sheet tab and press continue which I did not include here except for basic operation.

Not tested

VBA Code:
Sub Test()

Dim Fname As Variant
Dim wsA As Worksheet, wsB As Worksheet
Dim wbA As Workbook, wbB As Workbook
Dim rngA As Range, rngB As Range

' Select workbook A and define as wbA
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbA = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbA. Change sheet name accordingly
Set wsA = wbA.Sheets("Sheet1")

' Select workbook B and define as wbB
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbB. Change sheet name accordingly
Set wsB = wbB.Sheets("Sheet1")

' Copy frm wsA to wsB
wsA.Range("A1").Copy wsB.Range("A6")
wsA.Range("B1").Copy wsB.Range("B6")

End Sub
You are awesome, I understand this completely,
One other question, say i wanted the sum of two cells from wsA, say A1 and A2, and paste the sum of those two cells from that worksheet 1 from that workbook 1 into a cell, A1, in workbook3 worksheet1? Is that possible in VBA
 

drop05

New Member
Joined
Mar 23, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
This is a simple demo on how to do. I don't bother about workbook1, workbook2 or any workbook. The workbook where the macro resides can be just name as WorkbookProgram whatever.

In this demo the source and destination range is hard coded. To make the tool more flexible, you can create a userform asking user to enter copy range and destination range. Upon opening the workbook, you can make macro check for total sheets in workbook and if more than 1, pop-up button asking you to select by clicking sheet tab and press continue which I did not include here except for basic operation.

Not tested

VBA Code:
Sub Test()

Dim Fname As Variant
Dim wsA As Worksheet, wsB As Worksheet
Dim wbA As Workbook, wbB As Workbook
Dim rngA As Range, rngB As Range

' Select workbook A and define as wbA
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbA = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbA. Change sheet name accordingly
Set wsA = wbA.Sheets("Sheet1")

' Select workbook B and define as wbB
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked
Set wbB = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

' Define working sheet in wbB. Change sheet name accordingly
Set wsB = wbB.Sheets("Sheet1")

' Copy frm wsA to wsB
wsA.Range("A1").Copy wsB.Range("A6")
wsA.Range("B1").Copy wsB.Range("B6")

End Sub
Ignore that first comment on large scale range i figured it out
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
This is awesome! Thank you so much. Question, say I need a larger scale range

You are awesome, I understand this completely,
One other question, say i wanted the sum of two cells from wsA, say A1 and A2, and paste the sum of those two cells from that worksheet 1 from that workbook 1 into a cell, A1, in workbook3 worksheet1? Is that possible in VBA
You can perform any calculation in variable say:

Dim sumA as Double
sumA = wsA.Range("A1") + wsA.Range("A2")


then say your worksheet3 is defined as ws3

ws3.Range("A1") = sumA

no need to calculate on worksheet but just in memory.

A note on big range. The destination can be just a single cell like you perform copy range in Excel. You copy a big range then then when pasting you just click a single cell in destination (not a similar range).
So, it is just like like this (probably you have this figured)

wsA.Range("A1", "H10").Copy wsB.Range("A6")
 

Watch MrExcel Video

Forum statistics

Threads
1,130,037
Messages
5,639,673
Members
417,104
Latest member
Nelsini

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