Create New Sheet
1. How do you create a new sheet in an existing excel file that as a specific name, i.e. Bill ?
I want to continue to add and remove sheets from an existing file.
2. How do you remove Data from a Cell on a sheet?
I know how to add information, but remove is something else.
Thanks for the help
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
How To Use VB To Create A New Sheet?
how to use visual basic to create a new sheet? it shoud be temporary and shoud be deleted after the program is run. eg. a calculation sheet is created by vb and before the result is outputed to the output sheet, this calculation sheet should be deleted?
thx!!
How To Use Vb To Create A New Sheet
how to use visual basic to create a new sheet? it shoud be temporary and shoud be deleted after the program is run. eg. a calculation sheet is created by vb and before the result is outputed to the output sheet, this calculation sheet should be deleted?
thx!!
Create Excel Sheet
Hi guys, I know this has been answered here before 'cuz I have seen it here, but now I can't find it. Fitting, huh? Anyway, can someone give me an example on how to create an Excel spreadsheet from VB and copy values from a recordset into it? I would be very thankful!
Thanks
Andrew
How To Create Excel Sheet Using Vb
hi guys
this is my first exp in creating excel sheet using vb
can any one help me pls
i want to create an excel sheet from vb
place a command button when i select the command button,all the records from ms-access should be placed in excel sheet through vb i am using ado
thanks
Syntax To Create New Sheet In Excel With VB6?
Hello Everyone,
I just need the correct syntax. For some reason, I can't find it anywhere. I have seen several Excel VBA snipets, but I'm doing something wrong in the conversion to usefull VB6 code.
This is what I last tried. I get extra numbered sheets though.
With xlWkBk
.Sheets.Add Before:=.Sheets("Monday")
Set xlWS = .Sheets.Add
xlWS.Name = strWSName
End With
It is important to me that I can place the new sheet in specific placement to existing sheets.
Thanks for the help!
Create And Write To An Excel Sheet
Hi,
Wondering if anyone can help me with the following. I have a bunch of data I need to transfer onto excel spreadsheets. Each time the user must create a new excel spreadsheet and then I need to name each column according to different variables I have and fill those colums with the data in each variable.
Let's say we have 3 variables, Cars, Toys and CPUs
A loop is started where as the first line of data I want on the first line of the spreadsheet is the titles; Cars, Toys and CPUs.
Second line should hold data like Toyota, Doll and ThinkPad
Third line should have let's say Ford, Yoyo and Heward Packard
and so forth until the end of the loop.
Basically I would need to know how to create a new spreadsheet, how to add a line and determine number of colums i guess. Seems easy enough but I can't figure it out, I'm used to using access.
I'd rather not use any controls if possible such as data control
Phailak
How Can I Create A Graphic Chart In The Same Sheet
Hello,
When I create a graphic chart from Vb, it create into a new sheet, how can I
do to create into the same sheet of the data???
I´m using this code, is there something wrong???
obj.ActiveChart.Location(1, "report")
"report" is the name of my sheet.
Thanks
Excel Programming Problem: Create A New Sheet
Hey guys.
I'm doing an I.C.T. Project for A-level.
Its an excel project (crap, i know! Would have been much better if it was a vb project).
Anyways, i gotta do it, so heres my problem:
Its a 'Budget Wizard'. i.e. you enter a few figures, and it calculates a budget for you.
I've created a custom toolbar, which is created when the workbook loads (Auto_Open macro).
On the toolbar, it has a save button. But as the workbook is protected, and is not designed to be saved, i need to select the area of stuff they will want to save, copy it, and paste it in a new spreadsheet, and then save that as whatever they want!
Pretty tricky for a novice like me..!
I'd be really grateful if any of you guys could help.
THanks,
AJ
In Excel 2003 Create A GOTO Sheet On The Fly
found some code on another Excel forum under some archives.
When I run the code listed at bottom, I get an invalid call or procedure argument error when I click the created menu button and end up at the following line of code:
Code:
.insertlines X + 0, "Option Base 1"
I have Excel 2003 running in Windows XP. I seperated the code as requested with the link above into modules 4-5 for two of the areas suggesting using its own modules and ThisWorkbook for the code being suggested as placed here.
Here is the code: Place this code in ThisWorkbook
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Delete_Button
End Sub
Private Sub Workbook_Open()
Create_Button
End Sub
'Used to create a button to call the routine
'So it can be used in any workbook that is open.
The following code needs to be in another module (module4)
Code:
'This code handles the Back button, note
'it is robust enough to handle or use for
'any routine in which a return from is required.
Public Sub SaveLocation(ReturnToLoc As Boolean)
Static WB As Workbook
Static WS As Worksheet
Static Rg As Range
On Error GoTo NoGo
If ReturnToLoc = False Then
Set WB = ActiveWorkbook
Set WS = ActiveSheet
Set Rg = Selection
Else
WB.Activate
WS.Activate
Rg.Select
End If
Exit Sub
NoGo:
MsgBox "Not set !"
End Sub
'To save the current location, call SetSaveLoc.
Public Sub SetSaveLoc()
SaveLocation (False)
End Sub
'To return to the saved location, call GetSaveLoc.
Public Sub GetSaveLoc()
SaveLocation (True)
End Sub
Finally, this code needs to be placed in another module also: Module5
Code:
Option Base 1
'Passed back to the function from the UserForm
Public GETOPTION_RET_VAL As Variant
Function GetOption(Title)
Dim TempForm
Dim NewComboBox As MSForms.ComboBox
Dim NewCommandButton1 As MSForms.CommandButton
Dim NewCommandButton2 As MSForms.CommandButton
Dim NewCommandButton3 As MSForms.CommandButton
Dim X As Integer, TopPos As Integer
Dim MaxWidth As Long, Ams As String, Ap As String
Dim ShName()
' Hide VBE window to prevent screen flashing
Application.VBE.MainWindow.Visible = False
' Create the UserForm
Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3)
TempForm.Properties("Width") = 300
' Add the ComBoBox
TopPos = 4
MaxWidth = 0 'Stores width of widest OptionButton
Set NewComboBox = TempForm.Designer.Controls.Add("forms.combobox.1")
With NewComboBox
.MatchEntry = fmMatchEntryFirstLetter
.Width = 200
.Height = 15
.Left = 8
.Top = TopPos
If .Width > MaxWidth Then MaxWidth = .Width
End With
' Add the Cancel button
Set NewCommandButton1 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton1
.Caption = "Cancel"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 6
End With
' Add the GO button
Set NewCommandButton2 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton2
.Caption = "GO"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 28
End With
' Add the Back button
Set NewCommandButton3 = TempForm.Designer.Controls.Add("forms.CommandButton.1")
With NewCommandButton3
.Caption = "< Back"
.Height = 18
.Width = 44
.Left = MaxWidth + 12
.Top = 50
End With
Ap = Chr(34): Ams = Chr(38)
' Add event-hander subs for the CommandButtons
With TempForm.CodeModule
X = .CountOfLines
.insertlines X + 0, "Option Base 1"
.insertlines X + 1, "Sub CommandButton1_Click()"
.insertlines X + 2, " GETOPTION_RET_VAL=False"
.insertlines X + 3, " Unload Me"
.insertlines X + 4, "End Sub"
.insertlines X + 5, "Sub CommandButton2_Click()"
.insertlines X + 6, " SetSaveloc"
.insertlines X + 7, " On Error Resume Next"
.insertlines X + 8, " Sheets(ComboBox1.Text).Activate"
.insertlines X + 9, " If Err.Number <> 0 Then MsgBox " & _
Ap & "Sheet " & Ap & Ams & " ComboBox1.Text " & Ams & Ap & " doesn't exists!"
.insertlines X + 10, "End Sub"
.insertlines X + 11, "Private Sub UserForm_Initialize()"
.insertlines X + 12, "Dim ShName(),X as Integer"
.insertlines X + 13, "ReDim ShName(Sheets.Count)"
.insertlines X + 14, "For X = 1 To Sheets.Count"
.insertlines X + 15, " ShName(X) = Sheets(X).Name"
.insertlines X + 16, "Next"
.insertlines X + 17, "ComboBox1.List() = ShName()"
.insertlines X + 18, "SetSaveLoc"
.insertlines X + 19, "End Sub"
.insertlines X + 20, "Sub CommandButton3_Click()"
.insertlines X + 21, "GetSaveLoc"
.insertlines X + 22, "End Sub"
End With
' Adjust the form
With TempForm
.Properties("Caption") = Title
.Properties("Width") = NewCommandButton1.Left + NewCommandButton1.Width + 10
If .Properties("Width") < 160 Then
.Properties("Width") = 160
NewCommandButton1.Left = 106
NewCommandButton2.Left = 106
End If
.Properties("Height") = 24 * 4 'no buttons + 1
End With
' Show the form
VBA.UserForms.Add(TempForm.Name).Show
' Delete the form
ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=TempForm
' Pass the selected option back to the calling procedure
GetOption = GETOPTION_RET_VAL
End Function
Sub GotoSheet()
Dim UserChoice As Variant
UserChoice = GetOption("Select a Sheet")
If UserChoice = False Then End
End Sub
Sub Create_Button()
Dim TopButton As CommandBarButton
Set TopButton = Application.CommandBars(1).Controls.Add(Type:=msoControlButton, _
Before:=10)
With TopButton
.Style = msoButtonCaption
.Caption = "GoTo Sheet"
.OnAction = "GotoSheet"
End With
End Sub
Sub Delete_Button()
Application.CommandBars(1).Controls("GoTo Sheet").Delete
End Sub
I close the spreadsheet and save, then open it and alas, the new menu called goto.. appears and I click on that and end up with the error mentioned at the top of this post. Any suggestions would be helpful!
Any help would be greatly appreciated!
Read Csv File && Create Excel Sheet
I need help on doing this : I want to read a predefined Csv file and then create an excel worksheet based on the csv file values.
The values from csv are seperated with commas... how can I do this ? Please help , I 'm novice
Mining Data From Excel Sheet To Create CSV Files
Hey all,
I've got an Excel document that is generated from a third party programme (See ExcelDoc attached)
I'm trying to manipulate it so that a macro creates a CSV file like the one attached (see THK.csv)
I need to have the following columns in the CSV file populated using the Excel spreadsheet
Column A: Will need to have the NUMBER that is in cell B2 of the Excel file in this case the number is 0714 (need to keep the preceeding 0 too)
Column B: Will need the phone number
Column C: Will need the fax number
Column D: Will need to have the email address
Column E: Will need to have the date only
Column F: Will need to have the Order Number
Column G: Will need to have the Customer name
Column H: Will need to have the Customer telephone number
Column I: Will need to have the Customer alternative telephone number
**Column J: Will need to have the 1st line of the address (after First Address in xls doc)
Column K: Will need to have the 2nd line of the address
Column L: Will need to have the 3rd line of the address
Column M: Will need to have the 4th line of the address
Column N: Will need to be a blank cell
Column O: Will need to be a blank cell
Column P: Will need to be a blank cell
Column Q: Will need to be a blank cell
Column R: Will need to be a blank cell
Column S: Will need to be populated with the information that is after the field MyAddress: in Cell B11 of the xls document.
I've managed to get a macro that will take the information and transpose it into a new document and rename it as an CSV file but I am having a lot of problems trying to get the macro to create the CSV file in the format that I need.
If anyone can help me with this I would be very grateful. I've attached the xls document (the one that is generated and the CSV file which is the format I'm trying to achieve.)
Thanks in advance to all of you for any help that you can give me.
**This cell is generated and will have commas at the end of each line of the address and will be terminated by a period (at line 4 of the address. Sometimes there will be 4 lines to an address but sometimes there may only be 2 or three. In the case where there is no information I need to have a blank (null) value in the cell
Visual Basic 6-create Excel Sheet - Process Remains When Closed
Hi,
I am using VB6 and not VBA code. Within Vb i create an excel sheet using the excel.application etc.I then populate this with data from access using ADO.
This works fine except i have now realised then when i shut down excel the process is still there and increments each time i create a report.
Any ideas how to stop this from happening?
Thanks in advance
Visual Basic 6-create Excel Sheet - Process Remains When Closed
Edit by Moderator:
Original thread
http://www.xtremevbtalk.com/showthread.php?t=201681
is more than 1 month old and therefore archive-only.
Hi,
You said that you were able to create an excel sheet using the excel.application etc.I then populate this with data from access using ADO.
I am trying to do the same. could you please help!!
i have a db in access and a form in Vb.
after having searched a a field(jobtitle) on a Vb form, i want to
display all info abt people having this jobtitle in an
excel spreadsheet. For the users to do mail merge.
in other words transferring data feom access to excel through a vb form,is this possible??
having been looking on the net, no solution.
Can u help me on this Plzzzz!!
Thanks
Quote:
Originally Posted by neoice
Hi,
I am using VB6 and not VBA code. Within Vb i create an excel sheet using the excel.application etc.I then populate this with data from access using ADO.
This works fine except i have now realised then when i shut down excel the process is still there and increments each time i create a report.
Any ideas how to stop this from happening?
Thanks in advance
How To Copy Data From One Sheet And Paste In A Sheet In Multiple Workbooks
I'm pretty new to excel vba and I have a macro that I'm working where the goal is to copy the date out of one sheet and past it in another sheet but in multiple workbooks in a folder. Any help would be appreciated. Thank you.
Sub Copy()
Application.EnableEvents = False
Application.DisplayAlerts = False
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim FSO As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim this As Workbook
Dim cnt As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
Set this = ActiveWorkbook
sFolder = "My Folder"
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
cnt = 1
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWindow
Range("A1:T12005").Select
Selection.Copy
With ActiveWorksheet
Sheets("Sheet 1").Activate
Range("A1").Select
ActiveSheet.Paste
Selection.Interior.ColorIndex = xlNone
ActiveWorkbook.Close SaveChanges:=True
End With
cnt = cnt + 1
End If
Next file
End If ' sFolder <> ""
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
Please post in the approriate sections of the site. This post has been moved to the VBA section from VBCity News
Edited by - vikramjb on 8/10/2006 10:01:31 PM
Calculate Sheet Automatically Triggered By Sheet Activation
I am trying to automatically calculate a sheet when it is activated. Due to the slow speed of calculation (due to its size) of my excel spreadsheet I have turned calculation to manual. I have tried the following without success.
What am I doing wrong
Private Sub SheetActivate(ByVal Sh As Object)
Sh.Calculate
End Sub
VB In Excel - Trying To Display Cells From Sheet To Sheet.
I'm going to go ahead and appologize for asking this, but I have no idea where to even start to search.
What I am trying to do is run and excel macro that will look for an specific enrty in column A. The entry it is searching for can happen multiple times. I want it to then display the information in columns B-F, respective to the the criteria in column A. (If the search is met in column A, that row I want displayed on a new sheet within the workbook)
I'm pretty sure that I can get info to display from one sheet to another. But my problem is how to run the loop so that it will search column A, and dipslay all respective information in columns B-F, if their column A meets the criteria. And stop when it gets to the bottom of the data, obviously.
Any help is so much appreciated. Also if you could just refer me to another section/topic, that will work too. Thanks in advance.
-g2
One Excel Sheet Monitoring Another Sheet's Events
I've written some macros that open an excel workbook and when an account number is typed into a user defined cell or cells the name associated with that account number is plugged into another user defined cell from the workbook the macros opened. I think my co-workers would find these macros very useful, the problem is none of them are very computer literate, and I don't want to have to go around and set these macros up on their excel workbooks. I would like to have these macros stored in a single sheet, but be triggered by the events of the sheets created by my co-workers without them having to insert any code into the sheets they create... just insert the sheet with the macros. Does anybody know if this is possible?? I appreciate any of the help I can get!!! I'm getting frustrated!!!
Copy Date From Sheet And Rename Sheet
Hi All,
I need help with a macro in excel. I thought I had it all working except for one thing. Every evening excel will open and run the macro. I am inserting a new sheet every day and renameing with that days date. My problem is when I recorded the macro the line of code that names the sheet inserted the days date that I recorded the macro. See below:
Sheets("Sheet2").Name = "2003 2 19"
How do I change the above line so that it will reference cells b3:d3 to rename my sheet.
Thanks.
Activating Code On One Sheet From A Button On Another Sheet.
I have a string of code that behaves exactly as I want it to on Sheet 1 of my workbook (Excel 02). Now, I want to excecute this code via a button on Sheet 2 of my workbook. I know this is probably simple, but I haven't found a solution. Here's the code:
Quote:
Private Sub CommandButton1_Click()
Dim varAnswer As String
varAnswer = MsgBox("This cannot be undone." & Chr(10) & Chr(10) & "Edits to this workbook my only be entered into your Data Sheet manually once the current data is compiled.", vbOKCancel)
If varAnswer = vbCancel Then
Exit Sub
End If
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 2
EndRow = 21
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "a").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value <= " " Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
Range("A2:m21").Select
Selection.copy
End Sub
Thanks in advance.
Tring To Find Items On One Sheet On Another Sheet, Then Ect..............
Code:Private Sub cmdCompleted_Click()
Dim strProjects
Dim c
Dim firstAddress
Dim Count1
strProjects = lstProjects.List(lstProjects.ListIndex)
With Worksheets("Job Location and Status").Range("a1:a500")
Set c = .Find(strProjects, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.Pattern = xlPatternGray50
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
The code above will find the selected line (from a list box that is on one sheet) on "Job Location and Status" and the A# cell gray. (I have been piecing this code together from the F1 button.) What I want it to do is find like it does, but instead of doing something with the matching cell, I want it to change a cell in the same row. EX. If it finds that cell A7 matches, I want to place a value in C7.
Does this make sense?
Thanks
Linking Rows From Sheet To Sheet Within A Workbook ?
Hello all
I am trying to make a workbook easier to use. The workbook has 4 different worksheets & over 200 rows, each row is a different project. At the moment I have click buttons to change from sheet to sheet
on sheet 1 row 10 is one project, sheet's 2,3 & 4 row 10 contain information relating to the same project. Is it possible to wright some code that will link the rows form one sheet to the next and it bring the active row to the top of the page.
thanks for your help
Tom
Need VB Code To Insert A Row For A Subscripted Line In Excel & Code To Search For Amounts On One Sheet And Apply Them To Certain Area On Another Sheet
I am in the process of developing an Excel spreadsheet and need assistance
in adding visual basic code to automate it.
The first thing I am trying to do is to create a macro where a user can
click on a certain button, and the program will insert a row at a specific
row and will copy all formulas associated with the row above it. For
instance, if you have a spreadsheet that has 10 rows and 3 columns in it,
you can insert a subscript line below row 5 and call it 5.1. When the
subscripted row "5.1" is added, it will include the same formulas as in row
5, which would add columns 1 and 2 in column 3.
The second thing I am trying to do is have some code that if a user enters
information on Sheet B that needs to also be included on Sheet A, the code
will search Sheet B to see it an entry made on Sheet B should be carried to
a particular row in Sheet A.
I would greatly appreciate any feedback anyone can give me regarding this.
Thanks in advance.
Rename A Sheet And Sheet Properties
Simple on but I cannot figure it out, thanks in advance
I have software the exports products to excel and the file name is date.xls and the sheet name is a random number. what i need to do is rename the sheet, sheet1 and the properties(name) to sheet1. I then need to add another sheet, sheet2 and the name the properties(name) to sheet2.
I just cannot get it to rename the sheet properties(name). I have looked through the web and cannot find how to do it.
thanks
Prevent Add Sheet If The Sheet Is Already Exits
I have a macro that will add sheets (costing,T&A,order confirmations) into activeworkbooks (marketing.xls). If the sheets (costing (2), T&A (2),order confirmations (2)) are already exits, then will prompt user a msg, if not will create the sheets for them.
but when I run the macro, there will created 8 set of the three sheets.
pls help...
For Each ws In ActiveWorkbook.Worksheets
If ws.Name Like "Costing (2)" Then
MsgBox "You must save your current costing before create another costing"
Else
Worksheets("Costing").Visible = True
Worksheets("T&A").Visible = True
Worksheets("Order Confirmations").Visible = True
Sheets(Array("Costing", "T&A", "Order Confirmations")).Select
Sheets("Order Confirmations").Activate
Sheets(Array("Costing", "T&A", "Order Confirmations")).Copy Before:=Workbooks( _
"Marketing.xls").Sheets(2)
End If
Next ws
Worksheets("Costing").Visible = xlVeryHidden
Worksheets("T&A").Visible = xlVeryHidden
Worksheets("Order Confirmations").Visible = xlVeryHidden
Hide Sheet In Sheet List
I use a little procedure to create a pop-up worksheet selector. There are a couple of sheets that I would like left out of the list. Can anyone help edit this snippet.
Code:
Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 1)
ShtNum = 1
For Each sht In ActiveWorkbook.Sheets
If sht.Name = ActiveSheet.Name Then _
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = sht.Name
ShtNum = ShtNum + 1
Next sht
With ListBox1
.ColumnWidths = "100 pt;50 pt"
.List = SheetData
.ListIndex = ListPos
End With
Set OriginalSheet = ActiveSheet
ShtCnt = ActiveWorkbook.Sheets.Count
ReDim SheetData(1 To ShtCnt, 1 To 1)
ShtNum = 1
For Each sht In ActiveWorkbook.Sheets
If sht.Name = ActiveSheet.Name Then _
ListPos = ShtNum - 1
SheetData(ShtNum, 1) = sht.Name
ShtNum = ShtNum + 1
Next sht
With ListBox1
.ColumnWidths = "100 pt;50 pt"
.List = SheetData
.ListIndex = ListPos
End With
Cheers
A Sheet's Number Or A Sheet's Name Using A Macro
Hi,
I have searched for a way to let a macro copy a sheet and move it to (before) a special sheet - let's call it "Special".
The code Sheets("Origin").Copy Before:=Sheets("Special") worked fine for some days but not any longer.
When a macro records this it writes
Sheets("Origin").Copy Before:=Sheets(4)
where 4 stands for sheet number 4 (even if sheet 3 is hidden). Sheet number 5 is then "Special".
It is a complicated to use the sheets' numbers instead of the names I have given them. Any move of a sheet makes the macro not work as intended and I also have the intention to let a macro copy and rename sheets so keeping track of the numbers will be tough.
Do you know a way how solve this? Thanks in advance!
Edited by - benes on 12/10/2006 9:09:09 AM
Fill Cells From Sheet To Other Sheet
i have this userform MODIFICHE.
sheet REPORT is filled from a value into variuos textbox. Work fine....
sheet cartel1 is the data base of record.
I would want to fill during the scroll vevent into REPORT the list of value refered the actual record show into userform.
Example:
I scroll the record actually, show is ARNESE CIRO, i would want to fill the sheet REPORT with all line refred to ARNESE CIRO into REPORT started from line 38.
Note the unique id for each record is into column D (CERTIF)
here the file...
http://www.mytempdir.com/407468
Text From A Merged Cell On One Sheet To A Merged Cell On A Second Sheet
Here is what I am trying to do. When a user clicks a checkbox, the linked cell is read, if it = True then the information is copied from a cell into the first empty cell on a different sheet. The cells that the info is copied from is merged as is the cell that the info is pasted into. This is for Excel97. What is below is the whole of the code. Should there be declarations at the beginning? Or anything that is standard for VBA? I am close, but I get an error. This line:
Worksheets("Complete").Range("B" & x).Value = Sheet2.Range("B23").Value
gives an application-defined or object-defined error. I have tried several variations of the code. The text is being copied onto sheet6, also called Completed. I have tried it both ways.
Code:
Private Sub Check33_Click()
Dim x As Range
Sheet6.Activate
Set x = Cells(Rows.Count, "B").End(xlUp)(2)
'x = ActiveSheet.UsedRange.Rows.Count
If Sheet2.Range("K23").Value = True Then
Sheets("Complete").Select
Worksheets("Complete").Range("B" & x).Value = Sheet2.Range("B23").Value
End If
End Sub
Here are some other ways that I tried to do this
Code:
Private Sub Check33_Click()
Dim FirstEmpty As Range
Sheet6.Activate
Set FirstEmpty = Cells(Rows.Count, "B").End(xlUp)(2)
If Sheet2.Range("K23").Value = True Then
ActiveWorkbook.Sheets("Sheet6").Range(FirstEmpty).Value = Sheet2.Range("B23").Value
End If
End Sub
Code:
Private Sub Check33_Click()
Dim lastRow As Long
ThisWorkbook.Activate
SheetA.Activate
'lastrow is the last row without data
lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
If Sheet2.Range("K23").Value = True Then
SheetA.Range("lastRow").Value = Sheet2.Range("B23").Value
End If
End Sub
This version gives a 'subscript out of range' error
Code:
Private Sub Check33_Click()
Dim x As Range
Sheet6.Activate
Set x = Cells(Rows.Count, "B").End(xlUp)(2)
'x = ActiveSheet.UsedRange.Rows.Count
If Sheet2.Range("K23").Value = True Then
Sheets("Sheet6").Select
Worksheets("Sheet6").Range("B" & x).Value = Sheet2.Range("B23").Value
End If
End Sub
If anyone can help, I would be eternally grateful!
Kingman
Create A New Visio Object, && Create A New Document From VBA
Hello all,
I am trying to open Visio and paste a chart into it from Powerpoint, and from there saving it as a .wmf, or.emf file, all via VBA, not sure of the code though..
I tried creating a new Visio Application via-
Private VS As Visio.Application
-but it wasnt having any of it!
Any advice please?
Thanks
GARYBUG (bzz)
Sqldmo, Create Database, Create Teables... All From Vb6?
Hi all
I need code to create a database, create table(s) on that database, using sqldmo.
Can anyone give example pieces of code to get that, including the connection to server, all from a VB6 application?
Many Thanks in advance
Virginio Reis
How Do You Create New Methods For ActiveX Controls You Create?
I recently finished making my very first complete ActiveX control, and I feel confident enough now to make another that I also need to complete one of my applications. I am going to make an Alarm control, which shouldn't be too hard to create or code, but I want to add in some methods to perform some specific tasks I want it to be able to do. I know how to create new properties, but can someone help me out with how to create new methods? You don't have to teach me anything specific, if someone has just a tutorial or something simple, that will do the job.
Thanks
Now, don't get me wrong, 'cause baseball's a great sport.
It teaches little children simple eye-hand coordination with little effort or exhaustion.
-byufan
Create A 2 Simple Textboxs With A Create Button Pls Help!
Im a newbie in vb and have no help w/my vb app.
Can anyone give me the script for a simple form to display 2 textboxes on top of each other with a button underneath that when clicked writes the contents of the textbox to a file?
The the 2nd txtbox would be used for the name of the file...
Thanks to all that can code!
Next Sheet
Can anyone help me out here?
I want to use a macro on a button, to jump to a next sheet in my excel-workspace (say from a to b)
is there an option, or can I only use HYPERLINKS?? (please no)
WHAT I HAVE is :
Sub
Sheets("Main").Activate
End Sub
But I'd like it to be more effortable, thus
'main' would be 'next' in general, no matter WHERE I am on my Worksheet
Elmer
.Name Sheet
Hello!
How can I make this:
Workbooks("cogs.xls").Worksheets("sheet1").Activate
using the internal .name property of the sheet and not the "name" of sheet
From Sheet 1 To Sheet 2!!!!!!
Hi All
I have recently created a file that contains 2 Sheets.
Sheet 1 holds all my data and Sheet 2 is to hold certain bits of data from Sheet 1.
I was informed that if i used Data|Filter|Advance Filter, that i would be able to copy selected information from one sheet on to another.
However, when i came to do this, a message kept on appearing saying 'Can only filter data in active sheet' (or something very similar).
Is it possible to filter data from one sheet to another?
If not, can someone please advise me on how i can do something similar.
Thanks
How To Get Sheet Name From Excel Using VB6
ok.. i have code that imports an excel spreadsheet.. right now.. i have hardcoded the following line which works unless the spreadsheet page name is something else..
Set ws = wb.Sheets("Sheet1")
how can i set this using a variable so the sheet name doesnt have to be "Sheet1" in order to get this to work...
code
=========================================================
'Imports the new Data from data managment into temp table
Dim Y As String
Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim rmax As Long
Set xl = New Excel.Application
Set wb = xl.Workbooks.Open(Y)
Set ws = wb.Sheets("Sheet1") ----- Problem line here...
thanks for the advice
Output To A Different .csv Sheet??
I have a very standard bit of code (see below) which outputs values which have been stored in the variable KeepRecord(). Everything outputs fine etc... I was wondering is there a way of choosing to write into sheet 2 from this code instead of the default Sheet 1? It's just a psych experiment program which records the data and outputs it so I don't need any heavy integration etc... Just was wondering can I do it from this code or need to use a proper component. I know nothing about this kind of stuff.
Private sub CmdSave()
Dim i As Integer
Open FileName & ".csv" For Append As #1
Write #1, "File created on the " & Format(Date, "dd/mm/yy")
Write #1, ""
For i = 1 to 20
Write #1, KeepRecord(i)
Next i
Close #1
End sub
Thanks,
S.
Get The Name Of An Excel Sheet
hello,
i have a program that automates excel from vb. i'm trying to get the name of the active worksheet then assign the name to a variable in vb. does anyone have any ideas on how to do this. i've been trying things like
Code:
txtGraphTitle.Text = ActiveWorkbook.Sheets(Name)
but had no success
thanks a lot
Excel Last Sheet
How do you determine the name of the last sheet in a workbook. Once this has been done how do you add a sheet after the last one, every time I add one it inserts it before
Active Sheet
I am just starting out Coding in excel. What I'm trying to do is get the sheet name that the user is switching to next. Right now i'm trying the sheetActivate command. The next problem is getting the acual sheet name. I need the name to set it active in code which i have writen.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
end sub
If anyone could help that would be great.
Thanks
-Pilosi
Choosing A Particular Sheet
What is the syntax to pick a specifcally named sheet to paste to instead of the "active sheet". The sheet name would be "2002". What I have so far will copy info from a database and paste it into the active sheet:
Option Explicit
Sub GetData2()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
' Set the string to the path of your database
strDB = "\\Quartz\Common\Comptrol\Corp_Rep\MONTHEND\2002\Monthly Stewardship\Stewardship Master Files\Costbudget.mdb"
' Open connection to the database
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
' Open recordset based on Cost and Budget query
rst.Open "Select * from qryCOSTBudget 2002", cnt
' Set the sheet where data will be copied
Set xlWs = "2002"
Find A Value In A Sheet
how can i find a value at a particular column for example first i want to find a value in the usedRange and if i find then i want to find a value in the same column as the previous value.....i m doing something like this
Set rngid = shtcon.UsedRange.Find("Name")
msgbox rngid.column
"now this does return a value"
but now i want to find another value which is within that column but i don't know which row.....
how can i do that ?
waqas
|
TRACKER
