PDA

View Full Version : Solved: Prep many tabs for new year of data



JoeMoe
03-22-2012, 04:47 PM
I’m trying to develop some code that will essentially prepare many tabs of a spreadsheet for the next year (each spreadsheet shows two years of data, e.g. 2009 and 2010) and I need to be able to set-up for 2010 and 2011 and so on.

Here are the parameters:

Cell K12 – current year value (really K12 through M12 – all merged together)
Cell Q12 – prior year value (really Q12 through S12 – all merged together) – this is where the current year data should be transferred to

I need to move the value in cell K12 to Q12

The code below is what I developed—but includes only one cell transfer (there will be dozens). The only problem is that every time I end up with values of 0 in each cell. Am I missing something obvious? Are the merged cells causing issues?

Also, I’m trying to just perform this on certain tabs – tabs that have the value “LEADSHEET” in cell W1. Any thoughts on how I can revise the code to limit the procedures to these tabs? I really struggle with loops like this.

Thanks in advance!


Sub Rollforward()

Dim ws_count As Integer
Dim I As Integer

ws_count = ActiveWorkbook.Worksheets.Count

For I = 1 To ws_count

'rollforward numbers
s = Range("k12").Value
Range("q12").Value = s
Range("k12").Value = 0

I = I + 1

Next I

End Sub

Paul_Hossler
03-22-2012, 05:43 PM
Merged cells can cause problems


Option Explicit
Sub Rollforward()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
With ws
If .Range("W1").Value = "LEADSHEET" Then
.Range("q12").Value = .Range("k12").Value
.Range("k12").ClearContents
End If
End With
Next
End Sub



Paul

JoeMoe
03-22-2012, 05:58 PM
Thanks -- that works perfectly!