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

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

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

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

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

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

Excel and VB6 Questions
 

Ok thanks, I figured that one out before seeing this post :)