Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




VB Copy One Cell To Another Worksheet By Click


Hi
I am making a part list sheet and i need the code, when u click on a row with partnr., amount, price, that it gets copied into another worksheet on a specific place.
Lets say I have 200 parts with diffrent parts numbers, i select 20 not following parts by clicking on each once, I want these 20 parts to show up in the 2nd worksheet.

Please help me.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Worksheet Cell Copy Woes
Hello,
New to the forum and vba programming.
I am trying to automate the copying of certain cells from one worksheet to another in the same workbook.
I get an error '1004' regarding one piece of code referring to the worksheet range. Error follows:

Run-time error '1004'
Application-defined or object defined error

The code involved follows:

Do While Worksheets("Sheet1").Cells(crow, 1).Value <> ""
If Worksheets("Sheet1").Cells(crow, 5) = "x" Then
MsgBox "All code works except copy portion"

Worksheets("Sheet1").Range(Cells(crow, 1), Cells(crow, 3)).Copy _
Destination:=Worksheets("Sheet2").Range(Cells(crow, 1))


crow = crow + 1
MsgBox crow
Loop

I can eliminate the Worksheets("Sheet1").Range portion of code and all works well.
Is it possible to use the cells range language like this or must I do it another way?
Thank you in advance for your help.
Hope I've submitted correctly.
Thank you,
JCRSBJ

Writing A Cell Address Of A Cell In 1 Worksheet To A Cell In Another In Same Workbook
Hi……..
Its an urgent requirement….
I have an excel sheet. I need to write the macros in VB for the following requirement:
There are three worksheets in a single workbook.. worksheet3 contains some values in every cell. whenever user writes in something in the cell in worksheet1, the column number and row number from worksheet3 must get written automatically in the adjucent cell in worksheet1 only.

For example:
A worksheet 3 contains PLZ A,B in row 5 and column 7. These rows and columns are user defined. If i write PLZ 31, 20 in F26 in worksheet 1 then automatically G26 must contain value 75, G27 must contain a value 31 and G28 must contain a value 20.

I can't say when this value should get populated. Either on the lost focus of F26 or i need to run a macro manually for F26.
 what would be the code in both the cases? Is the former case possible? If i select group of cells like F26 present in worksheet1 and altogether run a macro then would it give the result?

Thanks in advance for your reply.
Regards...
Priyadarshini

Insert Cell Format && Get Cell Value Without Selecting Worksheet
Hello
       I am trying to tidy up a workbook. I have several macro's, one of them insert a new line with different cell formats in to different worksheets and most of the others get a cell values for creating reports.

The only way I know of inserting / getting a cell value is to select the worksheet and run the macro

        Sheets("Works Schedule").Select
            
        ' Run code

This way works but is their a way of running the macro with out selecting the worksheet to stop the workbook flickering between each sheet whiles the macro is running


Thanks
          Tom

Appending Data From A Cell To Another Cell In Another Worksheet
I'm automating excel using vb 6.0. The code appends the result of a select statement on to an excel file. This is a daily routine and data changes everyday. Is there a way of appending data from a cell and permanently storing it to another worksheet.
E.G.
DAY1
In worksheet 1 cell A1 contains the number 75 .. i wish to append it to Worksheet 2 cell A1

DAY2
In worksheet 1 cell A1 contains the number 85 .. i wish to append it to Worksheet 2 cell A2

.... and so on .. Worksheet 1 changes daily. Worksheet 2 should be filled with data everyday (filling up A1, A2, A3)

Thanks In Advanced

Select And Copy Just Cell Content (not The Cell Itself) By VB Macro
Hi,
my problem is that I can select the content of Cell in Excel sheet [by doubleclicking (or F2) onto the Cell and simply select the whole text inside] and copy it as text.
But I cannot do this by macro.

Is there any chance to select content and copy it by VB macro?

Copy Specifc Text Out Of Cell And Not The Whole Cell???
is there anyway to select a cell, then only copy a specfic amount of text out of it.
ex.copy all text From Part 1 to Part 3 within the cell

Copy Formula With Cell Property To Another Cell
I've a report with data extracted Excel Spreadsheet with subtotal of project code for each row.

The project code at column B. Subtotal for the project is at column F.
I want to copy formula with cell property from cell F3 to the subtotal if column B is not blank.

How to create VB for Excel to automate the copy process?

Please help. Thank you.
from sweelian

Worksheet Name = Cell Value
Im looking for a way to rename a worksheet based on the value entered into a cell. I know how to set it to run the code when the sheet is opened, changed, or whatever but i dont know what code to execute to make excel rename the sheet to a cell's value when the cell's value changes any help greatly appreciated

Copy From Worksheet To Worksheet??
I need to copy info from one cell in worksheet1 to another cell in worksheet2. The problem is I have a formulated cell in worksheet1 and when I copy from worksheet1 to worksheet2, it copies the formula. I need to copy the value only. Here's the code I'm using.

Worksheets("BT WORKSHEET").Range("A60").Copy
Worksheets("REQ.").Select
Worksheets("REQ.").Range("B11").Select
Worksheets("REQ.").Paste

Thanks for any help.

To Copy Worksheet At The End Of Other Worksheet
I WANT TO KNOW HOW TO COPY THE EXCEL WORKSHEET AT THE END OF OTHER WORKBOOK SHEET.
I HAVE WRITTEN CODE IN VB6.0 BUT IT OVERWRITE THE WORKSHEET WHILE COPYING.

Private Sub File1_Click()
Dim FileToCopy As String: FileToCopy = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLSMASTER_FILECAP 8 MASTER FOR RS232 V.95.xls"
Dim NewFile As String: NewFile = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLS2005" + File1.FileName
FileCopy FileToCopy, NewFile
Exit Sub
End Sub

Private Sub Form_Load()
Me.Show
Me.Caption = "Copying..."
File1.Path = "T:PRODUCTIONPRODUCTION FILESELECTRONIC PRODUCTION CONTROLS2005"

End Sub

Cell Location In A Worksheet
I'm new to VB coding in excel so forgive me if this is a stupid question! Is there an function or command within VBE that will return the active cell location? For example, if I issue "Selection.End(xlDown).Select" within a macro. How can I determine the location of the active cell in the worksheet?

Help? Positioning Cell(s) On Worksheet...
I've tried all sorts of things... How in the world can I position a specific cell to the top of the worksheet without manually using the scroll bar? More specifically, after I goto a specific cell name or range I would like to automatically position that cell or range at the top left of the worksheet.

How To Bold A Cell In Worksheet
Hi All,
I would like to know how to bold a cell in a worksheet.
Any help/code would be appreciated.
Thanks.
DEK

Center Worksheet On Selected Cell
I have several named one-cell ranges in my workbook. I have one worksheet which has hyperlinks to each of these ranges. Clicking on the hyperlink causes the cursor to jump to the target cell. The problem is that the selected cell could be anywhere in the window (although it seems to be at the extreme edge more often than not).

I want the cell to be centered in the worksheet window without my having to use the scroll bars to move the worksheet around.

I've tried several different searches of the forum (various combinations of "target cell", "position", "hyperlink", "center in worksheet", etc.) without finding anything to help. I would appreciate suggestions on how accomplish this task.

I'm pretty sure clicking a hyperlink is considered an event, but what would its name be? Can positioning the selected cell be triggered by an event?

Return Worksheet Name && Paste In Cell
Sorry if this is easy, but it's been a while since I had to write code and searching in Help was useless.

I recently got a new PC with Office 2003 software. Now I have to do some edits to our file storage tracking program (that my boss and I wrote several years ago) to make it work with the new version. I'm okay with most of the edits, but I'm having trouble with the following:

I need code to return the name (on the tab) of the active worksheet (into a variable?) so that I can paste it into a specific cell on the active worksheet (specifically cell A6).

Thanks in advance,
Peg

Writing To A Cell In A Worksheet On A .xla File
Hi everybody

Is it not allowed to write to a cell on a worksheet in an AddIn workbook (one with .xla extension)?

I tried to write in to one, and whilst it didn't come up with an error at that line, it did not return anything when I tried to Debug.Print the contents of a particular that I thought was supposed to hold a certain value.

Any suggestions?


Best regards


Deepak Agarwal

Comparing The Value In A Cell And Copying Row To New Worksheet
I found this in another thread, but was unable to get it to work. I keep getting a Run-time error 9, Subscript out of range on the second line. Any ideas?


Dim x As Long, lr As Long, lr2 As Long
lr = Application.WorksheetFunction.CountA(Sheets(CRTDATQ1).Range("A:A"))
For x = 1 To lr
lr2 = Application.WorksheetFunction.CountA(Sheets(CertReport).Range("A:A")) + 1
If Sheets(CRTDATQ1).Cells(x, 11).Value = "true" Then
Sheets(CRTDATQ1).Cells(x, 11).EntireRow.Copy
Sheets(CertReport).Select
Cells(lr2, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Next

Declaring A Formula In A Cell Of A Worksheet
I have written a program in VB6 that calculates values and then I store them in rows in a .csv file. When the program is completed, I copy the .csv file over to an Excel file. My problem is that the cells copy over as values and not as formulas. I would like to be able to enter formulas in the cells in the VB program and then when I copy over the .csv file the formulas would also copy over so if I change a value in the Excel spreadsheet the formula would calculate the proper value in that cell. My problem is that I dont know what cell I am writing to when I write in the VB program. I just write rows of data (comma delimited) and they copy over as cells in the Excel spreadsheet. Can anyone help me?

Thank you

Duane

Macro For Renaming Worksheet With Cell References
I'm trying to write code to rename a worksheet with two cell references. I'm able to do one cell reference, but can't concatenate the other. Can anyone help? This is what I have for one:

ActiveSheet.Select
ActiveSheet.Name = Range ("A1").Value

I want to cconcatenate the value in A1 and B1 and parse if it's over 31 characters long. It seems as if it should be simple, but I've come to a sudden stop.

Thanks!

How To Search For Data In The Worksheet By Specific Cell Name
First of all, I'd like to apologize if I post this topic in wrong forum. I'm a newbie for VB and now I got one project which has to be implemented in VB.

In my project, I've to write the program to fill in the data in specific cell name in excel spreadsheet. But until now it doesn't work

For example, I've data in range A1:C10 which I've one cell named 'Address', not the value of that cell, which I'd like to fill data in. Firstly, I specified the range in the spreadsheet by using

     Set rng = myWorksheet.Range("A1:C10")

then I wanna find where the cell named 'Address' is but I've no idea how to do it. Anybody has and idea? I tried with the command 'Find'

     Set rngFound = rng.Find(xxxxxxx, ,yyyyyy , Excel.XlLookAt.xlPart, _
        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, _
        False)

but I don't know which parameter I'd pass to this function. Could you pls help me finding out?

Thanks in advance

Writing A Number As Text In A Worksheet Cell.
Dear Member Friends,

I want to know the code to write a number as text in a cell using VB Macro code. For example:

Currently the number 00430 is written as 430 in the cell. I want the number to be written as 00430 and not 430 as internal storage of 00430 is char string.



 

How 2 Copy Worksheet To Another Using Vb6?
Hello Everybody,
Can you tell me how can i copy data from one worksheet to another worksheet using visual basic 6?
I get Error when i'm trying to use the paste method.

please help!

Thank you all in advance,
ERAN

Howto Find Next Empty Cell In An Excel Worksheet?
Hi!

I'm making this program that writes to an excel file to keep a summary of information. I used the Excel object library as a reference and I can open the excel file.

My problem is how can I find the next empty cell in a column so I know where to append the next information?

Hope someone can help....

Thanks!!

Sound+color Alert If Cell Value Changed In Another Worksheet
Hello everybody ! I'm new to this great forum and vba too. Here is my first problem I hope you can help my with:

I have 2 worksheets. In the first one I enter data regularely and in the second one never because it gets data from the first one (but rarely is changed something here since it keeps track of maximum and minimum values from first worksheet). I want a way to know at the time I enter data in worksheet 1 if something has changed in worksheet 2 (the way I would prefer the alert here would be a beep) then when I go to worksheet 2 I want to easily locate the newly changed values by having their cells fond in red for a certain amount of time (lets say 24 hours - so only if their values changed today). The cells in worksheet 2 I'm talking about are D3 : D23. How do I do that ?

Thanks.

Determine Cell Location Of A Drawing Object On A Worksheet
I'd like to create a code that determines approximate or if possible the exact cell location of a drawing object on a worksheet. Any help or suggestion would be greatly appreciated. Thanks.

Search By Worksheet And Range To Return Single Cell
Can anyone please help ? I don't know where to start.

I have to create a form (preferably web to share over an intranet) to search by worksheet name and then by x-range and y-range and give the single cell value as an answer. eg. Excel speadsheet no. 1 :

Syd Mel Per <- ex
Syd 1 2 3
Mel 4 5 6 <- dest
Per 7 8 9

The form should ask ex city and destination city. As above, if it is going from Mel to Per the it should return 8. The input fields should be dropdowns preferably filled from the range. There are a few spreadsheets that are the same, and the selection criteria for them is in the name of the sheet eg. 20%, 30%, 40% which is another dropdown on the form.

How do I do this? Should I use ASP.NET or XML and should I use the script editor for Excel XP or Visual Studio .NET?

Thanks.

Same Cell Multiple Worksheet Function: Please Help, Need To Complete By Tonight
Hi! I have a file with multiple worksheetsver 75.

I need to create a new worksheet, where column a1 is the first worksheets cell b5 and b1 is the first worksheets cell d73. Then a2=second worksheet cell a1, b2=second worksheet cell d73...and so on........


Possible?
ANY HELP will be GREATLY APPRECIATED!!!

Excel Macro Performing Web Query - URL From Worksheet Cell
All

I wonder if anyone can help me with the following, please:

I'd like to use a macro to perform a web query on a URL, but rather than specifying the URL in the code, the macro picks it up from a cell on a worksheet. For example, if cell A1 on Sheet 1 contains "http://www.google.com", and I want to display the web page on Sheet 2, my attempt at the code is;

Dim myURL

myURL = Worksheets("Sheet1").Range("A1").Text

Sheets("Sheet2").Select

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myURL, _
Destination:=Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True

End With

It seems to stop on the ".Refresh BackgroundQuery:=False" line - any ideas on this at all, please? I assume this can be done?

Also, if it's possible to specify the target URL in a message box, that would be ideal.

Thanks for your time, all suggestions much appreciated!

Neil

Excel, Error 1004 When Trying To Select Cell In Other Worksheet.
Hi,

I have a new problem in Excel VBA. When I try to select a cell on another worksheet it no longer recognises the object. The example below is code I have taken from running a macro but I have tried indexing the worksheets(2), naming the workbook etc with no luck. I have been told our IT Service provider doesn't support the VBA side of Excel and I will be unable to reload the software. Where the code already exists it continues to work. Not sure but the problem may have started on my first attempted at adding a CommandButton through code? (although it seems to affect all users).

Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
    Range("B4").Select
End Sub

Thank you in advance for any help you can give.

Rob.

COPY Excel Worksheet With VBA
Hello again,

Now I am trying to copy a VBA created Excel Worksheet and append multiple copies at the end of the Workbook.

I have come up with this:

Code:
' copy Worksgeet (Index # 4) and paste 15 copies
Set xlWS = xlWB.Worksheets(4).Copy(, 15)

but I get this error:

Quote:




Run-time error '1004':
Unable to get the Copy property of the Worksheet class




Any ideas to correcting this?

Copy To Current Worksheet
How do I copy the information i want to the current worksheet instead of having a new worksheet created.

Here's what i have so far.

Sub transferRecordset()

'Create a Recordset from all the records in the Orders table
Dim cost As String
Dim conn As New ADODB.Connection
Dim rs As ADODB.Recordset

cost = "Y:IQ38316DataAccessCost Stewardship.mdb"
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cost & ";"
conn.CursorLocation = adUseClient
Set rs = conn.Execute("CapexCurrentYear", , adCmdTable)

'Create a new workbook in Excel
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)

'Transfer the data to Excel
xlSheet.Range("A1").CopyFromRecordset rs

'Save the Workbook and Quit Excel
xlBook.SaveAs "c:My DocumentsADOExample.xls"
xlApp.Quit




End Sub

Excel Copy From One Worksheet To Another ?
I'm trying to write a write a macro that will transfer each row of data in one worksheet to a second worksheet (for example data from each column 1 in a row on first worksheet will transfer to cell B5 on second worksheet, column 2 will transfer to E10, and so on), print the second worksheet, and then repeat for each row of data in worksheet 1. My question is, how can I write code that will make each row number on worksheet 1 a variable and each column number a constant, and each destination cell on worksheet 2 a constant. Other than that, I think I can just write code that will loop through each row.

I apologize for the length of my question, I'm kind of a rookie. Thanks for any suggestions.

Copy A Worksheet To Another Workbook
Hi,

I've got a short question. How can i copy one single worksheet to another workbook.

Copy Excel Worksheet
How about if you wanna copy everything except the code in the worksheet? ie graphs, buttons, values (not formulas) but not the vb that lies with in the sheet?

Copy Recordset Into Worksheet.
I am using VBA in excel and I have written the following code:


Code:
Private Sub simpleConnection()

Dim IP_Address As String
Dim IP_UserId As String
Dim IP_Password As String
Dim cn_400 As ADODB.connection
Dim rs_400 As ADODB.Recordset
Dim i As Integer
Dim fldCount As Integer
Dim iCol As Integer
Dim sqlString As String

'Logon details for AS400
IP_Address = "127.0.0.1"
IP_UserId = ""
IP_Password = ""

'Initialise the ADODB objects
Set cn_400 = New ADODB.connection
Set rs_400 = New ADODB.Recordset

'Set AS400 connection
cn_400.Mode = adModeReadWrite
cn_400.CursorLocation = adUseClient
cn_400.Open "Provider=IBMDA400;" & _
"Data source=" & IP_Address & ";" & _
"User Id=" & IP_UserId & ";" & _
"Password=" & IP_Password

'Create the SQL string
sqlString = "SELECT * from LS3DTA100.PROFORDP WHERE (((PSRCE = 'T') OR (PSRCE = 'F') " & _
"OR (PSRCE = 'P')) AND (PENTDT BETWEEN " & startDate & " AND " & endDate & "))"

'Open AS400 file
With rs_400
.Open sqlString, cn_400, adOpenStatic, adLockOptimistic, adCmdText
End With

'MsgBox (rs_400.RecordCount & " records imported")

'Create the new Excel workbook & worksheet
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add
Set xlWs = xlWb.Worksheets("Sheet1")

' Display Excel and give user control of Excel's lifetime
'xlApp.Visible = False
xlApp.Visible = True
xlApp.UserControl = True

' Copy field names to the first row of the worksheet
fldCount = rs_400.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rs_400.Fields(iCol - 1).Name
Next

' Copy the recordset to the worksheet, starting in cell A2
xlWs.Cells(2, 1).CopyFromRecordset rs_400

' Auto-fit the column widths and row heights
xlApp.Selection.CurrentRegion.Columns.AutoFit
xlApp.Selection.CurrentRegion.Rows.AutoFit

' Close ADO objects
rs_400.Close
Set rs_400 = Nothing

End Sub

What I want to do is open this recordset (rs_400) in the current workbook on a specific worksheet (sheet 53 to be exact),rather than opening a new instance of excel and inserting the recordset into a new book and worksheet there (as I am doing above).

Seems to me that this should be fairly simple thing to do but I have no idea of the syntax.

Can anyone help?

Copy/rename Worksheet
How do I copy a sheet "Input Form" and rename it to "Input Form n", where 'n' is a sequential number? All the copied sheets must be in the same workbook (hence the sequential numbering).

My code is:
---------------
'Code to determine new name
Dim sh As Worksheet
n = 0
For Each sh In ActiveWorkbook.Sheets
shname = ActiveSheet.Name
If Left(shname, 10) = "Input Form" Then n = n + 1
Next sh

Sheets("Input Form").Copy Before:=Sheets("Input Form")
ActiveSheet.Name = newName 'the "newName" variable is defined elsewhere
-----------------

I am having two problems:

1. My value for 'n' is always counting the total number of worksheets in the workbook (which is currently 6), instead of the number of worksheets whose name starts with "Input Form" (which is 1).

2. The code finishes after the .Copy line. Any code I place after this line is never run.

Any thoughts? Thanks!

Name New Worksheet By Copy And Paste
I am trying to write code that creates a number of new worksheets and I want the name for each new work sheet to be copy and paste from list of names in one other worksheet. Is this possible?

Creating A New Copy Of WorkSheet
Hi,
I want to create a new copy of Excel Sheet which i am able to do through the command ThisWorkbook.ActiveSheet.Copy but the problem is that when the new sheet is created,it iniitiates the Activate Event and in the Activate Event there is a function which it is not able to find and it gives a Compile error as Sub or Function not defined.

Can anyone help me on this matter.

Please let me know if any additional information is required.

Thanks

Simi

Copy Worksheet From One File To The Other
How do I copy worksheet from one file to another file?

Please Help....I Cannot Copy 1 Worksheet To Newworkbook?
I am trying to copy just one worksheet ("Access_Database") from one workbook into a new workbook with just the one sheet, not all of the sheets in the original. The code I have attached below works fine, except it copies all worksheets and not JUST the one I want to keep size of files down. Anyone have any suggestions as to what wrong with my code? I have tried various combinations of Worksheet, Sheets, Activesheet but, then, I get no copy being generated (probably a syntax issue)?


Code:Private Sub Auto_Close()
Dim wbDOCname As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False
On Error Resume Next
MsgBox "Please Wait While project is saving and exiting"
docmd.hourglass = True
Worksheets("Access_database").Visible = True
ActiveSheet.Select = ("Access_Database")
wbDOCname = Sheets("Access_Database").Range("A2").Value
Worksheets("Access_Database").Copy.NewWindow
ActiveSheet.SaveAs Filename:="\ranchdataEEIAutoRollup" & wbDOCname & ".xls"

ActiveWorkbook.Close
ActiveWorkbook.Close

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

Edited by - bballava on 7/7/2005 9:18:08 AM

Copy Excel Worksheet
hi,

How do I make a copy of an excel worksheet in VB?

Thanks,

Larry Bargers

Copy Worksheet Excel
Hello,

I have created a time clock program to track employee hours. The program saves the employees time in and time out to a .dat file. I then have VB call excel to create a report and add the employees hours for a time period. Currently I have one pre-formatted worksheet in Excel, and the program writes just the selected employees hours. I want to make it so VB copies the main formatted worksheet for all selected employees, so I'll be able to list each employee to a seperate worksheet. Any help will help.

Thank you,

Larry B.



&lt;b&gt;&lt;i&gt;Larry Bargers&lt;/b&gt;&lt;/i&gt;

Help W/simple Transfer From Vba Form Field To Excel Worksheet Cell
Hey, I've got a real simple problem I can't get my head around. I'm just trying to copy or take data entered in text boxes (in a VBA form) and put it into a cell in a worksheet. Specifically, when the user clicks the "compute" command button I want the data from the text boxes copied or sent to excel cells.

Here's the code so far:

Code:
Private Sub cmdCompute_Click()
            
    Dim strFilePath As String
    strFilePath = "C: emp.xls" 'workbook to receive data from text boxes

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Open(strFilePath)
    Set xlSheet = xlBook.Sheets("Sheet1") 'worksheet to receive data
    
    xlSheet.Activate
    Range("A1") = frmMain.txtDate.Text 'data being transfered is from main form and text box "txtDate"
    Range("B1") = frmMain.txtMajor.Text 'same as previous line, but different text box
    xlApp.Visible = True
    
End Sub


Thanks for your help.

Newbie ! Copy And Rename Worksheet
I'm a real beginer with VBA so am just trying to learn from other's codes and hope for the best

Can someone help me fix the below. It's to copy a sheet called BLANK and then rename the new sheet the contents of cell L1 of sheet INDEX. I can get the below to copy, but not rename :


Code:
Sub CreateGateway1()
'
' CreateGateway1 Macro
' Macro recorded 01/09/2005 by Nyanko
' Application.DisplayAlerts = False
Sheets("BLANK").Select
Sheets("BLANK").Copy After:=Sheets(2)
Sheet.Name = Worksheets("Index").Range("L1").Value
Sheets("INDEX").Select
checkerr = MsgBox("Record Created")
End Sub

Copy Rows From One Worksheet To Another Using Inputbox
Hi,

I seem to have struck a brick wall with a code. Any help will be greatly appreciated.

Sheets("Orders") has a table A6:F20
Sheets("Products") has a list of products

I want to search Sheets("Products") Column A for any value entered through an inputbox and if a match is found then copy the whole row to the next available empty row in the table on Sheets("Orders").

The search item can contain alphabets, numbers, and or symbols and if the item is not found in the Sheet("Products") column A then a message should appear saying entry found. Also, if the table is full, then there should be a message saying cannot copy.

Here is what I have so far (even which doesn't work):

Sub Macro1()
Dim Ib As String
Dim rFound As Range
Ib = Application.InputBox("Enter Catalogue number")
rFound = Sheets("Products").Cells.Columns("A").Find(what:=Ib, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
Range(rFound).EntireRow.Copy Destination:=Sheets("Products").Range("A65536").End(xlUp).Offset(1, 0)
End Sub


Thanks in Advance

Bill

Copy Create Paste A Worksheet
Hi all,

How do I creat a new worksheet with a certain name and then copy paste one worksheet into it?


I am printing out Project and Project Phase milestones into excel.

There is a Worksheet called "Project Milestones"

There are an unkown number of phases in the project and I have to print a worksheet for each phase on a worksheet called strPhaseName & "Milestones" .

I need to add a worksheet and have it named strPhaseName & "Milestones"
Then I need to copy paste the "Project Milestones" worksheet into the new worksheet to get the graphics and formating.


Code below shows how I populate it:






=========================================================
Set xlsheet = xlbook.Worksheets("Project Milestones")
xlsheet.Select

If Me.chkIncludeMilestones.Value = 0 Then
xlApp.DisplayAlerts = False
xlsheet.Delete
Else
xlsheet.cells(1, 3) = Me.cboProject.Text & " Milestones"
SQL = "SELECT tblProjectMilestones.Auto_Index, tblProjectMilestones.strMilestoneName, tblProjectMilestones.dtmEstimatedDate, tblProjectMilestones.dtmActualDate, tblProjectMilestones.strComments " & _
"FROM tblProjectMilestones " & _
"WHERE tblProjectMilestones.intPR_Index = " & PR_Index & " " & _
"ORDER BY tblProjectMilestones.dtmEstimatedDate, tblProjectMilestones.dtmActualDate, tblProjectMilestones.strMilestoneName;"
Set rs = tdmsDB.Execute(SQL)
myRowNum = 3
While Not rs.EOF
For X = 1 To 4
If Not IsNull(rs(1)) Then xlsheet.cells(myRowNum, 1) = rs(1)
If Not IsNull(rs(2)) Then xlsheet.cells(myRowNum, 2) = Format(rs(2), StandardYearFormat)
If Not IsNull(rs(3)) Then xlsheet.cells(myRowNum, 3) = Format(rs(3), StandardYearFormat)
If Not IsNull(rs(4)) Then xlsheet.cells(myRowNum, 4) = rs(4)
Next X
myRowNum = myRowNum + 1
rs.MoveNext
Wend
Set rs = Nothing

Copy A Worksheet And Lose The Macros
Hello.

I have a very simple spreadsheet which the user completes with various numerical information. They then 'submit' the sheet by clicking a macro button.

The macro copies the sheet into a new workbook and saves it as a predefined filename into a folder on the user's c: drive.

The problem is that I need the saved file (which, incidentally is to be archived) to be devoid of any macros.

Is it possible to delete all macros automatically before the file is saved?

Macro To Copy Data To Another Worksheet
Hi I was wondering if someone could tell me if there is a way to highlight a row and copy certain bits of the information in the row to another worksheet i.e. data in A1,4,5,6 and 10 or on another selection H1,4,5,6 and 10 when you click a button.

Neil.

Duplicating Worksheet Without Copy/Paste
I would also like to be able to ignore original Worksheet buttons/controls during duplication. This is needed for Worksheet archiving within the Workbook. Is it possible?

Copyright 2005-08 www.BigResource.com, All rights reserved