Veeru
07-31-2017, 11:22 AM
Hi ,
I am looking for code which will copy some specific items from specific sheet and paste in main file by creating one separate tab.
Sample file attached…where we want to copy from “Var” workbook and from Raw sheet …Col.A,C, D and I and paste in Net workbook by creating one separate tab
As we can see col. A is merged so when end result should look like as per sheet attached in Net tab named”Sample”.
I have developed below code where is picking and pasting but not creating separate tab
Sub Block3()
Call Block2
' it will copy and paste from Block trade file col.C to F
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
'-------------------------------------------------------------
'Open file with data to be copied
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets("Raw")
End If
'--------------------------------------------------------------
'Copy Range
wsCopyFrom.Range("I2:J100").Copy
wbCopyTo.Activate
With wsCopyTo
.Range("F" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
'Close file that was opened
wbCopyFrom.Close SaveChanges:=False
End Sub
Please suggest any modification.
thanks
I am looking for code which will copy some specific items from specific sheet and paste in main file by creating one separate tab.
Sample file attached…where we want to copy from “Var” workbook and from Raw sheet …Col.A,C, D and I and paste in Net workbook by creating one separate tab
As we can see col. A is merged so when end result should look like as per sheet attached in Net tab named”Sample”.
I have developed below code where is picking and pasting but not creating separate tab
Sub Block3()
Call Block2
' it will copy and paste from Block trade file col.C to F
Dim vFile As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet
Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet
'-------------------------------------------------------------
'Open file with data to be copied
vFile = Application.GetOpenFilename("Excel Files (*.xl*)," & _
"*.xl*", 1, "Select Excel File", "Open", False)
'If Cancel then Exit
If TypeName(vFile) = "Boolean" Then
Exit Sub
Else
Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets("Raw")
End If
'--------------------------------------------------------------
'Copy Range
wsCopyFrom.Range("I2:J100").Copy
wbCopyTo.Activate
With wsCopyTo
.Range("F" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
'Close file that was opened
wbCopyFrom.Close SaveChanges:=False
End Sub
Please suggest any modification.
thanks