Solved

Copy Columns

Posted on 2011-09-14
21
389 Views
Last Modified: 2012-06-27
What I am trying to do is copy multiple rows in a worksheet and then create another worksheet in the same workbook and paste the columns. It is only working for one column and I already have to have 'Sheet2' created. I also need to apply 'Trim' to the column. Here is my code

oSheet.Range("B8:B900").Copy ' Part Number
set oSheet = oBook.Worksheets("Sheet2")
oSheet.Activate 'The sheet needs to be active
oSheet.Range("A3").Select
oSheet.Paste

What I tried to do was this:
oSheet.Range("B8:B900").Copy ' Part Number
oSheet.Range("A8:A900").Copy ' Serial
oSheet.Range("H8:H900").Copy ' Name
set oSheet = oBook.Worksheets("Sheet2")
oSheet.Activate 'The sheet needs to be active
oSheet.Range("A3").Select
oSheet.Range("C3").Select
oSheet.Range("D3").Select
oSheet.Paste

Can you help?

Thank you


0
Comment
Question by:drezner7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 2
21 Comments
 
LVL 12

Expert Comment

by:viralypatel
ID: 36536146
write a macro to loop thorough the range you pasted and make it trim the values in each cell in the range.
call the macro in your code after you paste.
0
 

Author Comment

by:drezner7
ID: 36536164
I would prefer not to use Macro. I really would like to do it in VbScript
0
 
LVL 12

Expert Comment

by:viralypatel
ID: 36536405
then you could loop through the target range and go on trimming the values.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:Bill Prew
ID: 36536590
Where are you trying to copy from / to?

~bp
0
 

Author Comment

by:drezner7
ID: 36536830
I am trying to copy from Worksheet 1 to Worksheet 2 in the same Workbook
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36536878
Yes, what columns are going to what columns?  It looks like you want to get:

oSheet.Range("B8:B900").Copy ' Part Number
oSheet.Range("A8:A900").Copy ' Serial
oSheet.Range("H8:H900").Copy ' Name

But where do you want them in Sheet2, should it be columns A, B and C?

~bp
0
 

Author Comment

by:drezner7
ID: 36537649
yes
0
 

Author Comment

by:drezner7
ID: 36538176
Columns A, B, C... will be good
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36538320
For the copy, I think it should be as simple as:

    Sheet1.Range("B8:B900").Copy Destination:=Sheet2.Range("A1")
    Sheet1.Range("A8:A900").Copy Destination:=Sheet2.Range("B1")
    Sheet1.Range("H8:H900").Copy Destination:=Sheet2.Range("C1")

Open in new window

~bp
0
 

Author Comment

by:drezner7
ID: 36538527
it does not work. Herei s the beginning portion of the code


Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel File|*.xls|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\Scripts"
intResult = objDialog.ShowOpen
 
If intResult = 0 Then
    Wscript.Quit
Else
    Wscript.Echo objDialog.FileName
End If


Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(objDialog.FileName, False, False)
oExcel.Visible = True ' Do not display excel window
Set oSheet = oBook.Sheets(1)


Sheet1.Range("B8:B900").Copy Destination:=Sheet2.Range("A1")
    Sheet1.Range("A8:A900").Copy Destination:=Sheet2.Range("B1")
    Sheet1.Range("H8:H900").Copy Destination:=Sheet2.Range("C1")

0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36539073
Sorry, the code I gave you was VBA code, it needs some adjustment for VBS usage. Give this a try.

Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel File|*.xls|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\Scripts"
intResult = objDialog.ShowOpen
 
If intResult = 0 Then
    Wscript.Quit
Else
    Wscript.Echo objDialog.FileName
End If


Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(objDialog.FileName, False, False)
oExcel.Visible = True ' Do not display excel window
Set oSheet1 = oBook.Sheets(1)
Set oSheet2 = oBook.Sheets(2)

oSheet1.Range("B8:B900").Copy Destination:=oSheet2.Range("A1")
oSheet1.Range("A8:A900").Copy Destination:=oSheet2.Range("B1")
oSheet1.Range("H8:H900").Copy Destination:=oSheet2.Range("C1") 

Open in new window

~bp
0
 

Author Comment

by:drezner7
ID: 36540051
It is throwing a error in this line
oSheet1.Range("B8:B900").Copy Destination:=oSheet2.Range("A3") ' Part Number

Error "Expected Statement"

Everything looks correct so, I am confused.
0
 

Author Comment

by:drezner7
ID: 36540076
I changed the line to look like this:
oSheet1.Range("B8:B900").Copy Destination = oSheet2.Range("A3") ' Part Number

It opens the excel sheet, but it remains open and it does not copy and paste to Worsheet 2
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36540098
Let me try a VBS test here, maybe that VBA syntax isn't supported, and we might have to do it the long way.

~bp
0
 
LVL 54

Accepted Solution

by:
Bill Prew earned 500 total points
ID: 36540251
Okay, give this a try:

oSheet1.Activate
oSheet1.Range("B8:B900").Select
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("A1").Select
oSheet2.Paste

oSheet1.Activate
oSheet1.Range("A8:A900").Select
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("B1").Select
oSheet2.Paste

oSheet1.Activate
oSheet1.Range("H8:H900").Select
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("C1").Select
oSheet2.Paste

Open in new window

~bp
0
 

Author Comment

by:drezner7
ID: 36540267
The Excel spreadsheet just stays open....here is my entire code:

'Script to compare data and pull the corresponding data elements

If LCase(Right(Wscript.FullName, 11)) = "wscript.exe" Then
    strPath = Wscript.ScriptFullName
    strCommand = "%comspec% /c cscript  """ & strPath & """"
    Set objShell = CreateObject("Wscript.Shell")
    objShell.Run(strCommand), 1, True
    Wscript.Quit
End If


'Input Boxes to request user input


reportname = inputBox("Please Enter Name Of Report?",,"Reports")


Set objDialog = CreateObject("UserAccounts.CommonDialog")

objDialog.Filter = "Excel File|*.xls|All Files|*.*"
objDialog.FilterIndex = 1
objDialog.InitialDir = "C:\Scripts"
intResult = objDialog.ShowOpen
 
If intResult = 0 Then
    Wscript.Quit
Else
    Wscript.Echo objDialog.FileName
End If


Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Open(objDialog.FileName, False, False)
oExcel.Visible = True ' Do not display excel

oSheet1.Activate
oSheet1.Range("B8:B900").Select
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("A1").Select
oSheet2.Paste

oSheet1.Activate
oSheet1.Range("A8:A900").Select
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("B1").Select
oSheet2.Paste

oSheet1.Activate
oSheet1.Range("H8:H900").Select
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("C1").Select
oSheet2.Paste


iRow = 3

With oSheet.Range("B3:B850").formula="=IF(ISNA(VLOOKUP(A3,'\\pusehf15\css\CSE\F135\ALIS\Sustainment_Data\Parts Saleability\[Saleability Matrix.xlsx]Sheet1'!$A$3:$B$795,2,0)),""Part Not Found"",IF(VLOOKUP(A3,'\\pusehf15\css\CSE\F135\ALIS\Sustainment_Data\Parts Saleability\[Saleability Matrix.xlsx]Sheet1'!$A$3:$B$795,2,0)=0,""Saleable"",(VLOOKUP(A3,'\\pusehf15\css\CSE\F135\ALIS\Sustainment_Data\Parts Saleability\[Saleability Matrix.xlsx]Sheet1'!$A$3:$B$795,2,0))))"
   
End With



oExcel.DisplayAlerts = False

OExcel.Save "C:\Testing\ Compare\Reports\Report.xls"
oExcel.quit
oOut.Close       
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36540277
Did the cells get copied?

~bp
0
 

Author Comment

by:drezner7
ID: 36540291
No cells were copied... it just stayed opened
0
 

Author Comment

by:drezner7
ID: 36540306
I found the issue.... I had removed the Set oSheet1 =

THank you
0
 

Author Closing Comment

by:drezner7
ID: 36540322
Excellent code.... This website rocks... Thank you very Billprew I really appreciate all the help
0
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36540340
Great, glad that worked, thanks.

~bp
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question