I am struggling with VBA macro to accomplish the following:

Step1:
If the word "frequency" in present in row 1 (A1, B1, C1... IV1) of any
worksheets of the existing workbook, then copy that column and a
column before it.

For example, if "frequency" is present in N1 of sheet4 AND AJ1 of
sheet13, then copy column M and N of sheet4 AND column AI and AJ of
sheet 13 etc...

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Rows("1:1") = "frequency" then ' this is not correct :(
..............

Step2:
Open a new workbook and save as test.xls

Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\test.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

Step3:
Paste the copied values of columns M and N of sheet4 (from step1) to
columns A and B of Sheet1 of the active workbook (test.xls) AND
Paste the copied values of columns AI and AJ of sheet13 (from step1)
to columns A and B of Sheet2 of the active workbook (test.xls)
etc.........

Saving the file can be done after step3 too..

Can someone be grateful to give me the macro accomplish these steps?
Thank you

On Oct 24, 1:31 pm, wrote:
I am struggling with VBA macro to accomplish the following:

Step1:
If the word "frequency" in present in row 1 (A1, B1, C1... IV1) of any
worksheets of the existing workbook, then copy that column and a
column before it.

For example, if "frequency" is present in N1 of sheet4 AND AJ1 of
sheet13, then copy column M and N of sheet4 AND column AI and AJ of
sheet 13 etc...

Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
If Rows("1:1") = "frequency" then ' this is not correct :(
.............

Step2:
Open a new workbook and save as test.xls

Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\test.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False, _
CreateBackup:=False

Step3:
Paste the copied values of columns M and N of sheet4 (from step1) to
columns A and B of Sheet1 of the active workbook (test.xls) AND
Paste the copied values of columns AI and AJ of sheet13 (from step1)
to columns A and B of Sheet2 of the active workbook (test.xls)
etc.........

Saving the file can be done after step3 too..

Can someone be grateful to give me the macro accomplish these steps?
Thank you

Can someone help me with the VBA code please?