Solved

Copy Columns

Posted on 2011-09-14
21
388 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 53

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 53

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 53

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 53

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 53

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 53

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 53

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 53

Expert Comment

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

~bp
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
Show hidden user account 7 62
How to make an ADE file by code? 11 94
VB Script: create txt file with DateStamp and IP Adress 6 27
VBScript on Html 15 47
Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

840 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