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

[drop05]

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.

 

[diddi]

hi and welcome to MrExcel
why do you need this middle workbook?

 

[drop05]

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

 

[drop05]

hi and welcome to MrExcel
why do you need this middle workbook?

If there is another way of thinking if it, if you don’t mind sharing?

 

[Zot]

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]

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]

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

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]

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

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

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]

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

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]

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")