Copy specific cells from multiple worksheets in VBA

[JorgenKjer]

Hallo

Cananyone help me?

I have aworkbook with multiple worksheets
One worksheetcalled “Search” is where data need to be past to.
Allother worksheets have filter buttons that if in use needs resetting, show alldata, before start searching for data.
Worksheet“Search” needs to be deleted of all data from previous run from row 2 and down
New Datawill be past to row 2 and down
The codeneeds to loop through all worksheets and copy rows that meets criteria “XX.XX”in column C
Onlycells in Columns A, C, D, E, H in matching rows shall be past to worksheet “Search”
I hopeall this makes sense.
I willbe grateful if anyone can help me.

Yourssincerely
JorgenKjer

 

[vcoolio]

Hello Jorgen

Try the following code:-

Sub Test()

        Dim ws As Worksheet
        Dim sh As Worksheet: Set sh = Sheets("Search")

Application.ScreenUpdating = False

sh.UsedRange.Offset(1).Clear

For Each ws In Worksheets
      If ws.Name <> "Search" Then
          With ws.[A1].CurrentRegion
                .AutoFilter 3, "XX.XX"
                Union(.Columns("A"), .Columns("C:E"), .Columns("H")).Offset(1).Copy sh.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
          End With
      End If
Next ws

Application.ScreenUpdating = True

End Sub

Based on your description, it should do the task for you.

I've assumed that all sheets have headings in Row1 with data starting in Row2.

I hope that this helps.

Cheerio,
vcoolio.

 

[JorgenKjer]

Hi

I don't know what i'm doing wrong but the code stops at

sh.UsedRange.Offset(1).Clear

 

[vcoolio]

Hello Jorgen,

What error message are you receiving?

Better still, upload a sample of your workbook to a free file sharing site such as Drop Box then post the link to your file back here.
Make sure that your sample is an exact replica of your workbook with just a few rows of data per sheet. If your data is sensitive, then please use dummy data.
This will enable us to test a code with your data set up.

Cheerio,
vcoolio.

 

[JorgenKjer]

Hello vcoolio

I have put together a code, Iam sure it can be done much smarter, but it works

The problems that is left is:

If any filter buttons in allthe worksheets are in use they need to be reset to show all data, before startsearching for data.

Copy / past only data from cellsin Columns A, C, D, E, H from rows in all the worksheet that matches the searchcriteria example 01.03

Sub Copy_Row_Multipel_Sheets()



Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet 'Remembers sheet that are active at startup


Application.ScreenUpdating = False 'No screen update while program is running



Worksheets("Search").Range("A2:Z1000").Clear 'Clear all Data from Search sheet


For Each ws In ThisWorkbook.Worksheets
ws.Activate ' Aktivate all Worksheets

a = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If ws.Cells(i, 3).Value = Sheets("Data").Range("C1") Then 'search criteria example 01.03 is defined in sheet "Data" cell C1
ws.Rows(i).Copy
Worksheets("Search").Activate

b = Worksheets("Search").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("Search").Cells(b + 1, 1).Select
ActiveSheet.Paste
End If

Next

Next



starting_ws.Activate 'Activate sheet that were active at startup


Worksheets("Search").Range("A1:Z1000").Columns.AutoFit


Application.ScreenUpdating = True 'Turn on screen update


End Sub

 

[vcoolio]

Hello Jorgen,

I'm not sure what it is that you are trying to do. I've tested the code supplied in post #2 in a sample of how I assume your workbook to be set out and it works perfectly.

You've created confusion as it now appears that your criteria will vary depending on what is placed in cell C1 of another worksheet named "Data". You did not mention this in your opening post.

Also, you've not answered my question in post #4 .

If you want us to help you then please answer any questions that are asked and follow any requests that we may make (e.g. supplying a sample workbook).
It is the simplest way to help you. For further help, please upload a sample of your workbook as requested in post #4 .

I would not use a For/Next loop to do this kind of search especially if you have many sheets with many rows of data to search through.

Cheerio,
vcoolio.

 

[JorgenKjer]

Hi vcoolio

Thankyou for trying to help me, but as you may have noticed, I'm computerilliterate. I have no knowledge of Dropbox and only a little knowledge of Excel,besides that there is certainly some language barrier on my part. I will notdisturb you further and thank you once again for trying to help me.

Sincerely
JorgenKjer

 

[vcoolio]

Hello Jorgen,

Don't give up just yet! Our aim is to help you not discourage you. But we also need you to help us to help you. Hence our questions and requests.

Following is the link to a sample file I've prepared based on the information that you have given us in your opening post:-

http://ge.tt./3h4zU4x2

Click on the link to take you to the site from where you can download the sample.

The code from post #2 has been implemented in a standard module and assigned to the "RUN" button. Click on the "RUN" button to see it work.

Again, I'm not really sure how your workbook is set up and I would still like you to upload a sample so that we can sort this out for you.
You can go to the file sharing site I've used above or use another one (such as Drop Box) to upload your sample. It is a very simple process.

Waiting to hear from you.

Cheerio,
vcoolio.

 

[JorgenKjer]

Hi vcoolio

Thankyou for your patience
I havetried to figure out how to use Drop Box so here is a link, I hope it works
Much ofthe text is in Danish, I hope it doesn’t cause problems with the extra lettersin the Danish alphabet.
On thesheet "Search" in cell I1 there is a drop down list where I choosethe criterion that I want to search for.
The listis placed on the sheet "Data" in the same sheet I remove the text incell C1 so only numbers are left using a simple function.
Theexample you have sent to me is exactly what I'm looking for.
However,there is a challenge with the filters that are on the sheets to be searched, ifon one sheet is filtered to show for example 01.03 and I’m searching for 01.04 the code I have made willnot find the Rows containing 01.04. therefore, all filters on all sheets mustbe reset before searching.
I hopeall this makes sense.

Sincerely
JorgenKjer

https://www.dropbox.com/sh/t3lr38mn75q2epo/AAAAurPfLOAjp1cEu0CQXL-pa?dl=0

 

[vcoolio]

Thanks for that Jorgen. I'll have a look at it tonight.

Cheerio,
vcoolio.