Copy row array with variable range

[renigar]

Hi,
I have a macro I previously wrote that creates a monthly log file for 10 pump stations. It copies the daily reads from a file where some cells are populated from a SQL server and some manually. Each station has it's own sheet and the file is named for the month, ex. "Feb-2003.xls". A button on the Daily Reads sheet creates (on the 1st of new month) and/or updates the monthly log. This works great except I have to manually copy & paste last months final reads forward. Problem code follows:

Workbooks.Open (LastMonthFile)
Sheets(Array("S-2", "S-4", Etc. 10 stations total).Select
Worksheets("S-2&quot.Range(?:?).Select
' How do I set up this range, I have a variable
' representing the last day of the month and row to copy.
' I need something like
' Worksheets("S-2&quot.Range("C" LastDay:"N" LastDay).Select
' Column range is C to N.
Selection.Copy

Windows(CurrentMonth).Activate ' Current month is already open
Range("C6&quot.Select
ActiveSheet.Paste
Windows(LastMonth).Activate
ActiveWindow.Close

I'm sure this is easy but, I just can't see it.
Any help would be appreciated.
renigar

 

[Zathras]

I would just do a final save of last month's workbook and then modify it and save it under this month's name. It would be much easier to work within one workbook, one sheet at a time: Copy final totals and paste special values to the starting balances. Then clear out the details and you should be ready to go.

Here is a macro that should be able to help:

Option Explicit
Const FIRST_COL = 3   ' Column "C"
Const LAST_COL = 14   ' Column "N"
Const BEGINNING_READS = "C6:N6"

Sub demo()
Dim rFirstCell As Range
Dim rLastCell As Range
Dim rToCopy As Range
Dim rToPaste As Range

  Set rFirstCell = Cells(65536, FIRST_COL).End(xlUp)
  Set rLastCell = Cells(65536, LAST_COL).End(xlUp)
  Set rToCopy = Range(rFirstCell, rLastCell)
  Set rToPaste = Range(BEGINNING_READS)
  rToCopy.Copy
  rToPaste.PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  rToPaste.Cells(1, 1).Select
  Set rFirstCell = Nothing
  Set rLastCell = Nothing
  Set rToCopy = Nothing
  Set rToPaste = Nothing
End Sub

 

[renigar]

Thanks Zathras,
I didn't reply earlier because I've had trouble getting on this site. Trying the busy time of day I guess.

I stumbled on to this in the Excel VBA help:

Range(Cells(1, 1), Cells(5, 3)).

It allows variables in place of the numbers and works for me. Plus I didn't have to rewrite my macro.

Thanks again for your response.
Renigar