code for copy and paste

[Veeru]

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

[mdmackillop]

Copy the worksheet and delete the columns you don't need.
Sub Test()
Set wbTgt = ThisWorkbook
Set wbSource = Workbooks.Open("C:\YourPath\Var.xlsx")
wbSource.Sheets("Raw").Copy after:=wbTgt.Sheets("Main")
wbSource.Close False
ActiveSheet.Range("B:B,E:H").Delete
End Sub

[Veeru]

I don't have one or two sheets...I have more than 30 sheets so just want to remove manual work here...

[Veeru]

Thanks for the code but one thing still remain unsolved...I need data to be pasted in new tab..so what can we do for it...

[mdmackillop]

Copying a sheet creates a new tab.

[Veeru]

Great thank you!!!