Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB6 Questions
Hi everyone, I need help with a couple things.
I need ot move data from a sheet in a spreadsheet to a new sheet int he same spreadsheet. Is there a way to set an Excel.Worksheet object by name of the sheet and not the index? i.e. instead of doing set xlSheet = xlBook.Worksheets(1), I have the name of the Sheet. Secondly, how do I copy from one sheet to another? Worksheets.FillAcrossSheets? Can anyone point to a good online sample? Thanks in advance for the help, Cen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB6 Questions
You can reference a worksheet by its name:
Set xlSheet = xlBook.Worksheets("Sheet1") You can copy a cell from one sheet to another with code like Worksheets("Sheet1").Range("A1").Copy destination:=Worksheets("Sheet2").Range("A1") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Cen" wrote in message oups.com... Hi everyone, I need help with a couple things. I need ot move data from a sheet in a spreadsheet to a new sheet int he same spreadsheet. Is there a way to set an Excel.Worksheet object by name of the sheet and not the index? i.e. instead of doing set xlSheet = xlBook.Worksheets(1), I have the name of the Sheet. Secondly, how do I copy from one sheet to another? Worksheets.FillAcrossSheets? Can anyone point to a good online sample? Thanks in advance for the help, Cen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB6 Questions
Given a workbook with:
Old Sheet Named Data New Sheet Named DataNew Then the code would be like Dim xlSheet as Worksheet set xlSheet = xlBook.Worksheets("DataNew") Worksheets("Data").Cells.Copy Destination:=xlSheet.Cells -- Regards, Tom Ogilvy "Cen" wrote in message oups.com... Hi everyone, I need help with a couple things. I need ot move data from a sheet in a spreadsheet to a new sheet int he same spreadsheet. Is there a way to set an Excel.Worksheet object by name of the sheet and not the index? i.e. instead of doing set xlSheet = xlBook.Worksheets(1), I have the name of the Sheet. Secondly, how do I copy from one sheet to another? Worksheets.FillAcrossSheets? Can anyone point to a good online sample? Thanks in advance for the help, Cen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB6 Questions
Chip,
Thanks for the response. Unfortunately, I'm getting a run time error on the code for the CopyDestination you gave me. Here is my code: Dim intRandomNumber As Integer Dim i, x, y, z, intCounter As Integer Dim strStart, strEnd, strSheet1, strSheet2 As String intRandomNumber = CLng(txtRandomNumber) intCounter = 1 x = CLng(txtStartingNumber.Text) y = CLng(txtEndingNumber.Text) z = CLng(txtRandomNumber.Text) strSheet1 = txtOriginalSheetName.Text strSheet2 = txtSampleSheetName.Text strStart = txtStartingColumn.Text strEnd = txtEndingColumn.Text Dim xlApp As New Excel.Application Dim xlBook As New Excel.Workbook Dim xlSheet1 As New Excel.Worksheet Dim xlSheet2 As New Excel.Worksheet Set xlBook = xlApp.Workbooks.Open(txtFileName) Set xlSheet1 = xlBook.Worksheets(strSheet1) Set xlSheet2 = xlBook.Worksheets.Add xlSheet2.Name = strSheet2 For i = x To y Step z Worksheets(strSheet1).Range(strStart & CStr(x) & ":" & strEnd & CStr(x)).CopyDestination = Worksheets(strSheet2).Range(strStart & CStr(intCounter) & ":" & strEnd & CStr(intCounter)) intCounter = intCounter + 1 Next xlBook.Close xlApp.Quit I even tried running what I am trying to do in the immediate window, same error. This was my code the Worksheets(strSheet1).Range("A5:W5").CopyDestinati on = Worksheets(strSheet2).Range("A1") and still a no-go. Any suggestions? Thanks Cen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB6 Questions
Tom,
I need to copy out a row at a time, skipping every Nth row. I posted my code just now to Chip - Can you help show me where I went wrong? Thanks Cen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB6 Questions
There needs to be a space between "Copy" and "Destination". Copy
is the method, destination is the named argument. The line of code needs to be on one line of text; it got wrapped in the newsgroup message. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Cen" wrote in message oups.com... Chip, Thanks for the response. Unfortunately, I'm getting a run time error on the code for the CopyDestination you gave me. Here is my code: Dim intRandomNumber As Integer Dim i, x, y, z, intCounter As Integer Dim strStart, strEnd, strSheet1, strSheet2 As String intRandomNumber = CLng(txtRandomNumber) intCounter = 1 x = CLng(txtStartingNumber.Text) y = CLng(txtEndingNumber.Text) z = CLng(txtRandomNumber.Text) strSheet1 = txtOriginalSheetName.Text strSheet2 = txtSampleSheetName.Text strStart = txtStartingColumn.Text strEnd = txtEndingColumn.Text Dim xlApp As New Excel.Application Dim xlBook As New Excel.Workbook Dim xlSheet1 As New Excel.Worksheet Dim xlSheet2 As New Excel.Worksheet Set xlBook = xlApp.Workbooks.Open(txtFileName) Set xlSheet1 = xlBook.Worksheets(strSheet1) Set xlSheet2 = xlBook.Worksheets.Add xlSheet2.Name = strSheet2 For i = x To y Step z Worksheets(strSheet1).Range(strStart & CStr(x) & ":" & strEnd & CStr(x)).CopyDestination = Worksheets(strSheet2).Range(strStart & CStr(intCounter) & ":" & strEnd & CStr(intCounter)) intCounter = intCounter + 1 Next xlBook.Close xlApp.Quit I even tried running what I am trying to do in the immediate window, same error. This was my code the Worksheets(strSheet1).Range("A5:W5").CopyDestinati on = Worksheets(strSheet2).Range("A1") and still a no-go. Any suggestions? Thanks Cen |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and VB6 Questions
Ok thanks, I figured that one out before seeing this post :)
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
Excel '07 Questions | Excel Discussion (Misc queries) | |||
have several excel questions | Excel Worksheet Functions | |||
Excel/VBA Questions | Excel Programming |