Excel VBA dynamic sheet select

[swtrader]

I have 53 spreadsheets -- 1 for each week of the year.

DataEntry spdsht has columns: Date, Year, WeekNum, Contractor, Amount

I want to copy each row of data to the correct weekly spreadsheet. How can I select the correct spreadsheet (using the value in WeekNum column) and loop through the rows in DataEntry?

(Having 53 spreadsheets may not be the most efficient design but it is the most simple.)



Sub CopyData()
Sheets("DataEntry").Select
' Find the last row of data
FinalRow = Range("A5000").End(xlUp).Row
' Loop through each row
For x = 2 To FinalRow
' Decide if to copy based on column
ThisValue = Range("A" & x).Value
If ThisValue <> 0 Then
Range("A" & x & ":B" & x).Copy
Sheets("-----WeekNum------???").Select
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("DataEntry").Select
ElseIf ThisValue = 0 Then

End If
Next x
End Sub

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

You have one workbook with 53 sheets or 53 workbooks ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[swtrader]

One (1) workbook; 53 sheets

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

Hi,

What a mess! 53 sheets containing similar data. It is unlikely that this is a good design. It will be a chronic headache and pain in the rear for you and anyone who has the misfortune to analyze, report and maintain the data.

It is almost always a tragedy for data to be chopped up into different places. It renders the data almost as useless as a paper report in a file drawer.

Such data ought to be organized into one table, from which the plethora of Excel data analysis and reporting tools can easily be employed. You could, if so desired, create 53 separate reports, one for each week of the year. Not that I would recommend that either, because each separate report represents an unnecessary maintenance overhead. But it would be light-years better than 53 separate sheets of data.

Skip,

_[red][/red]

 

[PHV]

So where is the problem ?
Which column in DataEntry contains WeekNum and how are named the 53 sheets ?
BTW, in wich workbook is located the DataEntry sheet ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[swtrader]

Skip...point well-taken. This is not necessarily an end-all. Ultimately, the solution will be Access. For now, this is the simplest and quickest Excel approach.

PHV...WeekNum is the column that holds the week number. Each sheet is named for its week number "22", "23", etc.

"WeekNum" is derived from the Date by =WeekNo(A2) -- Will this have to be converted to a value?

thanks.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

Each sheet is named for its week number "22", "23", etc.
Well, I'm not sure it's a good idea to give a sheet a numeric name ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[swtrader]

Good idea....will rename them wk1, wk2, etc.

Here's my problem: If I have only 4 sheets in the same workbook, how can I copy 50 rows from the DataEntry sheet to the correct report sheet (named wk1, wk2, wk3) using the name of the sheet that is in cell C of each row? (i.e., how do I reference the sheet by referring to the value in the cell?)

thanks for your help.

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[PHV]

something like this ?
Workbooks("workbook name").Sheets("wk" & Range("C" & x).Value)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886