VBA copy data from 1 excel to another - changing range

[zardia]

Hi all,

I got this macro working where I will copy and paste datafrom 1 excel to another.
What it does is that it will copy data from a predefinedrange from source file then paste it into the predefined range of thedestination file, then repeat. Each time it repeats, the range from the sourcefile will move 2 columns to the right, while the destination file will move 4columns to the right.
Therefore I used the char(67) to set (starting from Col C)and then + 2 for source file and + 4 for destination file for each loop.
But the problem is that it will die when it reaches AA.


Anyone got any ideas? Thanks

Sub ExtractFromInput()


 


 


'daily input file, range 2 cols x 20 rows, then paste intodaily performance range 2 x 20


'each loop, daily input file cols +2, performance + 4


 


 


 


Dim WkBk_Active As Workbook


Dim Rng_Active As Range


Dim WkBk_Input As Workbook


Dim Rng_Input As Range


Dim FName As String


Dim FPath As String


 


  


 


    Set WkBk_Active =Application.ActiveWorkbook


    FPath =WkBk_Active.Worksheets("Menu").Range("B1")


    FName =WkBk_Active.Worksheets("Menu").Range("B2")


    Set WkBk_Input =Application.Workbooks.Open(FPath & "\" & FName)


    


    'set the variableof the range col of the input file and performance file for loop


    ColC = 67


    ColD = 68


    'Set the jumpvalue to 0 to start off


    InputJump = 0


    PerfJump = 0


   


    'loop from inputfile and paste into performance file


    For j = 1 To 31


    


    'for Input day =13-25, Col need to add A in front, for 26+, Col add B in front.


 


    


    Set Rng_Input =WkBk_Input.Worksheets("Alex").Range(Chr(ColC + InputJump) & 3& ":" & Chr(ColD + InputJump) & 22)


    Set Rng_Active =WkBk_Active.Worksheets("Alex").Range(Chr(ColC + PerfJump) & 3& ":" & Chr(ColD + PerfJump) & 22)


    Rng_Input.CopyRng_Active


        


    


    


    


    Next j


    


End Sub

 

[zardia]

ps.... after which I want to repeat this on all sheets that are in a list in a worksheet call SheetName (A:A)

 

[zardia]

I managed to get the first part working, but if anyone can teach me how to run this macro on all designated sheets that would be great, thanks!

In the same workbook, I have a sheet called SheetName, which contains a list of sheetnames (same workbook) that I need to run this macro on.
(the list gets amended occasionally, so I cannot hardcode the sheetnames in the macro)