Working with columns(irregular iterations ) in Excel macros!

[romij29]

Hello VBA Techies,

I have a slight problem that i need help with.I have a source worksheet where i pull data and put them into ninety odd destination worksheets

The destination worksheet has an outlay as ffs.

A B E H K

Items 2000 2001 2002 2003 etc...

Under 2000, Cell (10,2) is the start of the range where the data from source worksheet is pasted and this covers two columns (Cell(10,2) to Cell(10,3))..Get the drift.
2001 is Cell(10,5);2002 is Cell(10,8); 2003 is Cell (10,11),so as you can see after the first entry in 2000, the interval for the next pasting will be 3 cols away..
I have designed a user form where upon choosing a specific destination worksheet from a listbox,filling in a txtbox labelled "year" and clicking an "update button",I expect my macro to basically put the data for 2004 after 2003 with no overlapping.I want to be able to run this yearly with any specific destination worksheet that I choose.

Thing is;

How do I relate the year to be put into the txtbox with the specific column that its data has to be pasted into..

I have related the past and copy code to the user form code by ref to txtYear which is the txt Year.

I have been thinking it through and taught I'd put this piece of code into txtYear procedure

Private Sub txtYear_Change()
Dim iCol As String
iCol = txtYear

iCol = 1

iCol = iCol + 3
End Sub

but frankly i don't have a clue.
To make it a bit clearer i'll put my code in here for the actual execution of copying and pasting the data.


Option Explicit
Dim strPath As String, strTemplate As String, strCinema As String
Dim intRow As Integer, intCell As Integer, i As Integer, intRowUse As Integer
Dim blnfail As Boolean, strTrad, strLoc, strPath2, strCinema2

Sub Squares_Macro(txtYear)
i = 2

Application.ScreenUpdating = False
strPath = Workbooks("Squares_Control.xls").Sheets("Control").Range("E2")
strPath2 = Workbooks("Squares_Control.xls").Sheets("Control").Range("E3")
'The two lines above are related to a control worksheet that tells macro where to go for the sorce and dest worksheets in Excel
Application.StatusBar = "Opening Files"
Workbooks.Open Filename:=strPath & "Region Alpha.xls"

Do Until Workbooks("Squares_Control.xls").Sheets("Control").Range("A" & i) = ""
'assigns a variable to a named range ,this variable
'will refer to the range whenever used in the following code
strTemplate = Workbooks("Squares_Control.xls").Sheets("Control").Range("A" & i)
strCinema = Workbooks("Squares_Control.xls").Sheets("Control").Range("B" & i)


Application.StatusBar = "Opening " & strCinema
Workbooks.Open Filename:=strPath2 & strCinema


Workbooks("Region Alpha.xls").Sheets("P&L").Activate
Columns("A:A").Select
Selection.Find(What:=strTemplate, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 0).Range("N3:O61").Copy

Workbooks(strCinema).Sheets(1).Activate
Cells(10, txtYear + 3).Activate
ActiveCell.PasteSpecial (xlPasteValues)

Range("N10:N68").Select

Selection.NumberFormat = "#,##0"

Range("O13").Select
Selection.NumberFormat = "0.0"
Range("O15:O22").Select
Selection.NumberFormat = "0.00"
Range("O25:O72").Select
Selection.NumberFormat = "0.0%"
Range("N12, N22:O22, N32:O32, N38:O38, N46:O46, N53:O53, N58:O58, N63:O63,N68:O68, N72:O72").Select
Range("N72:O72").Activate

Selection.Font.Bold = True
Range("N10:O72").Select
Selection.Interior.ColorIndex = 2

Application.StatusBar = "Saving " & strCinema
Workbooks(strCinema).Save

Workbooks(strCinema).Close

i = i + 1

Loop

Application.CutCopyMode = False
Application.StatusBar = False

End Sub

Sub Form()
'Displays the form when "Form" command button in clicked
Squares_Update.Show

End Sub

Please,I also need to know if i can say put 1 into the txtbox labelled Year(txtYear) for 2000.Most importantly,what do I refer my column iterations to as I thought col A WAS A GOOD IDEA until I realises that after the first year in worksheet (2000)(Col B), the next col for 2001 was 3 columns away(col E) ?

Regards,

romij29

 

[SkipVought]

romij29,

I have not taken the time to study your post completely but here is a suggestion, ESPECIALLY if you are planning to use this data to report from.

DESIGN YOUR TABLE NORMAILZED.

This means that you will at least have a column heading for YEAR and a column heading for VALUE(s). EVERYTHING goes into these columns, assuming that the total number of rows will not exceed 65536.

The assignment of the next available row for your import is

lNextRow = ActiveSheet.[A1].CurrentRegion.Rows.Count + 1

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[romij29]

Skip,
I don't have the slightest clue what you mean.The data is updated at the end of every year.I havce to basically be able to get it posted into a destination worksheet for each seperate dept. These depts have one workbook each..
From the worksheet in each workbook, i have columns headed 2000,01,....04 basically. Its already being updated till 2004.However,i need a nmacro that will be able to say paste 2005 values into the next three cols after 2004. The data for each year covers 2 columns.I have designed a user form for the update.I need to be able to say assign maybe 1 to 2000 that way when i enter "1" into the txtbox labelled "year" I can update my macro in the specific 2 columns under the 2000 heading.Is it a bit clearer now??