I care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get
Compile Error:
Expected: list separator or )
[code] Sub Macro1() Dim wks3 As Worksheet Dim wks4 As Worksheet Set wks3 = Worksheets("Sheet3")
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
I have code that i use to copy a worksheet of information in one workbook to a worksheet in another workbook. All i need is some guidance on how to copy a second worksheet from workbook 1 to a second worksheet in workbook 2. Should be fairl straight forward.
In the below i am copying the sheet Phone_data to a second work book sheet also called Phone_Data, i would like to include in the same proccess a sheet called Sur_Data from workbook 1 copy to a sheet called Sur_Data_R in the second. All the other features like find next empty row also apply.
I have a macro that compares two sheets (in a workbook that might contain 10 sheets). Currently, the user enters the names of the two sheets in two input boxes. Is there a way that the user need only to click on a sheet tab and the sheet names are used in the macro? (this would eliminate any misspellings)
I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
I am looking for a code that would copy the data from each worksheet in a given workbook and then paste to just one worksheet within a different workbook. The Sheet names are auto generated when I run this canned report but the naming structure is always the same...the first worksheet is named Repair Details and then the next sheet is named Repair Details_1, the next sheet is named Repair Details_2 and so on for every sheet in workbook. So I would like to copy all of the data(Headers to last cell) and then paste in a worksheet(ex: Master Repair Report.xlsx and the worksheet could be titled Master Repair Details) on a different workbook, then the next sheet would copy from the one under the header to the last record and paste to the same workbook. This process would repeat for every worksheet in the Repair Details Workbook and paste to Master Repair Details worksheet in the Master Repair Report workbook.
I need to copy a range of values (actually two ranges). The ranges are of fixed size. Le't say A2:D20 and E2:H20.
Both workbooks have the exact same layout, and they both have the same named worksheet (in this case it's called Entry). However the actual filename of the source workbook is unknown. I know numerous users have changed the filename of the xls file.
I want to instruct them to open the old and new workbook, and open a third workbook containing the copy macro, they run the macro and it copies the data.
How can I reference a known worksheet name but of an unknown workbook name esp when the source and destination share the worksheet name?
I have a spreadsheet where a user inputs a client name (ClientName). I will create a new Workbook with the ClientName along with the current date. 2014-03-03John Doe.xls
I am trying to now input data into 2014-03-03John Doe.xls by specifying it in VBA but I can't seem to reference it properly.
I have this code which opens another workbook and defines the two open workbooks as MainWkbk and NextWkbk:
Code: Private Sub wbo() 'open file selected from dropdown on Macros tab and define the two open workbooks Dim singlepath As String
[Code]....
This works great for going between the open workbooks within the same sub but I also need to be able to use MainWkbk/NextWkbk in another sub within the same module and cannot seem to get it to work!
I tried adding this to the top of the module;
Private MainWkbk As Workbook Private NextWkbk As Workbook
but this doesn't work, it always says the variable is not set when in the next sub.
I'm having difficulty using a worksheet variable with control objects on worksheets. The following code is successful at getting the value of the text in textbox txtDCN.
Public Sub test() Dim text As String text = Worksheets("Master Table").txtDCN.Value End Sub
The following code gives compile error "Method or data member not found" on the last line of code. I've searched around a bit all over the web but cannot find any examples of this same error. It's obvious that the compiler cannot figure out the reference to the text box but I cannot figure out why for the life of me.
Public Sub test() Dim ws As Worksheet Dim text As String Set ws = Worksheets("Master Table") text = ws.txtDCN.Value End Sub
I require 1 more variable to take this MySName and assign it to a workbook name and make it is as .xls as i required this workbook name to be used later to close the workbook
For ex: MySName = ActiveSheet.Name
this gives MySName =aaaa
Now i want like MyWBook = aaaa.xls
But aaaa i want it from variable as we cannot hardcode it.
I have a Macro that opens a closed file to VLookup some info and pull it to my spreadsheet then close that workbook. the problem I have run into is writing the Macro correctly so if I were to change the file name of the workbook I am running the macro with that it will still use the range declared for the workbook. I have colored the file path that I want to dim in Blue so that it will be easier to see what im trying to do. here is my current macro that works perfectly unless I save my current workbook under a different file name...
I am opening a workbook using a macro. I need to run a macro in the workbook I am opening when it opens. I have created a variable containing the workbook name but don't know how to use it to run the macro.
Dim WBName WBName = ActiveWorkbook.Name Application.Run _ "'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'!aStartProcess"
'DIST_91094_EDTABS_DIABETES CARE_07072006.xls'! needs to be replaced by the name stored in WBName
aStartProcess is the name of the macro in that workbook I want to run.
Ive just finished writing a macro that copies data from different parts of one workbook into a new workbook. However after doing all that my boss now informs me that the first workbook will be renamed regularly.
Is there a way adding a reference to a workbook that is not dependant on the name? Possibly declare it as a variable at the beginning or something? The indexing is not possible either as they might have multiple Spreadsheets open at any one time.
I am trying to transfer a value from the active worksheet in one workbook to another workbook. I keep getting a subscript out of range error on the line that uses activeworksheet command when I run my code.
I have a macro on a button click that copies and pastes the template worksheet into the same workbook on a dialy basis, i would like to rename the "template" copied file to a unique worksheet name based on a date ie 31_3 (31st march) and so on, the cell that provides the date changes on a daily basis to the next one, however when the script runs it uses the original macro recording date rather than the new one!, the changable cell is AS2.
I am trying to run some code when a worksheet is selected.
I'm doing this using a macro currently (code below), but this requires that i use a quick menu button and i would like to make it a form button and assign a macro to it (so i can distribute the file without everyone having to create a menu button).
I would like to replace (or modify) the code below to select any sheet other than the ones named "a" and "b".
Code: Sub ImportAlarms() Dim thisSheet As Worksheet Dim targetSheet As Worksheet
On Error GoTo failed
Set thisSheet = Application.ActiveSheet Set targetSheet = Sheets(TARGET_SHEET)
I'm replacing a specific sheet on a workbook with another one, but I want the users to be able to browse to a file, replace the sheet with the new one and re-establish the links on the replaced sheet with the target workbook. This macro is running out of the workbook that supplies the replacement sheet. The idea is that the user opens this workbook, runs the macro, browses to the file they need to fix and hey presto! I'm doing something stupid with my variable name, I'm sure it's syntax but I don't know what I'm doing wrong. The code is posted below.
Sub Macro1() Dim ToBeFixed ToBeFixed = Application.GetOpenFilename Workbooks.Open (ToBeFixed) Sheets("!").Visible = True Sheets("!").Select ActiveWindow.SelectedSheets.Delete.....................
I have a Excel workbook with two sheets - 'MainDataSheet' and 'ArchiveSheet' .
The 'MainDataSheet' has 5 columns and one of the column is 'status'.
The 'MainDataSheet' will have a command button 'MoveData' which will trigger the VBA macro to move data rows from 'MainDataSheet' to 'ArchiveSheet' . Only the rows having value set to "MOVE" in the 'status' column have to be moved.
I just started venturing into VBA. I'm trying to pull the data from a different workbook that will have the previous month as the sheet's name. I can't get the dynamic part of the code.
I am trying to open a workbook, assign it a variable and use that variable later. here is what i have
[Code] ......
When I try to activate it later on in another procedure using:
[Code] copy to clipboard
I receive an "Automation Error"
I have tried declaring it as a public variable, even a global variable and cannot get this to work.
All I'm really wanting to do is open a workbook (where the name will change depending on the book being opened), assign that workbook a variable name, and use it later in the code.
I need to reference a different workbook in a while loop. Each iteration, PopFive increases by 3, as I want to pull the numbers of every third row. How does the syntax of the line go after the " ! "?
Ive wrote some code that goes into a workbook. This code then opens up a "master" workbook which has 18 blank tabs in it and then proceeds to open up 18 other named workbooks one at a time and copy some date from these workbooks to the master (i.e workbook 1's data goes to the master workbook on tab "1".)
I have this working no problem but here is my snag.
The workbook name changes every week to correspond with the date (i.e 1_14DEC2011.xls then 1_21DEC2011.xls).
Ideally id like to be able to create a variable for the latter half of the filename so that i can apply this variable to each filepath but i cant quite get it to work.
A small sample of my code is below (and yes i know it is very blunt but so is my knowledge at this stage!)
Code: Sub collate() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual
I have one workbook called Ratecards, which contains various tabs which are named based on a client code. e.g. ABC02
I then have another workbook which has employees in column B and the client code in C3.
The current Vlookup is =VLOOKUP(B8,'[ratecards.xlsx]ABC02'!$A$5:$N$168,4,false) which works fine for one client.
I want to substitute the ABC02 in the vlookup for cell ref C3, as the contents of C3 is variable and I want it to tell the sheet which ratecard to refer to.
