Adjusting code to loop

W

Wopper

Hi all,

I need a little help with a code I use that I want to loop.
I get a file every day and the lenght of it is different each time.
So that's why there should be a loop to get all date.

Bellow is the code I'm using and that needs to be looped! (so also
line
2, 3 etc if they are there)

Also the output should not overwrite itself in the MEU sheet.


Please show me what to do.
Thanks


'Autofilter on cash that comes in
Sheets("Settled trades").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=IN", Operator:=xlAnd


'Place the right values in the Booking CASH in (Settled) sheet
for
MEU
Sheets("Settled trades").Select
Range("H2").Select
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("B4:B5,B8:B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("J2:J9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Calculate
Sheets("Settled trades").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L6:M9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L2:M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""OMR"",Settled trades!
RC[-13])"
Apllication.CutCopyMode = False
Selection.Copy
Range("N3:N9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Part fails"
Range("O2").Select
Selection.Copy
Range("O3:O9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2:X9").Select
Selection.Copy


'Place booking in MEU sheet
Windows("MEU.xls").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
 
B

Bernie Deitrick

Wopper,

It would be better if you posted a description of what you want to do.

HTH,
Bernie
MS Excel MVP


Wopper said:
Hi all,

I need a little help with a code I use that I want to loop.
I get a file every day and the lenght of it is different each time.
So that's why there should be a loop to get all date.

Bellow is the code I'm using and that needs to be looped! (so also
line
2, 3 etc if they are there)

Also the output should not overwrite itself in the MEU sheet.


Please show me what to do.
Thanks


'Autofilter on cash that comes in
Sheets("Settled trades").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=IN", Operator:=xlAnd


'Place the right values in the Booking CASH in (Settled) sheet
for
MEU
Sheets("Settled trades").Select
Range("H2").Select
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("B4:B5,B8:B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("I2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("J2:J9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Calculate
Sheets("Settled trades").Select
Range("B2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L6:M9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Settled trades").Select
Range("G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Booking CASH in (Settled)").Select
Range("L2:M5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("N2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(""OMR"",Settled trades!
RC[-13])"
Apllication.CutCopyMode = False
Selection.Copy
Range("N3:N9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("O2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Part fails"
Range("O2").Select
Selection.Copy
Range("O3:O9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A2:X9").Select
Selection.Copy


'Place booking in MEU sheet
Windows("MEU.xls").Activate
Range("A3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run-time error 1004 paste method of worksheet class failed 3
conso macro 0
Simplify Code for Copy/Paste Special 11
lenghty code 4
Copy Paste Macro not Pasting Values when Blank 3
Copy paste - Values - formula - Optimise 2
Run macro from active workbook whose name keeps changing 20
= Today()+1 into Sheet1, Sheet2, and Sheet3 2

Top