drezner7
asked on
Copy Columns
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").Co py ' 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").Co py ' Part Number
oSheet.Range("A8:A900").Co py ' Serial
oSheet.Range("H8:H900").Co py ' 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
oSheet.Range("B8:B900").Co
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").Co
oSheet.Range("A8:A900").Co
oSheet.Range("H8:H900").Co
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
ASKER
I would prefer not to use Macro. I really would like to do it in VbScript
then you could loop through the target range and go on trimming the values.
Where are you trying to copy from / to?
~bp
~bp
ASKER
I am trying to copy from Worksheet 1 to Worksheet 2 in the same Workbook
Yes, what columns are going to what columns? It looks like you want to get:
oSheet.Range("B8:B900").Co py ' Part Number
oSheet.Range("A8:A900").Co py ' Serial
oSheet.Range("H8:H900").Co py ' Name
But where do you want them in Sheet2, should it be columns A, B and C?
~bp
oSheet.Range("B8:B900").Co
oSheet.Range("A8:A900").Co
oSheet.Range("H8:H900").Co
But where do you want them in Sheet2, should it be columns A, B and C?
~bp
ASKER
yes
ASKER
Columns A, B, C... will be good
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")
~bp
ASKER
it does not work. Herei s the beginning portion of the code
Set objDialog = CreateObject("UserAccounts .CommonDia log")
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.Applic ation")
Set oBook = oExcel.Workbooks.Open(objD ialog.File Name, False, False)
oExcel.Visible = True ' Do not display excel window
Set oSheet = oBook.Sheets(1)
Sheet1.Range("B8:B900").Co py Destination:=Sheet2.Range( "A1")
Sheet1.Range("A8:A900").Co py Destination:=Sheet2.Range( "B1")
Sheet1.Range("H8:H900").Co py Destination:=Sheet2.Range( "C1")
Set objDialog = CreateObject("UserAccounts
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.Applic
Set oBook = oExcel.Workbooks.Open(objD
oExcel.Visible = True ' Do not display excel window
Set oSheet = oBook.Sheets(1)
Sheet1.Range("B8:B900").Co
Sheet1.Range("A8:A900").Co
Sheet1.Range("H8:H900").Co
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")
~bp
ASKER
It is throwing a error in this line
oSheet1.Range("B8:B900").C opy Destination:=oSheet2.Range ("A3") ' Part Number
Error "Expected Statement"
Everything looks correct so, I am confused.
oSheet1.Range("B8:B900").C
Error "Expected Statement"
Everything looks correct so, I am confused.
ASKER
I changed the line to look like this:
oSheet1.Range("B8:B900").C opy 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
oSheet1.Range("B8:B900").C
It opens the excel sheet, but it remains open and it does not copy and paste to Worsheet 2
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
~bp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.FullNa me, 11)) = "wscript.exe" Then
strPath = Wscript.ScriptFullName
strCommand = "%comspec% /c cscript """ & strPath & """"
Set objShell = CreateObject("Wscript.Shel l")
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 .CommonDia log")
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.Applic ation")
Set oBook = oExcel.Workbooks.Open(objD ialog.File Name, False, False)
oExcel.Visible = True ' Do not display excel
oSheet1.Activate
oSheet1.Range("B8:B900").S elect
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("A1").Select
oSheet2.Paste
oSheet1.Activate
oSheet1.Range("A8:A900").S elect
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("B1").Select
oSheet2.Paste
oSheet1.Activate
oSheet1.Range("H8:H900").S elect
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("C1").Select
oSheet2.Paste
iRow = 3
With oSheet.Range("B3:B850").fo rmula="=IF (ISNA(VLOO KUP(A3,'\\ pusehf15\c ss\CSE\F13 5\ALIS\Sus tainment_D ata\Parts Saleability\[Saleability Matrix.xlsx]Sheet1'!$A$3:$ B$795,2,0) ),""Part Not Found"",IF(VLOOKUP(A3,'\\p usehf15\cs s\CSE\F135 \ALIS\Sust ainment_Da ta\Parts Saleability\[Saleability Matrix.xlsx]Sheet1'!$A$3:$ B$795,2,0) =0,""Salea ble"",(VLO OKUP(A3,'\ \pusehf15\ css\CSE\F1 35\ALIS\Su stainment_ 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
'Script to compare data and pull the corresponding data elements
If LCase(Right(Wscript.FullNa
strPath = Wscript.ScriptFullName
strCommand = "%comspec% /c cscript """ & strPath & """"
Set objShell = CreateObject("Wscript.Shel
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
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.Applic
Set oBook = oExcel.Workbooks.Open(objD
oExcel.Visible = True ' Do not display excel
oSheet1.Activate
oSheet1.Range("B8:B900").S
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("A1").Select
oSheet2.Paste
oSheet1.Activate
oSheet1.Range("A8:A900").S
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("B1").Select
oSheet2.Paste
oSheet1.Activate
oSheet1.Range("H8:H900").S
oExcel.Selection.Copy
oSheet2.Activate
oSheet2.Range("C1").Select
oSheet2.Paste
iRow = 3
With oSheet.Range("B3:B850").fo
End With
oExcel.DisplayAlerts = False
OExcel.Save "C:\Testing\ Compare\Reports\Report.xls
oExcel.quit
oOut.Close
Did the cells get copied?
~bp
~bp
ASKER
No cells were copied... it just stayed opened
ASKER
I found the issue.... I had removed the Set oSheet1 =
THank you
THank you
ASKER
Excellent code.... This website rocks... Thank you very Billprew I really appreciate all the help
Great, glad that worked, thanks.
~bp
~bp
call the macro in your code after you paste.