user29383902
New Member
- Joined
- Dec 6, 2024
- Messages
- 8
- Office Version
- Prefer Not To Say
- Platform
- Windows
I recorded a Macro, to get this, but the record can't actually work with returning the value and going to the next row on it's own. I'd really appreciate any help. If this can be done with a Formula or VBA macro, i'd greatly appreciate, thanks.
VBA Code:
Sub MacroTest ()
'
' MacroTest Macro
'
'fileX and FileY are worksheets.
'fileX contains 1 sheets and a Macro
'fileY contains 15 sheets and data in these sheets
Windows("fileX").Activate ' i have 500 rows in just one sheet
Rows("1:1").Select 'select the first row in fileX
Selection.Copy
Windows("fileY").Activate
'if fileX.sheet.range(G1).value = fileY.sheetname
'that is the g1 value of fileX's sheet should match the sheet name of fileY
'then activate sheet in the fileY and paste in row 2:2 (this row 2:2 does not need to increase because of the filter)
' so there needs to be a loop through all 15 sheets
'to get the match name (when g1value = fileY sheetname).
ActiveSheet.Paste 'this pastes the value in the sheet that has been matched.
Application.CutCopyMode = False ' i do not know what this does actually.
Application.Run "'fileX.xls'!Macro1" ' this activates a macro for autofilter and run it.
'the autofilter returns a value
Windows("fileX").Activate
'if the autofilter returns a row (that is a data) or not
'fileX should be activated
'it should indicate it and put a value of 1 where data is found, (2 or more rows returned after the filter)
'i included 2 or more, because after excel filters, if there is no data
'it indicates a value of 1, that is the row that is being filtered.
'else it should put a value of 0, where no data is found, (1 row (pasted row) returned after the filter
'this value should be placed at A1 of worksheet fileX.
'this above is for the first row in FileX, then this should be done in all 500 rows of fileX.
'so there needs to be a loop which increases some values
'these values needs to be increased:
'Row(1:1) needs to go to Row(2:2), Row(3:3) and so on... **this applies to FileX only as file Y does not increase.
'G1 needs to go to G2, G3 and so on...
'A1 needs to go to A2, A3 and so on...
End Sub