Loop macro but change cell reference

Eros3d

New Member
Joined
Dec 17, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
This macro may not be set out correctly/efficiently but it works so i didn't dare change it. I recorded it and didn't impute to much manually.

Basically I change the dates daily which is fine but I was thinking is there a way to loop the macro changing the cells highlighted in bold after each time the macro completes? i.e F107 becomes F108? Until there is no value in column F workbook cards? If this last part is too complicated i could just count the amount of times i need it to loop and enter that instead. Thanks :)

Sub Purge()
Workbooks("Memos 04.09.2020").Sheets("Memos").Range("A1").AutoFilter Field:=4, Criteria1:=Workbooks("Cards 04.09.2020").Sheets("Cards").Range _ (**"F107"**).Value
Workbooks("Transactions 04.09.2020").Sheets("Transactions").Range("A1").AutoFilter Field:=2, Criteria1:=Workbooks("Cards 04.09.2020").Sheets("Cards").Range _ (**"F107"**).Value
Dim objWord As New Word.Application
Workbooks("Cards 04.09.2020").Sheets("Cards").Range _ ("**B107**:**C107**").Copy

With objWord
.Documents.Add
.Selection.PasteAndFormat (wdFormatPlainText)
.Selection.TypeParagraph
.Visible = True

Workbooks("Cards 04.09.2020").Sheets("Cards").Range _ ("**D107:F107**").Copy
.Selection.PasteAndFormat (wdFormatPlainText)
.Selection.TypeParagraph
.Selection.TypeParagraph
.Selection.TypeText ("Transactions")
.Visible = True

Workbooks("Transactions 04.09.2020").Sheets("Transactions").Range("E:G").Copy
.Selection.PasteExcelTable False, True, False
.Selection.TypeParagraph
.Visible = True

Workbooks("Memos 04.09.2020").Sheets("Memos").Range("E:K").Copy
.Selection.TypeParagraph
.Selection.TypeParagraph
.Selection.TypeText ("Memos")
.Selection.PasteExcelTable False, True, False
.Visible = True
End With
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,129,297
Messages
5,635,376
Members
416,856
Latest member
silentir

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top