Macro to copy a row/rows in an excel workbook for a given date/dates to another sheet.

[THIRU SAKTHI KUMAR K]

Hai,

Kindly help me with a macro to copy a row/rows in an excel workbook for a given date/dates to another sheet.

I had attached an excel sheet wherein the followings need to be done,

1. For given date/dates, all columns in the workbook from A1 to A13 need to be copied to another workbook/sheet
2. Similarly, all columns in the workbook from B1 to B9 need to be copied to another workbook/sheet.

Kindly help me to solve this with a macro.

Thank You,

WBR,
K.Theru Sakthe Kumar


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[rahulshewale1]

hii,

Could you please provide desire output.(ten entry).? so we might help .


Regard
Rahul shewale

 

[THIRU SAKTHI KUMAR K]

Hai,

Please find attached the required output that I require, and that for a date entered by us.

WBR,
KTSK

 

[rahulshewale1]

Hii,

is it ok with formula base solution? same result can be possible with formula
pfa sheet.


=IFERROR(IF($B2=350,INDEX('350'!$A$1:$Y$597,MATCH(B!$A2,'350'!$A$1:$A$597,0),MATCH(B!N$1,'351'!$A$1:$Y$1,0)),IF($B2=351,INDEX('351'!$A$1:$Y$597,MATCH(B!$A2,'351'!$A$1:$A$597,0),MATCH(B!N$1,'351'!$A$1:$Y$1,0)),IF($B2=352,INDEX('352'!$A$1:$Y$597,MATCH(B!$A2,'352'!$A$1:$A$597,0),MATCH(B!N$1,'352'!$A$1:$Y$1,0))))),"")


Thanks
Rahul shwale

 

[THIRU SAKTHI KUMAR K]

Hai,

Could you help me with a macro, by giving an option to input the date (from and to date)?

WBR,
KTSK

 

[THIRU SAKTHI KUMAR K]

Hai,

I tried the below macro, wherein the last row of all the sheets in a workbook is consolidated to a single sheet.

But, I need the row(s) of a given date to be consolidated into a separate sheet.

Could you help me?

Private Sub Worksheet_Activate()

'Consolidates data from the range A162:U163 for every tab except the one it's part of.

Dim wrkSheet As Worksheet
Dim rngCopy As Range
Dim lngPasteRow As Long
Dim strConsTab As String

strConsTab = ActiveSheet.Name 'Consolidation sheet tab name based on active tab.

If Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row >= 2 Then
If MsgBox("Do you want to clear the existing consolidated data in """ & strConsTab & """", vbQuestion + vbYesNo, "Data Consolidation Editor") = vbYes Then
Sheets(strConsTab).Range("A2:Q" & Cells(Rows.Count, "A").End(xlUp).Row).ClearContents
End If
End If

Application.ScreenUpdating = False

For Each wrkSheet In ActiveWorkbook.Worksheets

If wrkSheet.Name <> strConsTab Then

Set rngCopy = wrkSheet.Range("A162:U163")
lngPasteRow = Sheets(strConsTab).Cells(Rows.Count, "A").End(xlUp).Row + 1
rngCopy.Copy Sheets(strConsTab).Range("A" & lngPasteRow)
Application.CutCopyMode = False

End If

Next wrkSheet

Application.ScreenUpdating = True

MsgBox "The workbook data has now been consolidated.", vbInformation, "Data Consolidation Editor"

End Sub


WBR,
KTSK