Record Macro Query- Ctrl + F & Ctrl + Shift + Arrow

[johnkjohnjr]

Hi All,
I am excited to join this platform as my interest in such forums have increased recently considering the repetitive tasks at hand.
Currently I am trying to create a macro using "Record Macro" option in Excel. On a weekly basis I receive data (demand file with required delivery dates, product ID numbers & quantities) from my client which typically fits into a standard format I have created in excel, followed by a series of tasks to visualize the data in a month wise format for updating my team at work.
The number of lines are not the same for every week hence I manually select all data by : go to cell A1 and press "Ctrl + Shift + right arrow key" , then "Ctrl + Shift + down arrow key". And then I create a pivot in the same work sheet. I then copy and paste this pivot as values into a separate sheet for updating. In this separate sheet , I have to consider the running month (current month) and select next 11 cells in series (Shift + right arrow key) and the corresponding data (Ctrl + Shift + down arrow key).
I would appreciate some guidance on how to resolve 3 current gaps I am facing since I am not familiar with using VB editor for debug :

1) Recording "Ctrl+Shift+any arrow key"
2) Recording " Ctrl + arrow key"
3) Using "Ctrl + F" to find current month and then select next 11 cells to the right (as mentioned above)

Thanks for taking the time on this.

 

[Joe4]

Welcome to the Board!

Recording the Macro will give you most of the code you need. After recording it, you can make some small edits to it make it more dynamic (mostly, probably just removing the hard-coded address "select" statements"). Note that most of the time, it is not necessary to select range in order to work with, that is just a product of the Macro Recorder being very literal.

If you manually record yourself working through an example and post your code here, we can probably help you edit it to make it dynamic and more efficient.

 

[johnkjohnjr]

The following code has both the "Ctrl +F" & "Ctrl+Shift+arrow" :

Sub EDI_sample_run()
'
' EDI_sample_run Macro
'

'
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Data!R1C1:R869C15", Version:=6).CreatePivotTable TableDestination:= _
"Data!R1C20", TableName:="PivotTable2", DefaultVersion:=6
Sheets("Data").Select
Cells(1, 20).Select
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Firm / Planned")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Material")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("EX Factory Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("EX Factory Date").AutoGroup
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Balance"), "Sum of Balance", xlSum
Range("N9").Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
Selection.End(xlToRight).Select
ActiveSheet.PivotTables("PivotTable2").PivotSelect "'2025'", xlDataAndLabel, _
True
Range("W4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Years").PivotItems("2025"). _
ShowDetail = True
Range("V4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Years").PivotItems("2024"). _
ShowDetail = True
Range("X5").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarters").PivotItems( _
"Qtr3").ShowDetail = True
Range("W5").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarters").PivotItems( _
"Qtr2").ShowDetail = True
Range("V5").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarters").PivotItems( _
"Qtr1").ShowDetail = True
Range("U4").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Years").PivotItems("2023"). _
ShowDetail = True
Range("Z5").Select
ActiveSheet.PivotTables("PivotTable2").PivotFields("Quarters").PivotItems( _
"Qtr4").ShowDetail = True
Range("T6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range("T6:AT21").Select
Selection.Copy
Sheets("F+P").Select
Selection.End(xlUp).Select
Selection.End(xlToLeft).Select
Range("A2").Select
ActiveSheet.Paste
Range("B12").Select
Sheets("Macro_Reference").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("F+P").Select
Cells.Find(What:="Oct", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
Columns("G:G").Select
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Range("G2").Select
ActiveCell.FormulaR1C1 = "backlog"
Range("G3").Select
End Sub

Hope you can help me out

 

[johnkjohnjr]

Can somebody please help ? It would be great.

 

[Joe4]

I would appreciate some guidance on how to resolve 3 current gaps I am facing since I am not familiar with using VB editor for debug :

1) Recording "Ctrl+Shift+any arrow key"
2) Recording " Ctrl + arrow key"
3) Using "Ctrl + F" to find current month and then select next 11 cells to the right (as mentioned above)

If you isolate each one, you can easily see what is going on and how to do that, i.e.
for item #2:

Range(...).End(xlDown) - down arrow
Range(...).End(xlUp) - up arrow
Range(...).End(xlToRight) - right arrow
Range(...).End(xlToLeft) - left arrow

For item #1, you can choose some of the methods shown above.
For example, for CTRL + SHIFT + Down Arrow (to select the range starting the in active cell and going to the last cell in that contiguous range downward), use this:

Range(ActiveCell, ActiveCell.End(xlDown)).Select

The other directions will be similar, just changing the "xl" direction, like shown above for item #2.

For item #3, you can set the the find value each to a range variables, like this:

Dim rng as Range
Set rng = Cells.Find(What:="Oct", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False)

If it cannot find it, then the rng variable will be empty. So you can test it like this, and if found, move 11 cells to the right:

If rng is Nothing then
    MsgBox "Cannot find value"
Else
    rng.Offset(0, 11).Select
End If

 

[johnkjohnjr]

Thanks for your help !

With your guidance, I've successfully implemented the suggested code for item#1 & item#2

For item#3, I do have an query.

CASE :
I've put a formula in one sheet for current month in 1 cell. I click on the cell & press "ctrl + c", and then I press "ctrl +f " & then "ctrl +v".
Hence I'm trying to copy and paste whatever text in the cell (which has formula for current month MMM) , however the code for find is reflecting "Oct".

What changes should I make to the code (sample below) ?

Example :
If today is September then Sep,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug
If today is October then Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep

CODE taken from excel :
Sub Sample()
'
' Sample Macro
'

'
Sheets("Macro_Reference").Select
Range("E2").Select
Selection.Copy
Sheets("Data").Select
Cells.Find(What:="Oct", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

 

[Joe4]

Just use:

Sub Sample()
' Sample Macro
'
Sheets("Data").Select
Cells.Find(What:=Sheets("Macro_Reference").Range("E2"), After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

End Sub