Hi I have a workbook contains about 600 worksheets and I would like to extract data from fixed rang e.g A1 to G3 and G4 to G23 and save them anywhere to print it later I need help to write macro thanks a lot

Could you gives us a little more detail on what you mean by "extract data from fixed range ... and save them anywhere to print it later"? If all you want to do is print out those specific ranges, this code will do the folowing: - Loop through each sheet in your workbook - Save the current PrintArea in a variable - Print out the ranges you asked for, sheet by sheet - Reset the PrintArea for each sheet to the original setting If that is not what you are looking for, please explain your goal in a little more detail. Sub MyPrintArea() For Shts = 1 To Sheets.Count CurPrintArea = Sheets(Shts).PageSetup.PrintArea Sheets(Shts).PageSetup.PrintArea = "$A$1:$G$3,$G$4:$G$23" Sheets(Shts).PrintOut Sheets(Shts).PageSetup.PrintArea = CurPrintArea Next End Sub

Thank you DerbyDad03: Sorry I was not clear, what I meant that I like to copy certain cells which they repeat them selves in each worksheets,so those cells always they are (A1 to G3) and (G4 to G23), so I like to copy them in separate worksheet so I can do some analysis later, hopefully this clear enough, and thanks again

Sorry, but it's still not clear to me what you want to do. First issue: You said you have 600 sheets and you said A1:G3 and G4:G23 "repeat themselves in each work sheet". Does that mean that each of those cells contains the same value in all 600 sheets? In other words, as examples: Sheet1!A1 = Sheet2!A1 ... = ... Sheet600!A1 and Sheet1!G3 = Sheet2!G3 ... = ... Sheet600!G3 Second issue: In your first post you said "save them anywhere to print it later" but in your second post you said "copy them in separate worksheet so I can do some analysis later" I still don't think I have enough information to help you.

Hi DerbyDad03 Sorry again (Sheet1!A1 = Sheet2!A1 ... = ... Sheet600!A1) no they are not equal what I meant the data are at the same place in each sheet,but they are different in value. Second I tried to be specific where I like to save it, but really still doesn't matter where to save it as long I know where to access it hopefully this is clear enough thanks a lot

OK, let's recap... - You have 600 sheets in a workbook - Each sheet has a data set in A1:G3 and G4:G23 - You want to copy those data sets to "it doesn't matter as long you know where to access it" If that's correct, try this: This code will add a new sheet to the end of your workbook and then loop through the other sheets, copying each data set, one under the other, into the last sheet. When it's done, you'll have a sheet with 13,800 rows of data (3 x 600 for A1:G3 and 20 x 600 for G4:G23) and you'll know where to access it. Sub RealCriteriaNotKnown() 'Add A Sheet After The Last Sheet Sheets.Add After:=Sheets(Sheets.Count) 'Initialize Row Counter sRow = 1 'Loop Through All Sheets Except The Last One For Shts = 1 To Sheets.Count - 1 'Copy A1:G3 Sheets(Shts).Range("A1:G3").Copy 'Paste Each Sheet's A1:G3 Into The Last Sheet Below Previous Sheet's Data Sheets(Sheets.Count).Cells(sRow, 1).PasteSpecial 'Copy G4:G23 Sheets(Shts).Range("G4:G23").Copy 'Paste Each Sheet's G4:G23 Into The Last Sheet Below The A1:G3 Data Sheets(Sheets.Count).Cells(sRow + 3, 1).PasteSpecial 'Determine Next Empty Row In The Last Sheet So We Know Where To Paste Next sRow = Sheets(Sheets.Count).Range("A" & Rows.Count).End(xlUp).Row + 1 'Loop Next End Sub

HI DerbyDad03 Thank you a lot this is exactly what I need and when I ran the code I received error message says: Run time 438 Object doesn't support this properly or method