Variable declaration

[mrexcelrc]

Hello,

I recorded the macro below to import file CFLASH-AFS_PHANTOMH-20111003 in Sheet 1 then go Sheet 2 and copy paste values in cell B7 and then repeat the step to import file CFLASH-AFS_PHANTOMH-20111004" and then go to Sheet 2 and copy paste values in cell B8.


My problem is I have to do these steps 90 times and I need a VBA that will do just that. Notice that the filename changes (date increments) and the target cell in Sheet 2 increments by 1 row down (B7 then B8...and so forth). I think a For I macro will work here but I dont know where to start.


Can you please help. Thank you in advance.


Sheets("Sheet1").Select

ActiveWorkbook.XmlMaps("xml_Map").Import URL:= _

"Q:OperationsTreasury_IT ProjectsSummit V5.5CFLASH-AFS_PHANTOMH-20111003"

Sheets("Sheet2").Select

Range("B2").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

Range("B7").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Range("E18").Select

Sheets("Sheet1").Select

ActiveWorkbook.XmlMaps("xml_Map").Import URL:= _

"Q:OperationsTreasury_IT ProjectsSummit V5.5CFLASH-AFS_PHANTOMH-20111004"

Sheets("Sheet2").Select

Range("B2").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

Range("B8").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Range("E18").Select

End Sub

 

[NARAYANK991]

Hi ,


Suppose we use a counter , which starts from 1 ; this can be used to increment both the filename , and the cell. Insert a VBA procedure called , say Repetition , and from within this VBA procedure , execute a part of your recorded macro as follows :


Sub Repetition()

CONST MAX_STEPS = 90 ' change this to whatever is your requirement

Dim repeat_count as integer

For repeat_count = 1 to Max_MAX_STEPS

file_name = "Q:OperationsTreasury_IT ProjectsSummit V5.5CFLASH-AFS_PHANTOMH-20111" + RIGHT("00"+TRIM(STR(repeat_count)),3)

'................................................

' Insert part of your macro here

'................................................

Sheets("Sheet1").Select

ActiveWorkbook.XmlMaps("xml_Map").Import URL:= file_name ' changed statement

Sheets("Sheet2").Select

Range("B2").Select

Range(Selection, Selection.End(xlToRight)).Select

Selection.Copy

Range("B7").offset(repeat_count-1).Select ' changed statement

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False

Range("E18").Select

'................................................

' End of the macro

'................................................

Next

End Sub


Can you try this and let me know ?


Narayan

 

[mrexcelrc]

Hi Narayan, I will try this. Thank you.