Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop

user29383902

New Member
Joined
Dec 6, 2024
Messages
8
Office Version
  1. Prefer Not To Say
Platform
  1. 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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Forum statistics

Threads
1,226,642
Messages
6,192,177
Members
453,705
Latest member
correygold

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
Back
Top