Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Copy Columns

Posted on 2011-09-14
21
Medium Priority
?
393 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
Industry Leaders: 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 58

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 58

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 58

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 58

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 58

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 58

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 58

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 58

Expert Comment

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

~bp
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

609 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