Moving Thru Columns ( And Worksheets )
Hi,
I am working on a macro in excel.
I wanted to read the contents of the cell which is 10 columns to the right of my active cell.
How can I do that.
Also I wanted to know how can I move the control from the current active cell to a particular cell in a different worksheet.
Please suggest a method
amol
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Moving Between Different Worksheets
For my excel file, i have different worksheets. Would like to ask if its possible to lock it so that users can only go to the other worksheet only by pressing a button on the excel worksheet.
thanks.
Moving Through Worksheets
I have a subprocedure called 'AgressoUpload' that i wish to run on a selection of worksheets in a numerous workbooks. The subroutine works fine when executed on an individual worksheet so i know that this part of the formula is fine.
The 'AgressoUpload' procedure needs to be run on all worksheets with the prefix "WO_" at the start of thier name.
Code:
Sub WorksheetCheck()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ActiveSheet.name = "*WO_*" Then
Call AgressoUpload
End If
Next ws
End Sub
This seems to do nothing. I have also tried pasting the relevent sections in front of the AgressoUpload sub to see if that will loop but it does not.
I have also tried creating a variable and setting that as the worksheet name but this does not work.
Can anyone shed anylight ontop where i am going wrong?
Moving Worksheets Between Files
I am trying to write a macro that will copy worksheets from one workbook to another. The excel sometimes won't allow it.... when I right-click the sheet's tab and choose "move or copy" the "to book" box only shows the same book or <new book>.
I tried and record that action on another computer. I copied the code
Code:
sheets("sheet1").copy after:=workbooks("book2").sheets(1)
when I ran the code, it stuck and said :
Code:
Automation error
The object invoked has disconnected from its clients
and the "target" book vanished completely and it stuck my computer.
I had to close it with the task manager
*** ??
Moving Worksheets In VBA (Excel)
Hi,
I am trying to move a worksheet from one workbook into another, and then saving that workbook. so far i'm using:
sub move_sheet()
Workbooks.Open FileName:=AADirectory(LoopMeAround) & "" & ExcelFileName(j)
ActiveSheet.move Before:=Workbooks(TempWorkBookName).Worksheets(TempSheetName)
Workbooks(ExcelFileName(j)).Close SaveChanges:=False
end sub
well its that sort of syntax. - Using the VBA Move command
(the above might not work, as i'm just typing this off the top of my head). Now what i'm using now, works , but i am creating a lot of worksheets and loads of new workbooks (about 50 workbooks, and each will contain about 70 worksheets.)
My problem is, that Excel keeps on crashing (i get a dr. watson error). I need to build more stability into the coding, perhaps via this API thing i've heard of?
or am i better off creating a worksheet object?
comments, suggestions, and the relavent coding would be most appriciated.
Many Thanks,
PS ho do i use the vb tag/scrip commands to get different colours on this post?
Selective Moving Of Data From Active Worksheet To Other Worksheets
I am currently trying to copy/move data from the active worksheet to other worksheets.
My active worksheet contains the data which are input through a form. I would like to keep it that way. In addition, I would like to use the location as the criteria to make a duplicate of the data on the active worksheet and move it to the respective worksheets named by the respective locations.
Is there anyway to do it? I try to use If...then...else statements but seems like something is wrong with the code.
Hope someone could advise me. Thanks.
Listview, Moving Columns?
Well, i cant really explain the title in just a few words. I want to add 2 more rows to my listview, but i dont want to add them at design time (alot of code to change then).
The problem is, that i want the two rows that i add, i want to move them so they are positioned in-between two columns that are already there.
So, i added rows 12,13 to my listview during design time. I want 12 and 13 to appear to be inbetween columns 1,2.
Hope it makes sense
Moving Columns In A Datagrid
I am writing to a datagrid from a database, and all goes well. All of my information is displayed correctly. However, when the datagrid with all of the data is displayed I would like the user to be able to put the columns in the order in which he/she can besxt view the data. Can this be done? If I need to clarify more please let me know. Thanks.
Moving Columns In DataGrid In Realtime
Boss wants me to modify a program that uses DataGrid. He saw another program that allows the user to click on a column and drag it to the beginning column, thus moving the draged column to the new position.
Can this be done using DataGrid or is there another grid that comes with VB6 that will do this??
Moving Columns On A Listview (Report View)
I want to programmatically set the Position of the columns in a list view.
This is an "after thought" so I am trying to do it on already loaded listviews.
I see from a little experimentation that I can easily move the column headers by simply changing the .position property.
But that does not bring over the data for that column. (Just the header)
Do I have to manually move the data or there an easier way?
(By "manually move the data" I mean set up a temp array, copy the column's data, update new column after moving other column into original col number)
Regards,
MSHFlexrid Autosize Question/problem - Extra Columns After Sizing Columns To Autofit
I'm trying to use the MSHFlexGrid control, and it's almost a great thing, but not quite.
I wrote a VB app ( I'm a newbie, and my background is midrange program coding specifically RPG on an AS/400 so please keep that in mind. Event-driven programming and VB are new to me.) that will allow users to "drive" the application and select how they want to see the hierarchy. I let them choose from the pertinent fields and they are popped into a listbox control. The order in which they appear in the listbox control determines the hierarchy, and the underlying code writes the SHAPE, APPEND statements, etc...
So far, great... The problem I have is that since I have no way of knowing what hierarchy the user will select when they run the app, I have no way to know which columns will appear in the grid, or in what order, so there is no way to pre-size the column widths.
I found an autosize routine here posted by FlyGuy, so I put that code behind a button on the form called "AutoFit". When I run the code, it resizes the columns beautifully, but the column headings disappear from the sections over the "children" "grandchildren" "greatgrandchildren" , etc... columns.
Then if I do a collapse/expand, I find that the MSHFlexGrid control has a bunch of empty columns now between the original parent and child columns. The number of columns which appear are equal to the total number of child, grandchild, greatgrandchild, etc... columns fully expanded outward, so in the example shape statement below, I wind up with 16 empty columns on the control. Every subsequent autosize leaves the control with more blank columns. If I scroll far enough to the right, I find the children with their column headers.
The question I have is what is causing this ( the empty columns and the disappearing headings ) to happen, and can I do anything to stop it? I'm not sure it's usable for the end users if this continues to happen. Should I ( or can I ) dynamically set the column widths on the control at run time? Should I load the control by trying to iterate through all the recordsets and maybe do some sort of writing to the control directly? Is there a way to stop the control from showing the repeating columns in the children ( like maybe especially if I write directly to the control )? Can I ( if I write to the control ) format the columns so that numbers are right justified?
I'm using VB6 under Windows Xp Service Pack 2, and service pack 6 for Visual Studio 6 has been applied.
Here is how I was doing it: ( Warning: ugly code alert ):
Dim cnmx As ADODB.Connection
Dim rsMX As ADODB.Recordset
Set cnmx = New ADODB.Connection
Set rsMX = New ADODB.Recordset
With cnmx
.CommandTimeout = 0
.ConnectionTimeout = 0
.Provider = "MSDataShape"
.Open "dsn=<the dsn>;User ID=<the account>;Password=<the password>"
End With
rsMX.Open SQLShapeStatement, cnmx, adOpenStatic, adLockReadOnly, adCmdText
On Error Resume Next
If Err Then MsgBox Error
Set Form3.MSHFlexGrid1.DataSource = rsMX
Form3.MSHFlexGrid1.CollapseAll
rsMX.Close
cnmx.Close
Set rsMX = Nothing
Set cnmx = Nothing
Load Form3
Form3.Show
The value of the SQLShapestatement is:
SHAPE {SELECT USAGECOSTCENTER as Dept, cast(round(SUM(COST), 2) AS decimal(9, 2)) as DeptCst From V_ISSREC_WITH_COST_AND_ASSOCIATES Where issuedate between '8/1/2007' and '8/17/2007' GROUP BY USAGECOSTCENTER ORDER BY DeptCst DESC} AS Level1
APPEND ((SHAPE {SELECT USAGECOSTCENTER as Dept, coalesce(left(EQNUM,6),(right(USAGECOSTCENTER,4) + '99')) as Equip_Num, cast(round(SUM(COST), 2) AS decimal(9, 2)) as Equip_NumCst From V_ISSREC_WITH_COST_AND_ASSOCIATES Where issuedate between '8/1/2007' and '8/17/2007' GROUP BY USAGECOSTCENTER, coalesce(left(EQNUM,6),(right(USAGECOSTCENTER,4) + '99')) ORDER BY Equip_NumCst DESC} AS Level2
APPEND ((SHAPE {SELECT USAGECOSTCENTER as Dept, coalesce(left(EQNUM,6),(right(USAGECOSTCENTER,4) + '99')) as Equip_Num,ITEMNUM as Part_Num, cast(round(SUM(Quantity), 2) AS decimal(9, 2)) as Part_NumQty, cast(round(SUM(COST), 2) AS decimal(9, 2)) as Part_NumCst From V_ISSREC_WITH_COST_AND_ASSOCIATES Where issuedate between '8/1/2007' and '8/17/2007' GROUP BY USAGECOSTCENTER, coalesce(left(
EQNUM,6),(right(USAGECOSTCENTER,4) + '99')),ITEMNUM ORDER BY Part_NumCst DESC} AS Level3
Append ({Select USAGECOSTCENTER as Dept, coalesce(left(EQNUM,6),(right(USAGECOSTCENTER,4) + '99')) as Line_Num, ITEMNUM as Part_Num, description as Prt_Desc, cast(round(QUANTITY, 2) AS decimal(9, 2)) as Qty, cast(round(COST, 2) AS decimal(9, 2)) as Cst, issuedate as Iss_Dte, Name as Emp_Nme From V_ISSREC_WITH_COST_AND_ASSOCIATES Where issuedate between '8/1/2007' and '8/17/2007' ORDER BY Cst DESC, Emp_Nme, Iss_Dte} as Level4
RELATE 'Dept' to 'Dept','Equip_Num' to 'Equip_Num','Part_Num' to 'Part_Num' )) RELATE 'Dept' to 'Dept','Equip_Num' to 'Equip_Num' ))
RELATE 'Dept' to 'Dept' )
Any suggestions welcome.
Resizing Columns In One Listview To Automatically Resize Corresponding Columns In Another.
I'm currently building the front end to a VB6 application. This app is largely based on entering data into a Listview. In order facilitate data entry, I need to create an editable row beneath the listview, comprising of text boxes(unless there is a better method) and an "add" button. The problem is that when a column in the Listview is moved or resized, the array of textboxes need to reflect this change of order/size, as each textbox needs to appear beneath its corresponding column. Also, if the listview contains a large number of columns, and therefore a horizontal scrollbar, the textboxes will need to scroll also... I would be very grateful for any help..! Thanks...
Sorting Array With 5 Columns By Any Column Or Columns
Hi all!
I have an array with 5 columns:
- city
- first name
- last name
- birth date
- children quantity
How can I sort this array by any column, for example by 4?
And how can I sort it by few columns, for example:
sort by 5 then sort by 1 then sort by 3?
Delete Emtpy Columns, Deleting All Columns
I've tried almost everything I can think of at this point.
I have a large report generated, imported to Excel.
The beginning of the code (not shown), breaks all merged cells, deletes blank rows, etc.
Now, I'm starting at the far right of the report and want to move column by column to the left deleting empty rows to bring the report together.
Excel 2003 SP2, VBA 6.3
Current code (not working correctly) reads:
Rows("1:1").Select
Selection.ClearContents
Range("A1").Select
ActiveCell.FormulaR1C1 = "END"
Term = "END"
Range("BL1").Select
While Not (ActiveCell = Term)
ActiveCell.EntireColumn.Select
delcol = False
If Value = "" Then
delcol = True
End If
If delcol = True Then
ActiveCell.EntireColumn.Delete
End If
ActiveCell.Offset(0, -1).Select
Wend
Starts by clearing all contents of row 1, places "END" in A1 so the loop knows when to close. Loop begins in BL1 and moves left until it encounters "END" (in A1) and stops.
Any suggestions or assistance would help maintain my sanity at this point!!!
Thanks in advance.
Dave
Columns(n).Autofit Error Past 257 Columns
When I run my code I get an "Application defined or object defined error" "run time error '1004'"
I thought I saw another thread with the same problem but couldnt find it.
Code:
Private Sub WriteExcelFile()
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
' Create the appropriate instances of Excel and create a new sheet
Set xlApp = New Excel.Application
Set xlWorkBook = xlApp.Workbooks.Add
Set xlWorkSheet = xlWorkBook.Worksheets.Add
' Make the application visible - just toggle the setting
xlApp.Visible = True
Dim n As Integer
For n = 1 To UBound(arrRecords)
'Size columns to data
xlWorkSheet.Columns(n).AutoFit ' <----- This is giving me the error
' Enter a value into cells
xlWorkSheet.Cells(n + 1, 1).Value = Format(arrRecords(n).string1, "#0")
xlWorkSheet.Cells(n + 1, 2).Value = arrRecords(n).string2
xlWorkSheet.Cells(n + 1, 3).Value = Format(arrRecords(n).string3, "#0")
xlWorkSheet.Cells(n + 1, 4).Value = arrRecords(n).string4
xlWorkSheet.Cells(n + 1, 5).Value = arrRecords(n).string5
If arrRecords(n).string6 = 0 Then
arrRecords(n).string6 = 0
Else
xlWorkSheet.Cells(n + 1, 6).Value = Format(arrRecords(n).string6, "$###,##0.00")
End If
If arrRecords(n).string7 = 0 Then
arrRecords(n).string7 = 0
Else
xlWorkSheet.Cells(n + 1, 7).Value = Format(arrRecords(n).string7, "$-###,##0.00")
End If
xlWorkSheet.Cells(n + 1, 8).Value = arrRecords(n).string8
xlWorkSheet.Cells(n + 1, 9).Value = arrRecords(n).string9
xlWorkSheet.Cells(n + 1, 10).Value = arrRecords(n).string10
xlWorkSheet.Cells(n + 1, 11).Value = arrRecords(n).string11
xlWorkSheet.Cells(n + 1, 12).Value = arrRecords(n).string12
xlWorkSheet.Cells(n + 1, 13).Value = arrRecords(n).string13
xlWorkSheet.Cells(n + 1, 14).Value = arrRecords(n).string14
xlWorkSheet.Cells(n + 1, 15).Value = arrRecords(n).string15
xlWorkSheet.Cells(n + 1, 16).Value = arrRecords(n).string16
Next
' Save the spreadsheet
xlWorkSheet.SaveAs "C: est.xls"
' Quit Excel
xlApp.Quit
' Clean up objects
Set xlApp = Nothing
Set xlWorkBook = Nothing
Set xlWorkSheet = Nothing
End Sub
Thanks
Edited by - epatterson78 on 5/25/2003 1:03:58 PM
Code For Moving To The First Blank Cell In A Worksheet; Moving Left,right,up Or Down
I have created an application form as a userform in Excel. I need to send the data entered into my text boxes when the program runs to separate excel cells in a worksheet.
My problem is I can use code that will send the textbox entry to a specific cell eg. If my textbox is named Surname I can say
range("a5").value = surname but if I do that when I run the programme to enter the next record for someone else the first record will be overwritten.
I NEED A CODE LINE THAT WILL TAKE ME TO THE FIRST EMPTY CELL AND ALLOW ME TO MOVE DOWN OR TO THE LEFT OR TO THE RIGHT
Adding Columns To A Listview That Already Has Columns
Hi
I'm new to VB6 (I usually program in VB.NET but have been asked to make an enhancement to a VB6 application).
I have a listview which has columns defined within it in the designer. The code I am looking at then retrieves data from a db and puts it into the listview.
I've been asked to add another column in the middle of the listview e.g. there are 10 columns defined in the designer and this new column needs to go at position 6. However, the data that needs to go in the new column needs to come from a different query than the one that fills the other columns. I therefore thought that the best way to do it was fill up the original columns and then slot in the new column and fill it from another query. I've been searching around but can't find any example of how to do this.
Please can anyone help, or suggest a better way to do it? Thanks!
Moving Balls To Moving Images??
Please take a look at this as it is related to my question
Thread
is it possible for the create images to be an image type that is already drawn?
Cant Run Thru All Worksheets
i have a workbook with around 5 worksheets.
1 is sheetA, which holds all the data extracted from the database
1 is sheetB, which help forms the other worksheets with its data.
the other worksheets are holding data according to their departments (which is one of the field on sheetB). ie. 1 dept takes up 1 worksheet.
Code:
Private Sub Highlight()
Dim sheetA As Worksheet, sheetB As Worksheet
Dim i As Long, k As Long
Dim isheet As Long
Dim name As String
Set sheetA = ActiveWorkbook.Sheets("Sheet1")
Application.ScreenUpdating = False
For isheet = 1 To ActiveWorkbook.Worksheets.Count
i = 5
k = 2
With Worksheets(isheet)
While Not IsEmpty(Worksheets(isheet).Cells(i, 2))
If sheetA.Cells(k, 3) = Worksheets(isheet).Cells(i, 2) Then
Worksheets(isheet).Cells(i, 5).NumberFormat = "[Red]-$#,##0.00"
k = k + 1
Else
i = i + 1
End If
Wend
End With
Next isheet
Application.ScreenUpdating = True
End Sub
i wan it to be able to compare column B of all other worksheets wif column C of sheetA. if the values match, i wan the function to be able to change the numberformat of that particular row of data into "[Red]-$#,##0.00". but the problem is that if sheet 2 does not have any matching values, it will move onto sheet 3, which has matching values. After that when it moves onto sheet 4, which also has matching values, it is not able to change the numberformat. pls advise how to solve the problem anyone?
Compare 2 Columns With 2 Other Columns?
Hi,
anybody know of a way I can quickly compare 2 columns on sheet 1 with 2 others on sheet 2?
Eg:
Sheet1:
ColA ColB
A 1
B 2
C 3
D 4
Sheet2:
ColA ColB
A 1
B 2
C 6
E 5
D4 appears on sheet 1 but not on sheet 2, E5 appears on sheet 2 but not on sheet 1, and C has a diff value (3 on sheet 1 and 6 on sheet 2), so Sheet3 would need to show:
Sheet3:
ColA ColB
D 4
E 5
C 6
So far I'm using the code below, but it only works when comparing one column with another:
Code:
Sub Compare_Columns()
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim wsSheet3 As Worksheet
Dim rnLastC As Range, rnLastD As Range
Dim rnA As Range, rnB As Range, rnValue As Range, rnCell As Range
Set wsSheet1 = ThisWorkbook.Worksheets(1)
Set wsSheet2 = ThisWorkbook.Worksheets(2)
Set wsSheet3 = ThisWorkbook.Worksheets(3)
With wsSheet1
Set rnA = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With
With wsSheet2
Set rnB = .Range(.Range("A1"), .Range("A65536").End(xlUp))
End With
With rnB
For Each rnCell In rnA
Set rnValue = .Find(rnCell.Value, LookIn:=xlValues)
On Error Resume Next
If rnValue Is Nothing Or rnCell.Value <> rnValue.Value Then
On Error GoTo 0
With wsSheet3
Set rnLastC = .Range("C65536").End(xlUp).Offset(1, 0)
End With
rnLastC.Value = rnCell.Value
End If
Next rnCell
End With
Sheets("Sheet3").Select
End Sub
Any help would be greatly appreciated!!
Edit by italkid: VB tags do make your code easier to read
Copying Worksheets
Is it possible to copy an entire sheet, controls formula and all, to another worksheet without losing the same format as the original?
Targetting ALL Worksheets
I want to be able to target all worksheets and assign them all with the same property. Reading a previous post, I tried:
Code:
Dim mySheet As Worksheet
For Each mySheet In Worksheets
mySheet.DisplayGridlines = false
Next
But that seems to give me an error Can someone correct this for me, as well as explaining the line:
For Each mySheet In Worksheets
Thanks!
Renaming Worksheets
Hi,
I'm trying to rename my worksheets.
If you look at the sheet's properties in Visual Basic then you see a (Name) and another Name. I want to rename both in VB code to the same name. How do I do that, because the following code renames only one
sheetExample.Name = "NewName"
Can anyone help?
Thanx
Lynette
Worksheets In Excel
For some reason my insert worksheet option is not working in excel. Even I can't unhide worksheets that I have previously hidden. Some how anthying to do with worksheets is turned off. I can't figure out what's the reason. I can't even go to file/properties anymore. This is just in one of my excel files. All my other excel files and any new ones I'm creating are not causing this problem. Does anyone know why this is happening? Thank you,
Lina
Changing Worksheets
Is there a way to switch between different worksheets when using VB with excel? I have a workbook with 12 worksheets in it, each one has a different month on it, and I would like use a combobox to pick which worksheet to write to. Thanks for your help.
Pasting In Worksheets With VBA
I've been writing a fairly complex VBA / Excel 2000 application for a while and getting along fine, but suddenly I'm out of my depth!
Because Activating is a Bad Thing and seems to be getting me into other trouble I won't worry you with, I'm trying to cut and paste from one worksheet to another (working round the 256 char per cell limit issue) without changing the Active workbook. So I'm trying to change this code:
TempBook.Sheets("LL Rules Examples").Activate ' this works!
Columns("AY:AY").Select
Selection.Copy
ThisWorkbook.Sheets("LL Rules Examples").Activate
Columns("AY:AY").Select
ActiveSheet.Paste
to this (which fails!)
TempBook.Sheets("LL Rules Examples").Columns("AY:AY").Copy
Sheets("LL Rules Examples").Range("AY1:AY1").Paste
The error message is:
Run time error '438'
Object does not support this property or method
What's the correct version? TIA
Excel Worksheets
I have an application that will scan a table and sort the contents into an excel workbook. I need to be able to add sheets as I go along, but if the sheet does not exist then I need to create it, I have looked at scanning all the sheets but that can take time, is there a way of checking quickly if the sheet exists or not, maybe trying to activate it and if it does not activate use the error to try and create it. Any help would be appreciated.
Hiding Worksheets
Hi everyone. I am writing a VBA application and want to hide the underlying worksheet, or even the entire workbook. Unfortunately my code relies on getting index numbers of sheets within the workbook, and the index numbers seem to not take into account hidden sheets, which causes me problems. If a sheet has a particular index number, it will have a different number if one or more sheets are hidden. Is there any way that I can hide sheets but still search and manipulate sheets based on their index number?
Excel Worksheets
Somehow im posting a lot round these days...
So the question: How can you put values of textboxes into excel worksheets?? And print the sheet afterwards??
UncleSam
Using Two Separate Worksheets
I would like to save data to two separate worksheets. In my present program I am saving to only one with:
Set objWsheet = mobjExcel.ActiveWorkbook.ActiveSheet
What do I change to save data to two different worksheets of the same workbook or do I need to use two different workbooks? I so how is that handled?
Macro Across All Worksheets
Hi all.
I'm trying to get a macro I've written to run across all the worksheets in a workbook. Everything I have tried however results in the macro being run on the same worksheet and I am now in need of some advice as to how to get this to run.
If anyone knows of a way to do this I would be most greatful.
Multiple Worksheets
I am trying to run through all worksheets in my workbook using the for loop. The following code isn't working and I can't figure out why.
Code:
Option Explicit
Public Function Merge_Date_Time()
Dim index As Long
Dim worksheet As Object
For Each worksheet In Workbooks(ThisWorkbook.Path).Sheets
Do While Range("a1").Cells(index, 1) <> 0
Range("e1").Cells(index, 1).Formula = "=concatenate(a1, b1)"
index = index + 1
Loop
Next worksheet
End Function
Many Worksheets To One Workbook
Hello there,
I need to copy many unique worksheets into one workbook. These worksheets are one to each file (only one sheet in the unique workbook file). I would like to be able to select a number of files (workbooks with only one sheet) and merge them into one file (workbook).
Any help would be immensely appreciated!
JM
Combobox In Worksheets..
Can anyone please advise me on how to prevent user from moving or deleting comboboxes or textboxes placed in worksheets?
Cheers.
Iterate Through The Worksheets
Thank you for reading this. This is my first time using VBA in Excel.
Below is the code that I am using to change 2 cells on 9 worksheets in a workbook. This works for now, but if they add a worksheet to the template later this won't work for them.
Code:
Private Sub Workbook_Open()
Dim i As Integer
Dim mDate As String
Dim yDate As String
mDate = Format(Date, "mmmm")
yDate = Format(Date, "yyyy")
Sheet1.Range("A2") = mDate
Sheet1.Range("A3") = Sheet1.Name & " for " & mDate & " " & yDate
Sheet2.Range("A2") = mDate
Sheet2.Range("A3") = Sheet2.Name & " for " & mDate & " " & yDate
Sheet3.Range("A2") = mDate
Sheet3.Range("A3") = Sheet3.Name & " for " & mDate & " " & yDate
Sheet4.Range("A2") = mDate
Sheet4.Range("A3") = Sheet4.Name & " for " & mDate & " " & yDate
Sheet5.Range("A2") = mDate
Sheet5.Range("A3") = Sheet5.Name & " for " & mDate & " " & yDate
Sheet6.Range("A2") = mDate
Sheet6.Range("A3") = Sheet6.Name & " for " & mDate & " " & yDate
Sheet7.Range("A2") = mDate
Sheet7.Range("A3") = Sheet7.Name & " for " & mDate & " " & yDate
Sheet8.Range("A2") = mDate
Sheet8.Range("A3") = Sheet8.Name & " for " & mDate & " " & yDate
Sheet9.Range("A2") = mDate
Sheet9.Range("A3") = Sheet9.Name & " for " & mDate & " " & yDate
ThisWorkbook.SaveAs "J:MyDirMyFolder" & mDate & "Report"
End Sub
I'm sure there is a way to iterate(sp?) through the worksheets with a For...Next loop, but I tried:
Code:
dim sh as sheets
for each sh in thisworkbook
sh.range("A2")= format(date,"mmmm")
next sh
as well as
Code:
dim i as integer
for i=1 to worksheets.count
worksheet(i).range("A2")=format(date,"mmmm")
next i
and neither worked.
Thank you in advance,
Bart
Collection/worksheets
Been away for a while, so my memory is a little rusty.
I have this little program where I need to print a couple of worksheets in a workbook.
I thought that I could just declare a new Worksheets collection, and add my sheets to this collection, however it does not work like I thought:
Code:
Sub PrintAll()
Dim WS As New Worksheet
Dim SheetsforPrint As Worksheets
SheetsforPrint.Add (ThisWorkbook.Worksheets("Process Power"))
What is the proper syntax for adding a sheet to the worksheets collection?
Deleting Worksheets
I have little knowledge of VB within excel, and my level of skill is tinkering with existing macros and I'm getting out of my depth
I wonder if someone can give me some pointers with a macro I need, but is out of my skill range. I'd like a macro that deletes worksheets except for three named ones ....
Clearing Worksheets
is there any code that can clear a worksheet, without deleting it.
i need something that not only clears text but also chart objects as well.
thankyou.
Why? {Searching All Worksheets}
Hi all,
I'm trying to pinpoint specific data, which could appear on any worksheet in an Excel file... It worked yesterday (I know, famous last words).
Code:
For Each wk In Worksheets
intWkSht_COUNT = intWkSht_COUNT + 1
strWkSht_Name = Range("a1").Cells(1, 1).Parent.Name
' Sheets(strWkSht_Name).Select
' With Worksheets(strWkSht_Name)
' strCell_A_HOLD = .Cells(1, 1).Value
' If strCell_A_HOLD = "Admit Date" Then
' strCell_A_HOLD = .Cells(1, 44).Value
' If strCell_A_HOLD = "MRN" Then
' blnWkSht_FOUND = True
' Exit For
' End If
' End If
' End With
Next wk
My problem now is that the "strWkSht_Name" variable always remains as "Sheet1", no matter what the names of subsequent worksheets are within the file...
Any ideas?
Thanks,
RichS
Picts In Worksheets
I'm adding picts to a worksheet (via wksht.Shapes.AddPicture(...
AddPicture lets you set the destination rectangle. I'd like to preserve the original aspect ratio. Is there a way to go from jpeg to getting the actual bounding rectangle of the picture?
Deleting Worksheets
This is what I need to do: I add a worksheet to a workbook, if this increases the number of worksheets in the workbook beyond ten, then I delete the eleventh (last) worksheet.
Basically, how can I figure out if a workbook has more than ten worksheets (eleven) and if so, delete the last one.
Worksheets In Excel
I have done some research and found that the number of worksheets allowed to be created in a workbook is based on the systems memory.
I have an automated workbook that can, and will, create 200+ worksheets.
Now, once i get to a certain amount (120 the last time) it just stopped...ven the copy function was looping, no pages were created.
BUT, when i closed out the workbook, and then re-opened it, and started again, it created the remaining worksheets. So, I am guessing it was 'holding' on to memory as it was going along.
IS there a way to release any memory it is using when it creates a worksheet, so i don't have to have to close it out, each time, and re-open it?
FYI: I already have DoEvents in all of my LOOPs.
I also have this function:
Code:
Public Function Holding(Sec As Integer)
Dim i As Long
If Sec = 0 Then Exit Function
For i = 0 To Sec
Call Sleep(1000)
DoEvents
Next
End Function
And i use it after each 'grouping' of sheets to be created, wth 5 seconds as the value passed.
About Protecting Worksheets...
Hi there. If I want to protect all worksheets in a excel file using VBA codes, what steps should I take to achieve that purpose? I just want to prevent user from amending my worksheets using 'End macro' option to break my vba code and going straight to my worksheets.
I suppose first of all that I got to write some codes as follows to unprotect the locked sheets when initialising userform and lock and protect all the worksheets when I quit the vba application.
Code:
Private Sub UserForm_Initialize()
shNum = Sheets.Count
for prosh = 1 to shNum
worksheets(prosh).unprotect 123 Rem '123' is a password
next
end sub
Private Sub CommandButton14_Click() 'click to quit appln.
Unload Me
shNum = Sheets.Count
for prosh = 1 to shNum
worksheets(prosh).protect 123 Rem '123' is a password
next
Workbooks("ucfdemo.xls").Save
Application.Quit
End Sub
Does the above VBA code work?
Cheers.
About Adding New Worksheets.
Hi there.
I want to add to the workbook a monthly worksheet using the following vba code in my excel file but after I successfully added a worksheet according to the current month/year values, it prompted me with an error message. What did I miss in my code? Can my codes be further simpified?
Code:
Private Sub UserForm_Initialize()
Dim myMonth
Dim myYear
Dim myMY
Dim NmSheet As Integer
Dim i As Integer
myMonth = Month(Date)
myYear = Year(Date)
myMY = myMonth & "-" & myYear
NmSheet = Sheets.Count
For i = 1 To NmSheet
If Worksheets(NmSheet).Name = myMY Then
MsgBox "Monthly worksheet already existed!"
Else
ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = myMY
End If
Next
End Sub
Cheers.
Renaming Worksheets
Hi,
Is there a way of automatically changing the worksheetnames of workbooks (ie when they are opened, I want to set all available sheets to be Sheet1, Sheet2, Sheet3 etc). I need to save each worksheet under a different filename and this would make it much easier.
Thanks
Rename New Worksheets
I want to have a userform, with a combo box, which you celect the customer number. When the OK button is selected, excel creates a new spreadsheet and renames it to the customers firstname and surname with no space in between if this isn't possible then just the customer number.
I have tried to do this by using (can't remember exactly i deleted it cause i was annoyed cause it wasn't working):
Code:
Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name =JimCollins
Creating New Worksheets In Vb
does anyone know what the code is in visual basic for creating a new sheet with a string followed by a number generated by the code?
eg.
I want excel to create three new sheets labelled list1 list2 and list3.
Many thanks in advance
Worksheets Collection Help
I'm trying to rename the sheets in workbook2 the same order as workbook1. If the Name of the worksheet meets certain criteria the name should be carried over. However, My logic is failing in this sub:
Code:
For Each sht In Workbooks(stFileName).Sheets
If sht.name <> "Proposal Summary" And sht.name <> "Proposal Cost Breakdown" And sht.name <> "Hidden_Data" Then
Set Rng = sht.Range("A1:E300").Find("Equipment Total")
If Rng Is Nothing Then
MsgBox "An Error has occured the Field File can't be created."
Else
Endnum = Rng.Row
sht.Range("A1:d" & Endnum - 1 & "").Copy Destination:=Wkb.Sheets(iCount).Range("A1:d" & Endnum - 1 & "")
Wkb.Sheets(iCount).name = Workbooks(stFileName).Worksheets(iCount).name
Wkb.Sheets(iCount).Range("A4:B4").ClearContents
iCount = iCount + 1
End If
Set Rng = Nothing
'MsgBox sht.name & " :" & iCount
End If
Next sht
It works for the first sheet but then it gives me the error msg. Any help would be appreciated.
How Do I Collapse Many Worksheets Into One?
Hi
I have many worksheets all in separate workbooks that I need to collapse into a single worksheet, or failing that into one workbook. The final end result is to output them as a single tab delimited file.
Any suggestions are appreciated! thanks, John
|
TRACKER
