run a code that passes the word alarm from worksheet 3 to worksheet 2 as 100 instead of as the word alarm..... but i need it that it can copy the number 100 on any cell range between cell 2 to cell 10 on worksheet 2 depending on the other data i copy
View 1 RepliesI am trying to loop the following code for a total of 15 worksheets without copying and pasting that same code 14 more times for each worksheet. Right now it is only executing the code on the "CAN" tab. Is there a way to make it loop where indicated below?
The 15 worksheets are:
CAN
USA
ASG
Gallia
[Code]......
On Sheet1 I have a macro link. On the Setup sheet, in cell C14, I have the name I want to display in the link on Sheet1. The wording is changeable in the Setup sheet. This all works fine.
Now, I want to modify it. I want an if statement at the beginning of the macro that says. If Setup, cell c14 is blank, I simply want the macro to stop and do nothing. If Setup sheet, cell c14 is not blank, I want the macro to continue as normal.
For example, I have 50 clients. In 1 workbook, I have a sheet for each client. When I'm finished with a particular client, I need their one sheet to place in their file and be done with it.
The current process is to open that 1 global workbook, copy and paste the sheet I need into a new workbook and go from there.
I was wondering if there is a simpler way to achieve this with VBA coding?
Ideally, I would like to click an object button and then be prompted to select a worksheet from a list that contains all current non-hidden worksheets.
Once I select a worksheet, I would then be prompted to save as and select a file path. That would save that specific client worksheet in its own file that I selected.
I have many sheets in an excel workbook. Each sheet contains some data. I want data of all sheets into one sheet in the same workbook. The data of sheet 3 should appear below the data of sheet 2 & so on. I leave the first sheet of the workbook blank so that data of remaining sheets can be consolidated in the first sheet. Therefore I wrote the following code:
Public Sub consolsheets()
Dim a As Integer
Dim rng, rng2 As Range
a = a + 1
For a = 2 To Worksheets.Count
Worksheets(a).Activate
Set rng = Worksheets(a).UsedRange
rng.Copy
[code].....
Data of row 3 of sheet 2 (ccccc) is overwritten by data of row 1 sheet 3 (11111). Similarly, data of row 3 of sheet 3 (33333) is overwritten by data of row 1 of sheet 4 (@@@@@).
This problem is not faced if the sheet 1(blank sheet) has some data.
I want to try and do is as follows:
- go through all the worksheets in the workbook
- if the sheets begin with A, C or P
- and if monthly index = 0
- sum the range of cells - B33:L51
- output the value into cell L52
- etc
So far this is what I got
For i = 1 To Sheets.Count
If Left(Sheets(i).Name, 1) = "A" Or Left(Sheets(i).Name, 1) = "C" Or Left(Sheets(i).Name, 1) = "P" Then
If (MonIndex = 0) Then
SUM(B33:L51)
ElseIf (Index = 2) Then
SUM(B34:L51)
ElseIf (Index = 3) Then
SUM(B35:L51)
End If
My questions really are - how do u write the code for it to generically know to sum the range B33-L51 for like all sheets required. - and is it possible to output that value to a specific cell in each sheet?
My Thread title should have read covert code TO run on all worksheets with same word in Name. I have had a look at some other pieces of code in this forum but they are quite the same as I want to do....I have tried a few things but each time I get an error ...so for sure I am not modifying the code quite right, I ahve been missing something for what I now want it to do. So I wonder if someone could Please review these two pieces of code...they of course are both for running on just one worksheet in a wookbook.
I would like them both be able to run, still as tow separete pieces of code as they are, on all and only worksheets that have in their worksheet name a key word in this case the word "Region" and that those worksheets all in a single workbook but the workbook does have other worksheets in it I dont want the code to run one on - but those worksheets do not have in their worksheet name the word "Region".
Sub test()
Dim r As Range, txt As String, ws1 As Worksheet, i As Long
Set ws1 = Sheets(1)
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For i = 16 To 21
Again:
For Each r In ws1.Range(ws1.Cells(6, i), ws1.Cells(Rows.Count, i).End(xlUp))
If r.Value <> "" Then
If Not .exists(r.Value) Then
.Add r.Value, Nothing
Else
txt = txt & "," & r.Address(0, 0)
If Len(txt) > 245 Then
ws1.Range(Mid$(txt, 2)).EntireRow.Delete
txt = "": .RemoveAll: Goto Again
End If .............
I have the following code, I have it running to just work on Sheet 2 but I would like it to be able to run on mulitple sheets, not all but a range like from Sheet2:Sheet30.
[Code]........
What I am wanting to do is to transfer Names(Row B7:B160) to a different work sheet IF the Row next to it(C7:C160) is filled out with a time. I will be setting up a command button that once say 10 names have times next to it, this data then will transfer to another work sheet.
I have done below coding, however it will only transfer data in B7 and C7, if I try to add B7:B160 it comes back as Missmatch. Below is what I have done for the Command button.
Private Sub CommandButton1_Click()
Dim AdviserName As String, Time As Integer
Worksheets("Sheet1").Select
[Code]....
I have data on sheet "master", which with the macro (Ctrl+Q), splits in unique values in tabs and another macro sub Send_files() (Ctrl+e) triggers a selection in each split-sheet in email body to recipient.
Problem arises when I want to run a macro which collectively send files after running in each sheet. A simple code would be:
Sheet2.Activate
Application.Run "Test2.xlsm!Send_Files"
Sheet3.activate
Application.Run "Test2.xlsm!Send_Files"
etc.
But I don't know how much list of sheets can go on. So I want Sub Send_Files to run on all sheets in workbook after splitting data from master.
Refer attached wb Test2.
I am trying to run code to update Excel Worksheet from the value in a cell.
The code is -
Sub upDateSheetNames()
'Standard Module code, like: Module1.
Dim ws As Object
For Each ws In Worksheets
[Code] ......
Although it does actually work i.e. populates the worksheets with the correct values when I run the code a dialogue box appears indicating - "Run-Time Error '1004' Application Defined or Object Defined Error"
When i select the 'Debug' option the "ws.Name = ws.Range("Q1").Value" line is highlighted.
I saw the below code in another thread that does almost what I need it to do. The only thing is that I need to only select certain worksheets, not all. Is there an adjustment I can make to this code or is there something I can do differently?
Each worksheet has a table on it as well, is there a code I could use to just combine certain tables?
Sub debit1()
'Combine all worksheets to the Summary sheet
'Created by Trevor G 30 June 2011
Dim ws As Worksheet
[Code]......
My macro does some calculations for a worksheet. I need the macro to loop through all the worksheets regardless of how many worksheets there are. My first sheet is a summary page with the names of the subsequent sheets that the macro needs to do calculation on. I need the macro to recognize the worksheet names in the summary page and run for each worksheet name. For instance the next time I run the macro I may have fewer sheets of more sheet names in the column. It sounds almost like an Indirect function problem.
View 9 Replies View Relatedcode to sort all the worksheets in a workbook...
View 13 Replies View RelatedI have this code that will create 3 worksheets with the name "New Name" and the # ( 1/2/3 ) ..
How would I change the code if I want 3 worksheets with different name?
Like Sheet1 then Sheet 2 then Sheet3 or any other name?
'To add worksheets and change name with one code
For i = 1 To 3 'Creates 3 worksheets
Set ws = Worksheets.Add
ws.Name = "New Name" & i
Next i
Let's say I have 6 Worksheets in my Workbook with the following names:
Sheeta1
Sheeta2
Sheeta3
Sheetb1
Sheetb2
Sheetb3
And I have three Subs that I want to run, one Sub for two specific Worksheets.
How could I code my Subs so that the first Sub runs on Sheeta1 and Sheetb1, the second Sub runs on Sheeta2 and Sheetb2, and the last Sub runs on Sheeta3 and Sheetb3?
As you can see, the 1, 2 and 3 in the Worksheet names are what links the Subs to the Worksheet.
I have got a workbook with 20 or more worksheets. Every quarter I need to paste/append new data on these worksheets (which has already got existing data from previous quarters). All worksheets have different rows of data, for eg One worksheet may have 10rows, the other may have 50. Hence when I am writing my code, I cannot specify the destination cell as for eg A11, or A51.
How do I write up a VBA code that looks for the last row of data on these various worksheets and then pastes the new information on the next row(new row).
I recorded a macro in an Excel Workbook which contains 65 worksheets (--this is something received on a quarterly basis for which I have no control). The macro is successful within the workbook created however, a new quarter's data may or maynot have some of the referenced worksheets. 64.9 Waiv - 1 may exists and the others (2, 3, 4, etc) not. The macro fails and prompts for Debug if a worksheet does not exist.
Sheets("64.9 Waiv - 1").Select
Sheets("64.9 Waiv - 2").Select
Sheets("64.9 Waiv - 3").Select
Sheets("64.9 Waiv - 4").Select
Sheets("64.9 Waiv - 5").Select
Sheets("64.9 Waiv - 6").Select
Sheets("64.9 Waiv - 7").Select
Sheets("64.9 Waiv - 8").Select
Sheets("64.9 Waiv - 9").Select
Sheets("64.9 Waiv - 10").Select
Is it possible to alter the macro to look for and only invoke the code if a worksheet past 1 were to exist?
This is what I am requesting the macro do:
Sheets("64.9 Waiv - 1").Select
Range("B9").Select
ActiveWindow.FreezePanes = True
ActiveWindow.SmallScroll ToRight:=5
Range("J8").Select ...............
I have spreadsheet application which have a ton of sheets and macro into it, and i work on devlop and program it , and add new feature and codes
i wana Module_Macro to find and replace lines of vba code and replace it with lines of codes .
i know ,i should use vbide and vbcompnent , but i dont know from where to start ?
I have a workbook with various pages that are all hidden except the main page, on the main page it allows users to select items froms drop down boxes that returns a figure to cell B7 on the selection page.
What i would like to do is press a command button and the hidden worksheet that relates to that figure in cell B7 opens which allows the users to print it then after printing or closing the workbook is hidden again.
I have made a basic holiday planner, where each employee has one worksheet which shows 52 x 5 day weeks.
When an employee asks for holiday, I put a 1 in the corresponding day (cell). What I need to happen is that if 4 first aiders all ask for holiday on the the same date a warning User form loads up.
The names of the 4 worksheets are;
"Jason L 2014", "Sharon L 2014", "Alan B 2014" and "Chris M 2014".
The ranges for the 5 day weeks are
"B7:F7", "L7:P7", "V7:Z7"
"B10:F10", "L10:P10", "V10:Z10"
"B13:F13", "L13:P13", "V10:Z13"
"B16:F16", "L16:P16", "V16:Z16"
[Code] ........
The code should not run the User form until the sum of the same cells across the 4 worksheets = 4.
By this I mean if 3 of the first aiders book B7 off, the code searches the 4 worksheets and if the sum is 3 then nothing happens. However if the 4th and final first aider tries to book the same day off, when the code runs it totals 4 and the User form runs.
I just started playing with creating my own macros. I've written a macro to pull all the names of the worksheets in a workbook, now what I want to do is write a piece of code that will pull data from a cell in those worksheets.
So for instance I have all my worksheet names in cell A1 to A10, I want to pull the data from cell E1 from every worksheet in A1 to A10. Can someone help me with a sample code to work with. I'm still reading Power Progamming haven't gotten so far yet.
I have a workbook with multiple worksheets. On some of those worksheets a have merged cells, so in order to paste new data I need to unmerge them first. Below is the code that I wrote for it. However it doesn’t work and returns “Run-time error '1004': Application-defined or object-defined error”. I looked at this post List Maximum Value From Each Worksheet and tried adding With – End With, but it failed too. As far as I understood the problem is that the Worksheets/Sheets object does not support UnMerge property, but I may be wrong. If I add ws.Activate line (test2) everything works fine.
However I was wondering if there is a way to do it WITHOUT activating the worksheets.
Sub test1()
Dim i As Integer, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "5 - Top Network Facilities", _
"5b - Top Arb Facilities"
For i = 0 To 9
ws.Range(Cells(2 + i * 5, 1), _
Cells(6 + i * 5, 1)).UnMerge
Next i
Case Else
End Select
Next ws
End Sub
I am trying to create a program to automate the gauging figures on oil barges, currently everything is done by hand and takes approximately 20 minutes to complete. I have the charts entered for the tanks already and have the code set so that when you click on a tank "gauge" it will enter the "volume" which corresponds to that gauge on a totals sheet. Here is where my problem is coming in
The barges are gauged at four points
"before loading"
"after loading"
"before discharge"
"after discharge"
I want to set a worksheet as the default page with four command buttons that let the user select which operation he wants to perform. before load figures. after load figures and depending on which operation they select have it enter the volumes in the appropriate cells on the totals sheet. So if someone selects "loadopen" command button I want the following code to run on my worksheets............................
I am trying to write a macro that will find duplicate listings in two worksheets by row. I don't really want to do a user form unless I have to.
View 9 Replies View RelatedI am looking for the VBA code to copy worksheets (with formatting) and save to a specific folder. The steps I am need to follow are:
Create a copy of the first worksheet
Save it to a specified folder and name it with the worksheet name
Repeat with all worksheets until the end of the workbook
the following code filters data and copies to worksheets specified in code, can this code be shortened to make the macro run faster. Also in my second criteria how can I put "Contains "PT2" in the criteria as nothing happens when i run the macro, is *PT2?
With shtRec
.AutoFilterMode = False
.Range("A7").AutoFilter Field:=8, Criteria1:="LEHMAN", Operator:=xlAnd
With .AutoFilter.Range
On Error Resume Next
Set rng = .Resize(.Rows.Count - 1).Offset(1).SpecialCells(12)
On Error GoTo 0
End With
If rng Is Nothing Then
MsgBox "No data to copy"
Else
rng.Copy shtLehman.Range("A6")
End If
.AutoFilterMode = False
End With
I have a code that i got from here which will send the Workbooks named in col A to email addresses in col B, as below..
but i need it to send the named Worksheets from the currently active Workbook.
The name of the Worksheet will be contained in col A.
Sub SendWkbs()
Application.ScreenUpdating = False
Dim wks As Worksheet
Dim iRowA As Integer, iRowB As Integer
Set wks = ActiveSheet
iRowA = 2
On Error GoTo EH
Do Until IsEmpty(wks.Cells(iRowA, 1))
iRowB = 2
I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.
Private Sub UserForm_Activate()
Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet
Dim rngFound As Range
Set wsCB = Sheets("Current Bonds")
Set wsLL = Sheets("Landlords")
Set wsBond = Sheets("Bond")
TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy")
RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy")
Application. ScreenUpdating = False
10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number")
strFindMe = MyBond
With wsCB
Set rngData = .Range("c13:c490").......................
I have many worksheets which ends with the word " data". Is there a way to delete all the data sheets at one go through VBA code.
View 3 Replies View Related