Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Reference to another file Results

I am trying to write some code to transfer data to another sheet, but I am getting an object error. I am not sure why this is happening, so any help would be great.
If needs be I can post a sample file, but I am thinking it is just a silly mistake that I have left something out.

Code:

Sub Name_Change()
'
' Name_Change Macro
'
   FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
   For i = 2 To FinalRow
   Dim strName As String
   Dim WSMei As Worksheet  'Data worksheet
   Dim WSCont As Worksheet 'Contract (2) worksheet
   
'   Copy Worksheet

    Set WSMei = Worksheets("Data")
    
    
        Sheets("Contract").Copy After:=Sheets(2)
        
    Set WSCont = Worksheets("Contract (2)")
        
'   Copy and transfer Data to new sheet
        Worksheets("WSMei").Cells(i, 3).Copy Destination:=WSCont.Cells(7, 6)

'   Chnage Worksheet name

        Worksheets("WSMei").Select
        WSCount.Name = "Cells(i, 19).Value"
        Sheets("Contract (2)").Select
        Sheets("Contract (2)").Name = strName
    Next i
End Sub
Cheers

Hi-

I have a macro file that opens along with another file and the macro is
run against the file. I copy a bunch of buttons from the macro file to
the new file and currently have all of the code in the macro file as a
place to store it. The problem I am having is the buttons reference
the modules in the macro file. So when the macro file is closed and
you press the buttons in the new file that is created from the
macro....it errors out.

How can I get the code that is stored in this macro file into modules
in the new file that is created so that I don't have to keep the macro
file open in order to use the buttons?

TIA,
Chris

Hi -

I am using Excel 2007.

I have a workbook with several sheets. There's not much data, but there are a few small macros and formulas in one sheet which refer to another sheet. (and when I say not much data, I mean like 10 rows of 15 columns on each sheet).

When I save it in native 2007 format (xlsm), the file takes up maybe 500KB.

When I save it in a version that office 2003 can read it, it takes up 27MB!

Is there a reason for this? Is there a way to change that?

We feel like we need to offer 2003 format as we are sending this spreadsheet to many schools and we don't know if they've all upgraded to 2007 (or 2010) yet.

Thanks!

Hi

This is probably a simple solution, but I am balked!

I have created 12 "Month" workbooks (consisting of 28-31 day worksheets which are then collated onto another worksheet - used for daily bookings). I have then created a "Yearly" summary workbook that collates this data in various ways and presents the data as graphs.

I want to retain these spreadsheets in a folder holding the Master Copies.

Here is where the problem begins:

I then want to copy all 13 files into another folder (say Finacial Year 2010_2011) for daily use and future retention.

BUT the references in the Yearly Summary sheet still refer to the files held in the Master Copies folder. Eg Master Copy
" ='I:BackupsSound & Light Show[July Sound and Light Show.xls]July Summary'!$C$4 " (ignore the double quotes), but when the workbooks are copied to a new folder this happens
" ='I:Documents and SettingsAdministratorMy DocumentsSound & Light ShowMy MASTERS[July Sound and Light Show.xlsx]July Summary'!$C$10 "

Note the reference to the My Masters folder?

I want to be able to copy these 13 files each year so as to create a new Financial Year Folder (without having to do any more coding).

I want the references to the files to be relative to only those now in the new folder. Something like " ..[July Sound and Light Show.xlsx]July Summary'!$C$10 " ?

Is this possible?

PS

Note: I have been struggling with this for days - and now test it and it works as I expected - must have been repeating the same mistake over and over again! LOL I knew this would be simple! A PICNIC problem! (Problem In Chair Not In Computer)

I have inherited a monstrous speadsheet on which all data appears on a single sheet rather than being broken up across several sheets in some logical fashion.

One portion of this monster is a set of quite a few form letters, each of which reference various cells elsewhere in the spreadsheet.

I want to move all of these letters, together, to another tabbed sheet as a first step in reorganizing the monster.

Simply copying and pasting the cells or columns doesn't work. It fails in different ways depending on which pasting options I employ.

A very simplified version of my problem appears on the 5 sheets of the attached file, with what I hope is just enough further detail about the difficulty.

By the way, I also tried using the "Insert Copied Cells" option when pasting but since this failed with the exact same results as one of the other options I didn't include the results in the example shreadsheet.

I am grateful to anyone who will take a look at this problem.

Hi Guys, i need help!

I am working on an excel dashboard to display life information about energy consumption.

there is currently a bespoke syatem which does this but we need the dashboard to be changed so the job has fallen to me.

Energy usage across the site is recorded in a spreadsheet (automatically) every half an hour and is saved in a folder named CSV data, however the files are saved in a format which i assume is something for the current package: *.ew7

I can open and view the data by clicking "open as" and selecting excel.

I can reference to this data from another excel spreadsheet (dashboard) whilst they are both open it will update.

However when i close all spreadsheets and open just the dashboard it tells me that the links cannot be updated.

I need a solution or a way around this so i can reference from one workbook to another.

Has anyone had any experience of this kind of thing? can anyone offer a humble analyst a bit of advice?

I was not able to attach the file here (as its in the ew7 format) but i have uploaded it to rapidshare for anyone who wants to take a look...

http://rapidshare.com/files/295860196/01280909.ew7.html

many thanks for reading, and even more thanks for help

I am new to macros and VB with excel. I need to have a pivot tables page filed updated using a reference to another cell that is populated via a drop down.

I have pulled several examples from the net, however i am unable to get them working.

The one example I have is below. I have copied the code and replced all values, yet i still feel i am missing something?

Please help.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String

strField = "Calling Number"

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False

    If Target.Address = Range("C2").Address Then
        
        For Each ws In ThisWorkbook.Worksheets
            For Each pt In ws.PivotTables
                With pt.PageFields(strField)
                    For Each pi In .PivotItems
                        If pi.Value = Target.Value Then
                            .CurrentPage = Target.Value
                            Exit For
                        Else
                            .CurrentPage = "(All)"
                        End If
                    Next pi
                End With
            Next pt
        Next ws
    
    End If

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


Hi Friends:
I have a workbook(say "myprog.xls") with 4 sheets--val1,rpt1,val2,rpt2.
Manually(not in VB module)I copied data from val1 and paste them with Link in rpt1. So data in rpt1 has reference to val1. Similarly, in rpt2 I copied and link data from val2.
Now at the end I did SaveAs with the 2 sheets--rpt1 and rpt2 and created a new workbook named "Totalrpt" which has only 2 sheets--rpt1,rpt2.
Now each time, I am running my main program "myprog.xls" its perfectly creating my new workbook "Totalrpt". But when I am going to OPEN this Totalrpt manually(say by double clicking) its opening an alert screen. This alert screen asking to Update or not to Update the excel file.
and saying that this file has reference to another workbook.

I dont want to see this alert screen. Each time I will open "Totalrpt" it will automatically have the latest data and will not prompt me to Update or not. This is for the user and user will only open the latest data without any vb code and without any alert screen.

How can I do it in VB.

My SaveAs code:
set ns = Thisworkbook.range(array("rpt1","rpt2")
ns.copy
with ns
.parent.SaveAs filename "Totalrpt.xls" Fileformat:=xlnormal
end with
Thanks anyone

hi guys,

need some help here on copying data from cells on one worksheet to another worksheet on the same workbook. i tried to search around but still could not find which is the best to solution.

here is a short detail of what i want to do:

worksheet 1 (input):
i have a range of cells divided into fields which contains data. these fields are the same on worksheet 2 (which serves as my database sheet)

problem: i want to create a command button which after clicking would copy the data from worksheet1 to corresponding fields in worksheet2.
(anybody can help with formulating the code?)

i have attached the file i was working with for easier reference.

hoping someone can help with this.
thanks very much.

Hello,

I am trying to do some calculations using as a source another(closed) workbook. I am using SUMPRODUCT and if I specify the range everything is fine, but if I refer to a named range then I am getting an error when the source file is closed. Is there a workaround for this problem?

Thanks for the help!

OK, let me see if I can explain this clearly enough..... I have a group of files (each day of individual months) where I reference the same cells form each of the idividual files. For example, every day of September has its own file, only differing in file name, where I reference cells from each day or file to another file (sort of a montly total if you will). Now in the monthly total file, I reference the same cells in the individual (daily) files. In the monthly total file, I am trying to copy and paste the cell reference and just change the file name (I know of no other way to copy and paste these types of references... maybe there is a quicker way??). For example, I will set the cell references up with a file named 9-1-07 and only change the file names (to 9-2-07 and 9-3-07 for example). I ahve done this in the past with no issues, but now everytime I change the file name, excel asks me for the individual reference (small window pops up) where I have to navigate through the file architecture to choose the file reference. If I do not do this, I get an error even if the actual reference has not changed from what was tyoed (meaning that before the reference was "chosen" it was exactly the same). I have done this in the past where there were no issues with just changing the file names in the reference, so perhaps there is a setting that has changed that I am not familiar with ?? Any help would be greatly appreciated....

Hope I explained well enough
Pat

I have a master worksheet that pulls data from other worksheets in the
workbook. I want to copy those formulas to other workbooks which have
different text in them so I can't just make copies of the original file.
However, I don't want the target workbooks to refer back to the original
file, only to the same worksheets within each workbook that the original
workbook referred to. How do I do that?

Hi everybody,

I want to copy a worksheet to another file(or worksheet).
when i change something in the original worksheet every changes will
take place in the other one.
it is something to refer to a cell. but i want to refer to whole
worksheet.

I have more than a fifty workbooks. I want to copy all of them into a
new workbook in different sheets.this workbook will contain all my
excel documents in one workbook but in different sheets. however when i
change something in the original files every change should take place in
the new workbook.

how can i do this in excel?
many thanks in advance.....

--
lazmanyakPosted from http://www.pcreview.co.uk/ newsgroup access

I'm having an issue with what seems like it should be a simple formula. I'm using VLOOKUP to reference from one workbook to another. Both files are on the same machine on the desktop. Any help here?

Here's the formula:

=VLOOKUP(B5322,[MASTERSCHEDULE.xls]MSTRSCHEDULE!$G$2:$G$621,[MASTERSCHEDULE.xls]MSTRSCHEDULE!$F$2:$F$621,FALSE)

Hi
I want to get data from 1 workbook to another workbook with the following criteria.
1. Search for sheetname from 1st to 2nd
2. Search for date (1st file's filename will be date)
3. Search for the data from 1st to 2nd (Serial number wise)(if serial number not found in 2nd file then skip, else type 0)

I've attached the file below for your reference.
http://www.mediafire.com/?l4ssfnz3lljsytl

Originally Posted by jaslake Hi JesseBurton
The attached workbooks contain code that places hours from Card Swipe Time Log 2Ps Uploaded 12_07_2009.xlsx (Source File) into Fall 09 - 10 Study Session Hours - Test.xlsm (Target File).
Both files must be open and, as you requested, the file names are hard coded in the procedure. This is going to cause you headaches in that the name of your source file will continually change. If you want this modified so you can select the file, let me know.
I changed the sheet name to "Source" in Card Swipe Time Log 2Ps Uploaded 12_07_2009.xlsx as you indicated you do this in another procedure.
Let me know if you have any questions or issues. The code is below.
John
Option Explicit
Sub UpDateTime()
    Dim WKb1 As Workbook, WKb2 As Workbook, wsWKb1 As Worksheet, wsWKb2 As Worksheet
    Dim FindStuff As String
    Dim rng1 As Range
    Dim LR As Long
    Dim hCell As Range
    Dim x

    Application.ScreenUpdating = False
    
   '***************************************************************
   'replace next four lines with your open workbook designations
   '***************************************************************
 
    Set WKb1 = Workbooks("Card Swipe Time Log 2Ps Uploaded 12_07_2009.xlsx") 'Source workbook
    Set wsWKb1 = WKb1.Sheets("Source")
    Set WKb2 = Workbooks("Fall 09 - 10 Study Session Hours - Test.xlsm") 'Target workbook
    Set wsWKb2 = WKb2.Sheets("Hours")
    
    LR = wsWKb1.Range("A" & Rows.Count).End(xlUp).Row
    
    wsWKb2.Activate
    With wsWKb2
        .Range("A1").End(xlToRight).Offset(0, 1).Select 'find last column in Target sheet
        ActiveCell.Select 'select the column
        x = ActiveCell.Column 'find the last column number
        x = x - 1 'x-1 is the offset from Column A
        Range(ActiveCell, ActiveCell).EntireColumn.Insert 'insert a new column at x
        ActiveCell.Value = Left(wsWKb1.Range("B1").Value, 10) 'place the date from Source _
                                                              'sheet in row 1 of the new column
    End With

    For Each hCell In wsWKb1.Range("A1:A" & LR)
        FindStuff = hCell.Value 'find each ID in Card Swipe file
        If FindStuff <> "" Then
            With wsWKb2.Range("A:A") 'find ID in target file
                Set rng1 = .Find(What:=FindStuff, _
                                 After:=.Cells(.Cells.Count), _
                                 LookIn:=xlValues, _
                                 LookAt:=xlWhole, _
                                 SearchOrder:=xlByRows, _
                                 SearchDirection:=xlNext, _
                                 MatchCase:=False)
                If Not rng1 Is Nothing Then 'if you found it
                    rng1.Offset(0, x) = hCell.Offset(0, 3) 'copy the hours from source to target
                Else
                End If
            End With
        End If
    Next
    Application.ScreenUpdating = True
End Sub

Hi John,

THANK YOU SOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO MUCH!!!!

I haven't had a chance to try anything out yet, but am putting your code in place now. I will end up tweaking it a bit, because I didn't want the source file name hard coded exactly, but the source file is actually "saved as" and re-named earlier in my macro using the same convention so I wanted to code that same naming convention (so that it would generate the same file name, and then refer to the same file) So I'm going to look through the code and try to tinker a little and see what I can figure out, and see if I can figure out exactly what you're doing.

If I run into any problems or have any questions I will definitely ask.

Again THANK YOU SOOOO MUCH! I REALLY appreciate it.

-Jesse

Hi guys, new to the forum so straight in with a request for help! Apologies!!

I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.

I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.

I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.

If anybody could help out with this it would be very much appreciated!! Thank you. Perhaps in the future I can help someone else out in return.

EDIT: It's within the 'Opens each BRAG to write template' section.

Private Sub CommandButton6_Click()

Dim app As New Excel.Application
Dim book As Excel.Workbook
Dim Worksheet As Excel.Worksheet
Dim FileS As FileSearch
Dim F As Variant
Dim x As Integer
Dim score(1 To 10, 1 To 250)
Dim cellreference As String
Dim sp As String
Dim spPM As String
Dim spPD As String
Dim spProg As String
Dim spLeadTeam As String
Dim bragDirectory As String
'Creates a list of all spreadsheets within a specified directory

bragDirectory = InputBox("Enter the path in which the BRAGs are stored:")
If bragDirectory <> "" Then
Set FileS = Application.FileSearch
With FileS
.NewSearch
.Filename = "*.xls"
'.LookIn = InputBox("Enter the path in which the BRAGs are stored:")
.LookIn = bragDirectory
.SearchSubFolders = True
.Execute
End With

x = 1

'Opens each BRAG to write template
For Each F In Application.FileSearch.FoundFiles
app.Visible = False '<- BRAGs are opened in the background and this is done invisibly
Set book = app.Workbooks.Add(F) '<- Opens workbook using file location determined above
app.Range("e50").Activate
app.Range("e50").Select
ActiveCell.FormulaR1C1 = "Box"
ActiveCell.Text = "box"
MsgBox ("active cell: " & app.Range("e50").Text)
book.Close savechanges:=True '<- Closes the Excel workbook BRAG without saving changes
app.Quit '<- Closes the Excel application


Next F
Else
MsgBox ("BRAG location not entered")
End If


Hi all,

I am looking to write some code to copy info from one workbook to another.

I have currently wrote the following
This selects the cells i want to copy and copys them
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
I then want to open a new excel file and copy the cells i have selected (above) into this worksheet at column A, row 2 (as the worksheet i want to copy to already contains the appropriate headings)

my above code works for selecting and copying the information i want to paste into the other workbook but i dont know how to write code to "open another workbook, select sheet 1, select column A row 2, paste, save, close"

If someone could adivse it would be much appreciated! i am a bit of a begineer with code, ive been on a short course, but that baffled me (was an intermediate course and i clearly should have gone on a beginner one haha)

I also have Excel for Dummies (9 in 1 desk reference) and Excel VBA programming for dummies! they are helpful but i cant seem to find what i am looking for.......

THANKS

Greetings to all and thank you for any help provided. The code below is not of my making. However, I have been able to use it as a solution to my problem. I am trying to move all the files that include "xls" from my source folder into my destination folder. When I run the macro, it is doing all that it should do, except that it is not grabbing the files that have xls in them and moving them to their destination. I will be honest, since the code is miles above my understanding, I was only able to tweak it in a limited fashion. Can any one point out what I might be leaving out for the code not to work as it should? Thank you
Option Explicit
 ''MUST set reference to Windows Script Host Object Model in the project using this code!
 
Sub Copy_Files_To_New_Folder()
     ''This procedure will copy/move all files in a folder to another specified folder'''
     ''Can be easily modified
     
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim PathExists As Boolean
Dim strSourceFolder As String, strDestFolder As String
Dim x, Counter As Integer, Overwrite As String
     
    Application.ScreenUpdating = False
    Application.EnableEvents = False
     
     
    strSourceFolder = Sheets("Controls").Range("C8").Value 'Source path
    strDestFolder = Sheets("Controls").Range("C12").Value 'destination path
     
     'below will verify that the specified destination path exists, or it will create it:
    On Error Resume Next
    x = GetAttr(strDestFolder) And 0
        If Err = 0 Then 'if there is no error, continue below
            PathExists = True 'if there is no error, set flag to TRUE
            Overwrite = MsgBox("The folder may contain duplicate files," & vbNewLine & _
            "Do you wish to overwrite existing files with same name?", vbYesNo, "Alert!")
            'message to alert that you may overwrite files of the same name since folder exists
            If Overwrite <> vbYes Then Exit Sub 'if the user clicks YES, then exit the routine..
    Else: 'if path does NOT exist, do the next steps
            PathExists = False 'set flag at false
            If PathExists = False Then MkDir (strDestFolder) 'If path does not exist, make a new one
    End If 'end the conditional testing
     
    
        For Each objFile In objFolder.Files 'for every file in the folder...
         
         'Below:
         'If statements can be used to evaluate parts of file name for file type,
         'or using the InStr method below, can identify parts of a file name to conditionally
         'copy files based on any part of the file name.  For non-extension checks, replace
         'what is inside the " " to check for that within the file name.
         
         If InStr(1, objFile.Name, ".xls") Then ' Will copy only Excel files
         'If InStr(1, objFile.Name, ".txt") Then ' Will copy only Text files
         
        'objFile.Copy strDestFolder & "" & objFile.Name 'use the destination path string, add a / separator
and the file name
         
         objFile.Move strDestFolder & "" & objFile.Name  'Syntax for MOVING file only, remove the ' to use
         
        Counter = Counter + 1 'increment a count of files copied
         
         End If 'where conditional check, if applicable would be placed.
         
         ' Uncomment the If...End If Conditional as needed
         
        Next objFile 'go to the next file
     
MsgBox "All " & Counter & " Files from " & vbCrLf & vbCrLf & strSourceFolder &
vbNewLine & vbNewLine & _
" copied/moved to: " & vbCrLf & vbCrLf & strDestFolder, , "Completed Transfer/Copy!"
'Message to user confirming completion
     
Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
     
    Exit Sub
     
NoFiles:
     'Message to alert if Source folder has no files in it to copy
    MsgBox "There Are no files or documents in : " & vbNewLine & vbNewLine & _
    strSourceFolder & vbNewLine & vbNewLine & "Please verify the path!", , "Alert: No Files
Found!"
    Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
     
    Application.ScreenUpdating = True 'turn screenupdating back on
    Application.EnableEvents = True 'turn events back on
     
    Exit Sub 'exit sub here to avoid subsequent actions
     
ErrHandler:
     'A general error message
    MsgBox "Error: " & Err.Number & Err.Description & vbCrLf & vbCrLf & vbCrLf & _
    "Please verify that all files in the folder are not currently open," & _
    "and the source directory is available"
     
    Err.Clear 'clear the error
    Set objFile = Nothing: Set objFSO = Nothing: Set objFolder = Nothing 'clear the objects
    Application.ScreenUpdating = True 'turn screenupdating back on
    Application.EnableEvents = True 'turn events back on
End Sub


Hi Guys,

I need to open another excel file using a maco, but I want the filename situated in a cell, rather than it forming part of the code.

Eg. I want to enter the filename in Cell C2, and then run the macro for it to open the file specified in C2.

This is because I would like to change the file that is being referred to in C2 at any time.

Is this possible?

Thanks for the help and time in advance...


No luck finding an answer? You could always try Google.