vba to copy from another workbook based on criteria

T

TRIP82

New Member
Joined
Jun 11, 2016
Messages
28
Office Version
  1. 2013
I need to copy data from table below in workbook data.xlsb based on criteria TEAM and EPOCA) and paste it in another workbook (H2H.xlsm). I'm trying this vba code, to copy the data if matches criteria from colomn B and D, but only copy the first one (then need to repeat the same to copy from colomn B and E)
I find the code online and try to adpat for my case but without sucess.
Thanks in advance

criteria:
EPOCA = 2021-2022
TEAM = Dortmund

VBA Code: 
Option Explicit

Sub getData()

Dim LastRow As Integer, i As Integer, erow As Integer
Dim TEAM As String
Dim EPOCA As String

TEAM = Range("B2").Value
EPOCA = Range("A2").Value

Application.Goto Workbooks("DATA.xlsb").Sheets("DATABASE").CELLS(1, 1)

LastRow = Workbooks("DATA.xlsB").Sheets("DATABASE").Range("A" & Rows.COUNT).End(xlUp).ROW

For i = 2 To LastRow

If CELLS(i, 2) = EPOCA And CELLS(i, 4) = TEAM Then
Range(CELLS(i, 1), CELLS(i, 37)).Select
Selection.COPY

Application.Goto Workbooks("H2H.xlsm").Sheets("SHEET1").CELLS(1, 1)
Worksheets("Sheet1").Select
erow = ActiveSheet.CELLS(Rows.COUNT, 1).End(xlUp).Offset(1, 0).ROW

ActiveSheet.CELLS(erow, 1).Select
ActiveSheet.Paste
'ActiveWorkbook.Save
'ActiveWorkbook.Close
Application.CutCopyMode = False
End If

Next i
End Sub

CODEPOCADATAcasaforaCFHT CHT F
D12021-202222-08-2021Bayern MunichFC Koln3200
D12021-202222-08-2021HoffenheimUnion Berlin2221
D12021-202227-08-2021DortmundHoffenheim3200
D12021-202228-08-2021Bayern MunichHertha Berlin5020
D12021-202228-08-2021FC AugsburgLeverkusen1412
D12021-202228-08-2021StuttgartFreiburg2323
D12021-202228-08-2021FSV MainzGreuther Furth3020
D12021-202228-08-2021FC KolnBochum2100
D12021-202228-08-2021BielefeldE. Frankfurt1101
D12021-202229-08-2021Union BerlinMonchengladbach2120
D12021-202229-08-2021WolfsburgRB Leipzig1000
D12021-202211-09-2021RB LeipzigBayern Munich1401
D12021-202211-09-2021LeverkusenDortmund3421
D12021-202211-09-2021FreiburgFC Koln1101
D12021-202211-09-2021HoffenheimFSV Mainz0201
D12021-202211-09-2021Union BerlinFC Augsburg0000
D12021-202211-09-2021Greuther FurthWolfsburg0201
D12021-202212-09-2021MonchengladbachBielefeld3111
D12021-202212-09-2021E. FrankfurtStuttgart1100
D12021-202212-09-2021BochumHertha Berlin1302
D12021-202217-09-2021Hertha BerlinGreuther Furth2100
D12021-202218-09-2021Bayern MunichBochum7040
D12021-202218-09-2021FSV MainzFreiburg0000
D12021-202218-09-2021BielefeldHoffenheim0000
D12021-202218-09-2021FC KolnRB Leipzig1100
D12021-202218-09-2021FC AugsburgMonchengladbach1000
D12021-202219-09-2021DortmundUnion Berlin4220
D12021-202219-09-2021StuttgartLeverkusen1312
D12021-202219-09-2021WolfsburgE. Frankfurt1101
D12021-202224-09-2021Greuther FurthBayern Munich1302
D12021-202225-09-2021MonchengladbachDortmund1010
D12021-202225-09-2021LeverkusenFSV Mainz1000
D12021-202225-09-2021HoffenheimWolfsburg3111
 
Sort by date Sort by votes

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You must log in or register to reply here.

Similar threads

C
  • Solved
Copy paste columns based on matching header into another workbook
Replies
5
Views
112
mumps
mumps
youbitto
  • Question Question
Copying from Filtered range
Replies
1
Views
95
Peter_SSs
Peter_SSs
P
  • Question Question
Copy a worksheet from one workbook to another workbook based on conditional statements
Replies
5
Views
349
HighAndWilder
H
F
  • Solved
VBA split sheet based on a repeating value
Replies
4
Views
447
fogarasia
F
Guna13
  • Question Question
How to Efficient way for create Multiple Workbooks
Replies
1
Views
384
Guna13
Guna13

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top