VBA Code to copy range based on value

[Corleone]

I would like to put some code into my spreadsheet do that it copies a range into columns based on the value in row 1 of each column

I hacve a tab entitled "TABLES"
Column C contains a range of data (c3:c204)
Columns D > EL the columns are numbered 2>140 in row 1

Row2 downwards is all blank in columns D>EL (Only column C has data in it)

I would like the code to copy the range in column C (c3:c204) into any column where the value in row 1 is equal or less than the value which is
located in a separate TAB "COVER Sheet" cell 14

thanks

 

[frank_AL]

Corleone,

This code assumes the value on the tab "COVER Sheet" is in cell A14. Give it a try.

Option Explicit


Sub CopyColumn()
Dim chkval As Integer
Dim i As Integer
chkval = Sheets("Cover Sheet").Range("A14")


For i = 2 To chkval
    Sheets("Tables").Range(Cells(3, i + 2), Cells(204, i + 2)).Value = Sheets("Tables").Range("C3:C204").Value
Next i


End Sub

 

[Corleone]

Thanks for this Frank - how would I change it so that it copies the formulas rather than the values

 

[frank_AL]

Okay, I've modified the code to just copy what is in Column C.

Option Explicit


Sub CopyColumn()
Dim chkval As Integer
Dim i As Integer
chkval = Sheets("Cover Sheet").Range("A14")


For i = 2 To chkval
    Sheets("Tables").Range("C3:C204").Copy Destination:=Sheets("Tables").Range(Cells(3, i + 2), Cells(204, i + 2))
Next i


End Sub

 

[Corleone]

Frank - This works perfectly , Many thanks for your help!!

 

[frank_AL]

You’re welcome.