Script to copy cell value to another workbook

Microsoft Microsoft office professional...
November 3, 2009 at 13:08:11
Specs: Windows XP, 1024
I wanna know are there any VBS script that can be able to copy a cell value of an workbook to another workbook.

If there is a script that can do it without the workbook being opened, that will be great.

See More: Script to copy cell value to another workbook

Report •

November 3, 2009 at 13:17:07
You can't write to a closed workbook.

However, macros could be written to open the workbook, write/copy/link the values and then close the workbook.

Why not give us some more detail and we'll see what we can offer.

Report •

November 3, 2009 at 17:08:09
basically every week, I will receive 17reports from each branch of my company, and I will have to combine their #s together in order to calculate the % of each one.

The format of my master file will be always the same, and the format and the file name of the 17 reports will be identical each month. I only need to copy and paste them into my master excel sheet.

Alternative solution:
I actually have thought about using google doc. But, do I have to setup 17 google accts?

Report •

November 4, 2009 at 05:51:45

Here is an example for combining your branch office reports.
This example has three 'branches'

There is a Master workbook named 'HeadOffice.xls' containing Four worksheets.
One W/s named Totals and one W/s for each branch named Branch01, Branch 02 and Branch03

There are three branch workbooks: Branch01.xls etc.
Each branch Workbook contains one worksheet named BranchReport

Each branch report contains data in the range A1 to N50.
For this example Branch01.xls contains 10000 in cell A2
Branch02.xls cell A2 contains 20000 and
Branch03.xls cell A2 contains 30000

The HeadOffice.xls Workbook has a standard Visual Basic Module 'Module1' containing the following code:

Option Explicit

Private Sub BranchCombine()
Dim strPath As String
Dim strWbName As String
Dim intBranches As Integer
Dim strBranchWs As String
Dim n As Integer

On Error GoTo ErrHnd

'set path to workbook locations - include the closing "\"
strPath = "C:\Temp\"
'set number of branches
intBranches = 3

With Workbooks("HeadOffice.xls")
    For n = 1 To intBranches
        'get branch workbook name - starts at cell A1 - n is row
        strWbName = .Worksheets("Totals").Cells(n, 1)
        'open Workbook
        Workbooks.Open (strPath & strWbName)
        'create Branch Worksheet name
        strBranchWs = "Branch" & Format(n, "00")
        'copy BranchData to HeadOffice Worksheet
        Workbooks(strWbName).Worksheets("BranchReport").Range("A1:N50").Copy _
    Next n
End With
'Save updated HeadOffice Workbook
Exit Sub

'Error handler
End Sub

Each example Workbook contains a total sales value in cell A2 of the BranchReport Worksheet
The Totals worksheet in HeadOffice.xls looks like this after the macro code has run:

	A		B		C
1	Branch01.xls	10,000.00	16.67
2	Branch02.xls	20,000.00	33.33
3	Branch03.xls	30,000.00	50.00
4	Total		60,000.00	100.00

The formulas on the HeadOffice "Totals" worksheet are:
B1 =Branch01!A2
B2 =Branch02!A2
B3 =Branch03!A2
B4 =SUM(B1:B3)
C1 =IF($B$4=0,"",B1/$B$4*100)
Note the $ signs
Drag formula in C1 down two more rows
C4 =SUM(C1:C3)

To enter the code in HeadOffice.xls:
Enter the VBA window by clicking Alt + f11 (The left Alt key and function key #11 at the same time)
In the Project Explorer window (usually on left), find VBAProject(HeadOffice.xls)
Right click on it and select Insert then Module (not Class Module)
Double click Module1 which is under the Modules folder
Enter the code in the main window.

Alternatively use the Control Toolbox toolbar to create a command button on the Totals worksheet and add the code to it (In Design mode, right click button and select view code). Enter 'Option explicit before the opening 'Private Sub CommandButton1_Click()'
and enter the code, but excluding the opening sub and the 'End Sub' as these are already in place for the button.

In the code change the path to whatever you are using in the line

strPath = "C:\Temp\"

To run the code place your cursor somewhere in the code after Sub and click f8 to single step and f5 to run

Instead of the

you could use SaveAs and create a new filename for each month or whatever.
The new filename could be created from a combination of fixed Text such as "HeadOffice" and the Month and Year, or you could include an Input box and type in the name each time.

If one of these options is of interest to you please ask and I or someone else will suggest some code.

Hope that this gives you the basics for a solution


Report •

Related Solutions

Ask Question