VBA IF recognise text THEN paste in Master

Sworks

New Member
Joined
May 12, 2021
Messages
5
Platform
  1. Windows
Hi,

I currently have a number of sheets containing data.
Each sheet contains a cell with the following string "XXXX - followed by a number" the XXXX are always in the cell.
The cell is not always in the same location for each sheet e.g. A1 or B5.

Is it possible to have a VBA loop through all sheets to recognise the 4 X's and paste the cell into a master sheet? Similar to ctrl f and ctrl c ctrl v but automated.

Thanks,

S
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,184
try this
VBA Code:
Sub do_it()

wr = 1
For Each ws In Worksheets

If ws.Name = "Master" Then GoTo 99


For Each cel In ws.Range("A1:B10")
    If InStr(cel, "XXXX") > 0 Then
        Worksheets("Master").Cells(wr, "A") = cel
        wr = wr + 1
    End If
Next

99 Next


End Sub
 

Sworks

New Member
Joined
May 12, 2021
Messages
5
Platform
  1. Windows
try this
VBA Code:
Sub do_it()

wr = 1
For Each ws In Worksheets

If ws.Name = "Master" Then GoTo 99


For Each cel In ws.Range("A1:B10")
    If InStr(cel, "XXXX") > 0 Then
        Worksheets("Master").Cells(wr, "A") = cel
        wr = wr + 1
    End If
Next

99 Next


End Sub
Hi there, thanks for the help.

I'm getting a "type missmatch" error on " If InStr(cel, "XXXX") > 0 Then"

I am not too sure why? XXXX in my situation are letters and maybe that's why.

Thanks,

S
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,385
Office Version
  1. 365
Platform
  1. Windows
If the string of "XXXX" (whatever it is you are looking for) is always at the beginning of the cell, you can replace this line:
VBA Code:
If InStr(cel, "XXXX") > 0 Then
with this instead:
VBA Code:
If Left(cel,4) = "XXXX" Then

If you are still having issues, it might be helpful if you post a sample of what your data looks like.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Forum statistics

Threads
1,137,120
Messages
5,679,729
Members
419,853
Latest member
hc9587

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