VBA Macro with numerous variables

[botting6]

Hi,

I am new to macros, I managed to create the below macro which completes a task and seems to do it well.

Now I want the macro to run another 90 times…but there are a number of variables that change. I have listed these in the macro.

Any thoughts on how I could get this to work…without copying the macro and chancing the variables 90 times.

The macro will work down a list in Excel where is will obtain the file names to open.

Thanks for the help.

Sub macrotocmpltetasks()

Application.AskToUpdateLinks = False
Application.DisplayAlerts = False


'first one, adjusted from all others

Dim rf As Workbook
Set rf = ActiveWorkbook
Sheets("Paste SUPPLIER").Select
Columns("A:A").Select
Selection.Copy
Windows("AP SUPPLIER Invoices for CIS.xlsx").Activate
Sheets("OU").Select
Range("A1").Select
ActiveSheet.Paste

Sheets("AP SUPPLIER DETAILS").Select
Range("A2:G" & Range("G" & Rows.Count).End(xlUp).row).AutoFilter Field:=1, Criteria1:=Sheets("OU").Range("A1").Value

Range("C2:F5000").Select
Application.CutCopyMode = False
Selection.Copy

rf.Activate
Dim filelocation1a As String ‘file location changes to 2a
Dim file As String
Dim filepathfull1a As String ‘changes to 2a
Filelocation1a = "B5" ‘ changes to 2a
File1a = "B1" ‘changes to b2 and works down list
filepathfull1a = Sheets("prep cis").Range(filelocation1a) & "\" & Sheets("paste Supplier").Range(file1a).Value & ".xlsm" ‘changes to 2a
Workbooks.Open (filepathfull1a), UpdateLinks:=3 ‘changes to 2a

Sheets("Rec").Select
Range("A21").Select
ActiveSheet.Paste

Dim wp As Workbook ‘changes to wp2
Set wp = ActiveWorkbook ‘changes to wp2


'data detail

rf.Activate

Dim Filelocation1b As String ‘changes to 2b
Dim File1b As String ‘changes to 2b
Dim Filepathfull1b As String
Filelocation1b = "F5" ‘changes to 2b
File1b = "C1" ‘changes to c2 and works down list
Filepathfull1b = Sheets("paste Supplier").Range(Filelocation1b) & "\" & Sheets("paste Supplier").Range(File1b).Value & ".txt" ‘changes to 2b
Workbooks.Open (Filepathfull1b) ‘changes to 2b

Dim dd As Workbook ‘changes to dd2
Set dd = ActiveWorkbook ‘changes to dd2

Columns("A:A").Select
Selection.Copy
wp.Activate ‘changes to wp2
Sheets("Data detail").Select
Range("I1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
dd.Close ‘changes to dd2

'summary file

rf.Activate

Dim File1c As String
Dim Filepathfull1c As String
File1c = "D1" ‘changes to 2c and d2, works way down
Filepathfull1c = Sheets("paste Supplier").Range(Filelocation1b) & "\" & Sheets("paste Supplier").Range(File1c).Value & ".txt" ‘changes to 2c
Workbooks.Open (Filepathfull1c)

Dim sf As Workbook ‘changs to sf2
Set sf = ActiveWorkbook ‘changes to sf2

Columns("A:A").Select
Selection.Copy
wp.Activate
Sheets("Data summary").Select
Range("I1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
sf.Close ‘changes to sf2

wp.Activate ‘changes to wp2

Sheets("Data detail").Select

Application.Run "'Rename Files.xlsm'!cistxtcol"

Dim wB As Workbook
Dim nPath As String ‘changes to npath2
nPath = ThisWorkbook.Sheets("Paste SUPPLIER").Range("F10").Value & ThisWorkbook.Sheets("Paste SUPPLIER").Range("E1").Value
Set wB = Workbooks.Add

With wB
.SaveAs Filename:=nPath
Dim np As Workbook
Set np = ActiveWorkbook
End With

wp.Activate
Sheets("Rec").Select
Range("H4").Select
Selection.Copy

np.Activate
Range("A1").Select
ActiveSheet.Paste
np.Save
np.Close

wp.Activate
wp.Save
wp.Close
'--------------
'--------------




Application.AskToUpdateLinks = True
Application.DisplayAlerts = True

MsgBox "Task complete."

End Sub

[PFDave]

You'll be able to loop through a range to repeat the same actions but use the different variables, however a sample workbook would be required

[MarMo]

And pls put your code in CODE tags as per forum rule #3 :
https://www.excelforum.com/forum-rul...rum-rules.html