VBA Tutorial - Call Code From Another Workbook

Is there a website and/or youtube video(s) that you could suggest where we can learn VBA code? All about variables and actually writing code? For beginners?

I have seen many websites but none of them really give a breakdown of what the variables mean or how to write a program using fresh code (from scratch), something simple to something complex.

I would like to learn more about how to automate tasks associated with reporting, including using macros. Thank you for your help.

Is there a website and/or youtube video(s) that you could suggest where we can learn VBA code? All about variables and actually writing code? For beginners?

I have seen many websites but none of them really give a breakdown of what the variables mean or how to write a program using fresh code (from scratch), something simple to something complex.

I would like to learn more about how to automate tasks associated with reporting, including using macros. Thank you for your help.

Dear Experts,

I have a problem & a question this tutorial on mrexcel.com: http://www.mrexcel.com/articles/macr...ily-report.php. I'm trying to utilize this in a PO system for work. Unfortunately, I keep receiving a Run-time error '9' (subscript out of range) on this specific line:

Code:

Sheets(TabName).Copy After:=Workbooks(ControlFile).Sheets(1)

The code is strictly copy/pasted into my VBA editor, except changing the names inbetween each of the quotes ("") for specific sheet/cell names. I'm not a programmer, but I've read through the code and logically can't deduce why it shouldn't work for me.

My other related question is that I would like to have the new worksheet sent to the end of the active workbook, so would substituting "Sheets(Sheets.Count)" at the end of the above line of code work to send it to the end of the workbook?

Thanks for your help ahead of time.

Hello,

This is my first question to the board. I apologize in advance for the long summary.But I have used this site as a reference many of times! I am working on a spreadsheet that has phone numbers dialed by specific extensions and the call duration for each call made. What I am looking to do is multiply call rate (depending if the call was made to a long distance or international phone number) by the call duration. If Long distance calls are $0.05 per minute and International are $0.07 per minute. So if the "dialed number" begins with "1-###-###-###" then is is long distance and if it begins with "011-##-###-###-####" then it is an International call.

Here's an example:
Col C
0:09:26
Col D
18586632210
0:15:54 18774682136 0:24:36 13106312700 0:00:02 011525550107780 0:00:22 18887510624 0:00:34 0115215517984540 0:02:18 011525550107733 0:26:52 011525550107733

All,

I have multiple workbooks ( with unique names) under same folder. Each workbook has "Report-Corn" sheet.

I would like to copy cell "P15" from each (workbook-> report-corn -> P15) and paste to Master.xls workbook -> Sheet 1 one after another which is also located under same folder.

Can someone please help to provide how to go with this one?

(Excel 07)

In EXCELFILE1 I have a macro (MACRO1) that is pulling data from cell A4 in spreadsheets 1-50 (63 total spreadsheets in the workbook) and listing that data on a Summary spreadsheet in the same EXCELFILE1.

In EXCELFILE2 I have a macro (MACRO2) that is pulling data from cell A4 in spreadsheets 1-50 (63 total spreadsheets in the workbook) and listing that data on a Summary spreadsheet in the sameEXCELFILE2.

Here's the problem:
In EXCELFILE3, I need to make a macro that will take the data that MACRO1 is pulling and MACRO 2 is pulling and have them listed on a summary sheet without either EXCELFILE1 or EXCELFILE2 being open. Is this possible?

How can you call a function from one workbook to another

iTemp = workbooks(“Workbook1�).module(“Module1�).GetTotalRows

Thanks in advance,

Stewart Rogers

hi,

I am traying to call macro that is available in excel addin.
my code is like this:

Public Sub aaaa()
Dim myarray() As Variant
Dim givenpair As String
givenpair = "USD - JPY"
Application.Run "'volquary.xla'!DataExtract", givenpair, myarray()
For i = 1 To 12
For j = 1 To 24
Sheet1.Cells(i + 20, j).Value = myarray(i, j)
Next j
Next i
End Sub

I am trying to pass empty array to addin and get it filled there....

but the problem is addin returnes empty array only


can anyone tell me how can I do this.

thanks & regards,
Sandeep Singh

I am trying to copy a Named Range in another workbook but I have some code that protects and unprotects all sheets. I need to call that code before I can access the Named Range and copy it. The problem seems to be that the workbook that I am calling is selected from a userform I created and the Workbook name is passed into a variable.

How can I pass the variable to the file part of the Application.Run "Filename.xls!UnprotectAll" syntax when the file name is stored in a string variable.

Code is as follows:
VB:

Private Sub cmdCompare_Click() 
     
    Dim WBookName As String 
    Dim File1 As String, File2 As String 
    Dim Range1 As Range, Range2 As Range 
     
    File2 = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name 
    Debug.Print "File2 is:" & File2 
    If txtCompareFile.Text = "" Then 
        MsgBox "You must choose a valid file above to Compare.", vbOKOnly 
        Exit Sub 
         
    Else 
         
        File1 = txtCompareFile.Text 
        Debug.Print "File1 is:" & File1 
         
    End If 
     
    Unload frmCompare ' closes the userform
    Application.Workbooks.Open File1 'opens the target file
    WBookName = ActiveWorkbook.Name 'passes the active workbooks (TARGET FILE) name to a variable
    Application.Run WBookName".xls!"UnProtectAll  [COLOR=red] '*** I NEED TO CALL THIS SUBROUTINE IN THE ACTIVE WORKBOOK (TARGET FILE / WBookName) to unprotect it before copying ***[/COLOR]
    ActiveWorkbook.Sheets("Tracking Master").Range("TM_WIID").Copy 'copies the desired range in the target file
    ThisWorkbook.Activate 'activates the source workbook
    ThisWorkbook.Worksheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count) 'inserts a new worksheet in the source workbook
    ActiveSheet.Name = "COMPARE" 'names the new worksheet
    Sheets("COMPARE").Cells(1, 1).Select 'selects top left cell
    ActiveSheet.Paste 'pastes copied range to selected cell
     
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines