updating code loop throught specific sheets and ignore the others

[KalilMe]

hi
I have this code works from sheet to another . what I want loop for specific sheets are (export,import) and ignore sheets(main,data,archive ) .
so if any body help truly appreciate .

Sub Copy_Columns()
  Dim cols As Variant, sh1 As Worksheet, sh2 As Worksheet, i As Long, c As Long, f As Range
  Set sh1 = Sheets("SHEET1")
  Set sh2 = Sheets("INVENTORY")


  cols = Array("BRAND", "MODEL", "CLIENT", "QTY IMP", "QTY EX") 'Put column titles here
  For i = 0 To UBound(cols)
    Set f = sh1.Rows(1).Find(cols(i), , xlValues, xlWhole)
    If Not f Is Nothing Then
      c = f.Column
      Set f = sh2.Rows(1).Find(cols(i), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh1.Columns(c).Copy sh2.Columns(f.Column)
      End If
    End If
  Next
 
  MsgBox "End"
End Sub

 

[Joe4]

Just like you have an array to loop through the columns you want to apply it to, you can create another array that has the names of the sheets that you want to apply this to. Then loop through that array, activate each sheet, and apply the code you have above (so the array you have about would be nested inside of your sheet array).

 

[KalilMe]

thanks I' m not good at vba but i try learning . based on your guiding . the code doesn't work after I changed a simple things .

Sub Copy_Columns()
  Dim cols, sh As Variant, sh1 As Worksheet, sh2 As Worksheet, i, s As Long, c As Long, f As Range
  Set sh1 = Sheets("SHEET1")
  Set sh2 = Sheets("INVENTORY")

 sh = Array("export", "import")
  cols = Array("BRAND", "MODEL", "CLIENT", "QTY IMP", "QTY EX") 'Put column titles here
 s = 0 To UBound(sh)
  For i = 0 To UBound(cols)
    Set f = sh1.Rows(1).Find(cols(i), , xlValues, xlWhole)
    If Not f Is Nothing Then
      c = f.Column
      Set f = sh2.Rows(1).Find(cols(i), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh1.Columns(c).Copy sh2.Columns(f.Column)
      End If
    End If
  Next
  Next
 
  MsgBox "End"
End Sub

 

[Joe4]

You are missing the word "For" in this line here:

s = 0 To UBound(sh)

You need it to loop through the array, just like you have for the other array loop:

For i = 0 To UBound(cols)

Also, after this line, you will either want reference all your ranges with:

Sheets(sh(s))...

or just select the sheet in the loop right after the first for line (so it is the active sheet), i.e.

  For s = 0 To UBound(sh)
    Sheets(sh(s)).Activate
    For i = 0 To UBound(cols)

This is because looping through the sheet names does not actually activate/select the sheets.
So you either need to do so in the code, or reference the sheet in your range references.

 

[KalilMe]

now it gives me subscript ou of range in this lin

Sheets(sh(s)).Activate

Sub Copy_Columns()
  Dim cols, sh As Variant, sh1 As Worksheet, sh2 As Worksheet, i, s As Long, c As Long, f As Range
  'Set sh1 = Sheets("SHEET1")
  Set sh2 = Sheets("INVENTORY")
  sh = Array("export", "import")
  cols = Array("BRAND", "MODEL", "CLIENT", "QTY IMP", "QTY EX") 'Put column titles here
 For s = 0 To UBound(sh)
     Sheets(sh(s)).Activate
  For i = 0 To UBound(cols)
    Set f = sh.Rows(1).Find(cols(i), , xlValues, xlWhole)
    If Not f Is Nothing Then
      c = f.Column
      Set f = sh2.Rows(1).Find(cols(i), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh.Columns(c).Copy sh2.Columns(f.Column)
      End If
    End If
  Next
  Next
  MsgBox "End"
End Sub

 

[Joe4]

now it gives me subscript ou of range in this lin

Sheets(sh(s)).Activate

That seems to suggest that you do not have a sheet named "export" or "import".
Make sure that the names match EXACTLY.

 

[KalilMe]

yes you're right , I fix it , but It shows another problem . it doesn't bring the values which header is QTY EXP from sheet "export"

 

[Joe4]

You would need to post the latest version of your code along with a sample of what the data on this sheet looks like and what your expected results should look like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[KalilMe]

SHEET"IMPORT"

loop.xlsm
	A	B	C	D	E	F	G	H	I	J
1	DATE	BRAND	MODEL	CLIENT	QTY IMP	PRICE	DISCOUNT	PRICE DD	MARGIN	SALE PRICE
2	7/28/2021	AS-10	AL-100	MM	180	130	10%	117	10%	128.7
3	7/29/2021	AS-11	AL-100	MM	100	130	10%	117	10%	128.7
4	7/30/2021	AS-12	AL-101	LL	95	120	25%	90	15%	103.5
5
IMPORT

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(VLOOKUP(D2,SS!$B$4:$C$1000,2,FALSE),"")
H2:H5	H2	=IFERROR(F2-F2*G2,"")
I2:I5	I2	=IFERROR(VLOOKUP(D2,SS!$B$4:$D$1000,3,FALSE),"")
J2:J5	J2	=IFERROR(H2+H2*I2,"")

Cells with Data Validation
Cell	Allow	Criteria
D2:D5	List	=SS!$B$4:$B$1002

SHEET EXPORT

loop.xlsm
	A	B	C	D	E
1	DATE	BRAND	MODEL	CLIENT	QTY EX
2	7/28/2021	AS-10	AL-100	MM	60
3	7/29/2021	AS-11	AL-100	MM	100
4
EXPORT

Cells with Data Validation
Cell	Allow	Criteria
D2:D3	List	=SS!$B$4:$B$1002
D4	List	=SS!$B$4:$B$1002

EXPECTED RESULT IN SHEET INVENTORY
BEFORE

loop.xlsm
	A	B	C	D	E	F
1	ITEM	BRAND	MODEL	CLIENT	QTY IMP	QTY EX
2
3
4
5
INVENTORY

Cells with Data Validation
Cell	Allow	Criteria
D2:D5	List	=SS!$B$4:$B$1002

AFTER

loop.xlsm
	A	B	C	D	E	F
1	ITEM	BRAND	MODEL	CLIENT	QTY IMP	QTY EX
2	1	AS-10	AL-100	MM	180	60
3	2	AS-11	AL-100	MM	100	100
4	3	AS-12	AL-101	LL	95
5
INVENTORY

Cells with Data Validation
Cell	Allow	Criteria
D2:D5	List	=SS!$B$4:$B$1002

the final code

Sub Copy_Columns()
  Dim cols, sh As Variant, sh1 As Worksheet, sh2 As Worksheet, i, s As Long, c As Long, f As Range
  'Set sh1 = Sheets("SHEET1")
  Set sh2 = Sheets("INVENTORY")
  sh = Array("IMPORT", "EXPORT")
  cols = Array("BRAND", "MODEL", "CLIENT", "QTY IMP", "QTY EX") 'Put column titles here
 For s = 0 To UBound(sh)
     Sheets(sh(s)).Activate
  For i = 0 To UBound(cols)
    Set f = sh.Rows(1).Find(cols(i), , xlValues, xlWhole)
    If Not f Is Nothing Then
      c = f.Column
      Set f = sh2.Rows(1).Find(cols(i), , xlValues, xlWhole)
      If Not f Is Nothing Then
        sh.Columns(c).Copy sh2.Columns(f.Column)
      End If
    End If
  Next
  Next
  MsgBox "End"
End Sub

 

[Joe4]

OK, I will play around with this a little later tonight when I have time to recreate all this on my side.