Macro to work regardless of sheets

[willh22]

Hi, I recorded a macro yesterday with about 10 steps, and was very pleased that I got it working for Sheet 1, I then entered data into sheet 2 in exactly the same format (all columns the same etc), and ran the Macro, without any errors, but the results it gave weren't accurate.

I read a load of stuff and understand the macro always refers back to the original data etc. I wanted to give editing the vba code a go, but it didn't look anything like the code that the posts I saw were suggesting making edits to.

Please can somebody help me make my macro work in what ever sheet I am currently in?

Sub Sourcecodes()
'
' Sourcecodes Macro
'


'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveCell.Offset(0, 24).Columns("A:A").EntireColumn.Select
Selection.Copy
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveCell.Offset(0, 16).Columns("A:A").EntireColumn.Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 27
Application.CutCopyMode = False
Selection.AutoFilter
ActiveCell.Range("Table1[[#All],[RESORT]]").AdvancedFilter Action:= _
xlFilterCopy, CopyToRange:=ActiveCell.Offset(0, 1).Range( _
"Table1[[#Headers],[RESORT]]"), Unique:=True
ActiveWindow.SmallScroll ToRight:=5
ActiveCell.Offset(1, 2).Range("Table1[[#Headers],[RESORT]]").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-18],RC[-1],(C[-16]))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A76")
ActiveCell.Range("A1:A76").Select
ActiveCell.Offset(0, 1).Range("Table1[[#Headers],[RESORT]]").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-19],RC[-2],(C[-22]))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A76")
ActiveCell.Range("A1:A76").Select
ActiveCell.Offset(0, 1).Range("Table1[[#Headers],[RESORT]]").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-20],RC[-3])"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A76")
ActiveCell.Range("A1:A76").Select
ActiveCell.Offset(0, -2).Columns("A:A").EntireColumn.Select
Selection.Style = "Currency"
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Revenue"
ActiveCell.Offset(0, 1).Range("Table1[[#Headers],[RESORT]]").Select
ActiveCell.FormulaR1C1 = "Room Nights"
ActiveCell.Offset(0, 1).Range("Table1[[#Headers],[RESORT]]").Select
ActiveCell.FormulaR1C1 = "Bookings"
ActiveCell.Offset(0, -3).Columns("A:D").EntireColumn.Select
ActiveCell.Columns("A:D").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(6, 5).Range("Table1[[#Headers],[RESORT]]").Select
End Sub
Sub Test2()
'
' Test2 Macro
'


'
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
Columns("Y:Y").Select
Selection.Copy
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
Columns("AO:AO").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFilter
Range("AO1:AO651").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"AP1"), Unique:=True
Range("AP2").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 67
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.SmallScroll ToRight:=5
Range("AQ2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-18],RC[-1],(C[-16]))"
Range("AQ2").Select
Selection.AutoFill Destination:=Range("AQ2:AQ35")
Range("AQ2:AQ35").Select
Range("AR2").Select
ActiveCell.FormulaR1C1 = "=SUMIF(C[-19],RC[-2],(C[-22]))"
Range("AR2").Select
Selection.AutoFill Destination:=Range("AR2:AR35")
Range("AR2:AR35").Select
Range("AS2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-20],RC[-3])"
Range("AS2").Select
Selection.AutoFill Destination:=Range("AS2:AS35")
Range("AS2:AS35").Select
Range("AQ1").Select
ActiveCell.FormulaR1C1 = "Revenue"
Range("AR1").Select
ActiveCell.FormulaR1C1 = "Room Nights"
Range("AS1").Select
ActiveCell.FormulaR1C1 = "Bookings"
Columns("AP:AS").Select
Columns("AP:AS").EntireColumn.AutoFit
Columns("AQ:AQ").Select
Selection.Style = "Currency"
Range("AU16").Select
End Sub


Thanks!!

 

[Norie]

The code doesn't refer back to the original data, it refers to the currently active sheet.

How did the code you recorded for Sheet1 not work for Sheet2?

Are Sheet1 basically identical, or are there some significant differences?

 

[willh22]

The problem seems to be with the part that is copying only the unique values to the next column. It works fine for the first one, but it's almost as if when there are more rows in sheet2, it only selects the same number of rows as sheet1 - is this possible?

I need it to copy unique values from the whole of AO:AO, not just rows 1-1000 or however many sheet1 contains... does this make sense?

Each row is an individual booking for a hotel, so depending on the date range I have the data for, there will be varying amounts of rows, but all columns are always the same

 

[Norie]

It's perfectly possible if there are more rows that they won't be selected.

That's because the ranges in the code are hard-coded based on Sheet1.

So I suppose the code is referring back to Sheet1 but not its data.

You'll need to change the code to handle varying no of rows.

Not sure where you would do that - the code's a bit messy, especially with all the ScrollRow/ScrollColumn stuff which can just be deleted.

 

[willh22]

Ok, that's what I posted in here for - I don't know how to write code for this, so need some help.

There must be a way you can tell the macro to reference the whole column, like you can when writing simple formulas? I assumed I had done that when I recorded the macro by selecting the whole column?

Please can you let me know how I can get the help I need?

 

[Norie]

Remove all the ScrollRow/Column code from the code and post it back inside code tags.

It'll then be a lot easier to read and help with.

I think I know what needs changed and it would involve finding out how many rows there were rather than just changing to the entire column.

Using the entire column could actually cause problems, for example it could slow the code down considerably.

 

[willh22]

I'm sorry - this is still why I came here for help - I don't want to remove the scrollrow/column code as I don't know how much to remove...

Also, what are code tags?

Are you able to help using the code I pasted above?