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

Help merging two VBA codes 2
Having difficulty copying and pasting on VBA 3
= Today()+1 into Sheet1, Sheet2, and Sheet3 2
lenghty code 4
Copying Solver results of each iteration to a given location in a workbook 1
Macro Related to Paste Values 2
Code not working as expected 2
Copy Paste Macro not Pasting Values when Blank 3

Top