Vincent88
Active Member
- Joined
- Mar 5, 2021
- Messages
- 382
- Office Version
- 2019
- Platform
- Windows
- Mobile
Hi EveryOne, I need to copy the data of the last 5 days from previous-month sheet (let says sheetname is 202109) to a newly created sheet, the following month of previous month (let says sheetname is 202110).
SheetName is in format(yyyymm)
Of all sheets, Row 1 from Column C to AL states the dates from 5 days in previous month + total days in the month of the sheetname.
The destination cells In new sheet to paste will always be from column C to G whereas the position of the last 5 days of previous month in previous sheet will be varied becuz numbers of days in every month is varied particular in February. See images attached.
My code is hardcoded the sheetname and cells' addresses of previous month. Need assistance to amend them since it will be a routine job to do when a new-month sheet is created.
SheetName is in format(yyyymm)
Of all sheets, Row 1 from Column C to AL states the dates from 5 days in previous month + total days in the month of the sheetname.
The destination cells In new sheet to paste will always be from column C to G whereas the position of the last 5 days of previous month in previous sheet will be varied becuz numbers of days in every month is varied particular in February. See images attached.
My code is hardcoded the sheetname and cells' addresses of previous month. Need assistance to amend them since it will be a routine job to do when a new-month sheet is created.
VBA Code:
Sub LastMonthData17()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range, rng2 As Range
Dim c As Range, f As Range
Dim lastrow1 As Long
Dim lastrow2 As Long
Dim rng1Col As Range, rng2Col As Range
Set ws1 = Sheets("202108") 'Need to Change to previous month of ActiveSheetName
Set ws2 = ActiveSheet
lastrow1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = ws1.Range("A3:AL" & lastrow1)
Set rng2 = ws2.Range("A3:G" & lastrow2)
Set rng1Col = ws1.Range("A3:AL" & lastrow1).Columns(1) 'Column A in Sheet of previous month
Set rng2Col = ws2.Range("A3:G" & lastrow1).Columns(1) 'Column A in Activesheet
For Each c In rng1Col.Cells
Set f = rng2Col.Find(c.Value, , xlValues, xlWhole)
'Search the last 5 days of previous month then copy & paste to column C:G in ActiveSheet
If Not f Is Nothing Then
f.Offset(, 2).Resize(, f.Columns.Count + 4).Value = _
c.Offset(, 33).Resize(, c.Columns.Count + 4).Value 'Need to amend also'
End If
Next c
'IF NO MATCH, THEN PUT BLANK IN CELLS
For Each c In rng2Col
If IsEmpty(c.Value) Then f.Offset(, 2).Resize(, f.Columns.Count + 4).Value = ""
Next
End Sub