0

Im looking for a VBA Code which essentially counts the times the macro is run and resets the program. The macro currently copies an array of data and pastes it onto a seperate sheet. Each week the data will change so the macro will be run every week to get that weeks worth of data (the data is pasted directly below the last cell used in the previous weeks paste). Ideally, id like to partition the data to every 4 weeks and i beleive the best way to do this would be a count and reset function (if theres a better way then by all means)! Ideally, the code would also every four weeks start a new sheet for the data to be copied and name the previous one something like "Week 1-4". So after a few months id have an active sheet where the data for that 4-week period is running and a log of previous weeks names week 1-4, week 5-8, ... and so on

The closest ive got so far is assigning a cell which +1 everytime the macro is run and an IF function stating to reset after 4. But i have no idea how to go about the new sheet and naming thing.

Sub Historization()

workbookname = ActiveWorkbook.Name

'If Range("P3").Value >= 4 Then
'add new sheet in

'For Each ws In ThisWorkbook.Worksheets

'code in here

'Next
' loop through all worksheets


'worksheetname = ActiveSheet.Name
'Else

'End If
Workbooks("Historized_CAS_RK_Control.xlsm").Worksheets("Query").Activate

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Workbooks(workbookname).Select
Worksheets(worksheetname).Activate

Range("A1").Select
ActiveCell.End(xlDown).Select

ActiveSheet.Paste

thecount = Range("P3").Value
thecount = thecount + 1
Range("P3").Value = thecount

End Sub
Daniel Marschall
  • 3,739
  • 2
  • 28
  • 67
Harry Broad
  • 1
  • 1
  • Do you have your variables declared somewhere? – Mark Fitzgerald Jul 15 '19 at 08:55
  • To create the new sheet I suggest that you take a look at this https://stackoverflow.com/a/20697790/7862279 As for the name I'm not really sure how to do it yet. You can use this `MySheet = Right(Application.Caller.Worksheet.Name,1) ` to get the last number but I am not sure it is not exactly what you are looking for – Jack Jul 15 '19 at 08:56
  • Is there any way to find or calculate the week number from the data you are copying? BTW, I can't see a `.Copy` command. – Mark Fitzgerald Jul 15 '19 at 09:08
  • What issues are you having with the new worksheet? Trying to understand so can assist. – Dean Jul 15 '19 at 09:26
  • i can add a new data set with a time code on it. which would be in the form 'yyymmddhhmmss' so if the product was made at 11am today it would have the time stamp 20190715110000. the copy command is now in there i cut it temporarily to fix a seperate issue. – Harry Broad Jul 15 '19 at 09:58
  • The issue with the worksheet im having is being able to add a new work sheet every 4 weeks with the name Week 1-4, Week 5-8... – Harry Broad Jul 15 '19 at 09:59
  • IMO more robust way is algrorithm to process each new record from an input sheet and to decide which 4-week-sheet to move it on depending on time stamp, also to check if the target sheet exists prior to moving, and if not then create one. – omegastripes Jul 17 '19 at 05:16

0 Answers0