Home

Macro in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-678906%22%20slang%3D%22en-US%22%3EMacro%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678906%22%20slang%3D%22en-US%22%3E%3CP%3EI%E2%80%99m%20new%20to%20Macro%20in%20excel%20and%20I%20was%20wondering%20on%20how%20to%20apply%20macro%20rules%20on%20previous%20sheets%20without%20the%20rule%20looking%20for%20the%20sheet%20name%20and%20not%20the%20order%20of%20sheets.%3CBR%20%2F%3EExample.%20I%20use%20excel%20for%20finance%2C%20I%20have%20sheets%20dated%20from%201-6-2019%20till%2030-6-2019.%20I%E2%80%99m%20trying%20to%20set%20a%20macro%20rule%20were%20it%20will%20fetch%20data%20from%20previous%20work%20sheet%20but%20it%20is%20always%20returning%20result%20from%20the%20sheet%20initially%20set%20in%20the%20recording%20process.%3CBR%20%2F%3Eto%20clarify%20further-more%2C%20I%E2%80%99m%20working%20in%20sheet%204-6-2019%20and%20I%20want%20the%20rule%20to%20look%20in%20the%20previous%20work%20sheet%20labelled%203-6-2019.%20when%20i%20run%20the%20rule%20on%20sheet%205-6-2019%20the%20macro%20will%20still%20look%20in%20sheet%203-6-2019%20and%20return%20result%20to%20sheet%204-6-2019.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20solve%20this%20issue.%3C%2FP%3E%3CP%3Ebelow%20are%20the%20steps%20and%20im%20not%20sure%20if%20they%20might%20help%20you%20in%20understanding%20more%20the%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E'%20DS%20Macro%3CBR%20%2F%3E'%20ds%3CBR%20%2F%3E'%3CBR%20%2F%3E'%20Keyboard%20Shortcut%3A%20Ctrl%2Bq%3CBR%20%2F%3E'%3CBR%20%2F%3EActiveCell.Offset(0%2C%20-3).Range(%22A1%3AB114%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3ESheets(%224-6-2019%22).Select%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CEM%3E%3CSTRONG%3E(i%20want%20to%20set%20sheets(%224-6-2019%22)%20as%20previous%20sheet%20so%20if%20im%20sheet%2017-6-2019%20it%20will%20fetch%20from%20sheet%2016-6-2019)%3C%2FSTRONG%3E%3C%2FEM%3E%3CBR%20%2F%3EActiveCell.Offset(0%2C%20-1).Range(%22A1%3AA2%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%225-6-2019%22).Select%3CBR%20%2F%3EActiveCell.Offset(2%2C%20-7).Range(%22A1%3AA2%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ESheets(%224-6-2019%22).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%204).Range(%22A1%3AA2%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%225-6-2019%22).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%204).Range(%22A1%3AA2%22).Select%3CBR%20%2F%3EActiveSheet.Paste%3CBR%20%2F%3ESheets(%224-6-2019%22).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%20-3).Range(%22A1%3AA2%22).Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3ESelection.Copy%3CBR%20%2F%3ESheets(%225-6-2019%22).Select%3CBR%20%2F%3EActiveCell.Offset(0%2C%20-3).Range(%22A1%3AA2%22).Select%3CBR%20%2F%3ESelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%3CBR%20%2F%3E%3A%3DFalse%2C%20Transpose%3A%3DFalse%3CBR%20%2F%3EActiveCell.Select%3CBR%20%2F%3EApplication.CutCopyMode%20%3D%20False%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%2225726%22%3CBR%20%2F%3EActiveCell.Offset(1%2C%200).Range(%22A1%22).Select%3CBR%20%2F%3EActiveCell.FormulaR1C1%20%3D%20%22-24384%22%3CBR%20%2F%3EActiveCell.Offset(15%2C%201).Range(%22A1%3AB1%22).Select%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-678906%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-678921%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-678921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F356646%22%20target%3D%22_blank%22%3E%40Taan87%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Eyou%20don't%20need%20to%20select%20ranges%20and%20sheets.%20For%20example...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20GetDataFromSheetBefore()%3CBR%20%2F%3EDim%20i%20As%20Integer%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20i%20%3D%202%20To%20Worksheets.Count%3CBR%20%2F%3E%3CBR%20%2F%3EWorksheets(i).Range(%22A1%3AA2%22).Value%20%3D%20Worksheets(i%20-%201).Range(%22A1%3AA2%22).Value%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20i%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22http%3A%2F%2Fwww.vba-Tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Taan87
Frequent Visitor

I’m new to Macro in excel and I was wondering on how to apply macro rules on previous sheets without the rule looking for the sheet name and not the order of sheets.
Example. I use excel for finance, I have sheets dated from 1-6-2019 till 30-6-2019. I’m trying to set a macro rule were it will fetch data from previous work sheet but it is always returning result from the sheet initially set in the recording process.
to clarify further-more, I’m working in sheet 4-6-2019 and I want the rule to look in the previous work sheet labelled 3-6-2019. when i run the rule on sheet 5-6-2019 the macro will still look in sheet 3-6-2019 and return result to sheet 4-6-2019.

Is there a way to solve this issue.

below are the steps and im not sure if they might help you in understanding more the issue.

 

' DS Macro
' ds
'
' Keyboard Shortcut: Ctrl+q
'
ActiveCell.Offset(0, -3).Range("A1:B114").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("4-6-2019").Select                 (i want to set sheets("4-6-2019") as previous sheet so if im sheet 17-6-2019 it will fetch from sheet 16-6-2019)
ActiveCell.Offset(0, -1).Range("A1:A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("5-6-2019").Select
ActiveCell.Offset(2, -7).Range("A1:A2").Select
ActiveSheet.Paste
Sheets("4-6-2019").Select
ActiveCell.Offset(0, 4).Range("A1:A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("5-6-2019").Select
ActiveCell.Offset(0, 4).Range("A1:A2").Select
ActiveSheet.Paste
Sheets("4-6-2019").Select
ActiveCell.Offset(0, -3).Range("A1:A2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("5-6-2019").Select
ActiveCell.Offset(0, -3).Range("A1:A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "25726"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "-24384"
ActiveCell.Offset(15, 1).Range("A1:B1").Select

1 Reply

@Taan87 

Hi,

you don't need to select ranges and sheets. For example...

 

Sub GetDataFromSheetBefore()
Dim i As Integer


For i = 2 To Worksheets.Count

Worksheets(i).Range("A1:A2").Value = Worksheets(i - 1).Range("A1:A2").Value

Next i

End Sub

 

Regards

Bernd

www.vba-Tanker.com

Related Conversations