?
Solved

Copy Columns

Posted on 2011-09-14
21
Medium Priority
?
394 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
  • 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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 60

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 60

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 60

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 60

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 60

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 60

Accepted Solution

by:
Bill Prew earned 2000 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 60

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 60

Expert Comment

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

~bp
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

601 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