Column Macro

[Manuel998]

Hi,

I am trying to set up a macro to copy and paste the formulae in cell G10:I19. the macro will need to automatically move to the next adjacent column e.g. it should copy the formula in the actuals and the KPI indicators and paste as formula in G10:I19 (e.g in W1) and when i run the macro next week it should take the formulae from G10:I19 and paste in Col K10:M19. Thanks in advance for you help.

Sample:

 

[NARAYANK991]

Hi ,

Upload your workbook if possible.

Narayan

 

[Manuel998]

Narayan,

The formulaes link to other sheets so i cant upload the full file. Thanks for helping

 

[Hui]

Sub Copy_Block()

  Dim sht As Worksheet
  Dim LastColumn As Long

  Set sht = ThisWorkbook.Worksheets("Sheet1")
   
  LastColumn = sht.Cells(10, sht.Columns.Count).End(xlToLeft).Column
   
  Range(sht.Cells(10, LastColumn - 2), sht.Cells(19, LastColumn)).Copy Range(sht.Cells(10, LastColumn + 2), sht.Cells(19, LastColumn + 5))
   
  Columns(LastColumn + 1).ColumnWidth = 0.5
  Columns(LastColumn + 2).ColumnWidth = 8.43
  Columns(LastColumn + 3).ColumnWidth = 3.57
  Columns(LastColumn + 4).ColumnWidth = 4.29

End Sub

 

[Manuel998]

Hi

that works but i also have columns past the last column that have forecast dat in it so i was wondering if it can actually take the previous column and just paste to the next columns?

Thanks

 

[Manuel998]

if it makes it easier my last column with actual will be K and the columns past that will be forecast col

 

[Manuel998]

apologies what i meant to say is e.g if C9 says actuals then run the sub routine to copy and paste the formulae to G9:I19.