Copy temp hidden sheets

Hello everyone
I have in Sheet1 two tables(Columns A:G) and (Columns I:O)
Based on sheet1.range("Q2:Q9") I need to copy two temp (will be hidden) sheets ..

First:
-----
TempA sheet would be copied and the new copied sheet would be renamed by Q2 value which is here "Com 1" and add the suffix "A" and copy range("A" & range("R2").value &":C" & range("S2").value) .. A2:C22 (according to the columns R & S
As for cell A3 in the new copied sheet would be the value of Sheet1.range("Q2").value

Second:
-------
TempB sheet would be copied and the new copied sheet would be renamed by Q2 value which is here "Com 1" and add the suffix "B" and copy range("I" & range("R2").value &":K" & range("S2").value) .. I2:K22 (according to the columns R & S
As for cell A3 in the new copied sheet would be the value of Sheet1.range("Q2").value


and so on with all the cells in range("Q2:Q9"). That means that the new sheets that would be created would be 8 * 2 = 16 sheets
COM 1A
COM 1B
COM 2A
COM 2B
COM 3A
COM 3B
And etc ...

Another last point to put borders in the new sheets for range("A4:L" & LastRow)

Thanks advanced for help

Hello everyone
Is every point clear ?
Please I need help

I appreciate any help in this topic please

I'm still waiting for help in this topic as it is urgent for me

Is everything clear my dear members? I need help in this topic

What have you tried that doesn't work?

What specific part of the code do you need help with?

Thanks a lot Mr. Shg for reply
In fact I don't have code and I prefer to receive help as I need professional and perfect solutions (I'm just a learner) ..
Thank you very much for offering help

If you want work done for you, rather than assistance in doing it yourself, you might consider posting in the professional services forum.

Thanks a lot for reply
I will try to start my own code although I was preferring to have a perfect solution ..and if I face any obstacle I will tell you
I'm learning excel for fun most of the time ..

YasserKhalil,

Thanks for the Private Message, and, confidence.

When I attempted to open/download your workbook I received the following message:

Excel found unreadable content in 'Copy Temp Sheets.xlsm'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

I had a problem in the past when trying to open/download an Excel workbook containing macros with a file extension of xlsm. Please save your workbook as an xlsx file, and, try posting again.


Or, you can post your workbook as an xlsx file, to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

Thank you very much Mr. Stan for sharing my thread and trying help
Here's xlsx version

YasserKhalil,

When I attempted to open/download your latest workbook in your reply #11, I received the following message:

Excel found unreadable content in 'Copy Temp Sheets.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.


Try posting your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

Here's the file again
https://www.dropbox.com/s/e1uyiy3hri...eets.xlsx?dl=0

YasserKhalil,

Same message again?????

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

Last attempt try this xls version

Originally Posted by YasserKhalil

Last attempt try this xls version

YasserKhalil,

I was able to open the new workbook, Copy Temp Sheets.xls

But, I do not have a clue as to what I should do with Sheet1.

And, there are two hidden worksheets, Temp A, and, Temp B?????

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

Mr. Stan..
Did you try downloading the xls version or the same problem with you?
I advise you to change office 2007 and use 2013 or 2016 version

Thanks Mr. Stan
As clue for solution ..
We can loop through range("Q2:Q8") and for every loop copy the two temp sheets and rename them based on cell value (Q2 in first loop) adding the suffix A for first temp sheet (COM 1A in first loop) and adding the suffix B for the second temp sheet(COM 1B in first loop) ..
Then Copy range(A2:C22") to COM 1A >> (the rows would be based on range(R2:S2") for first loop)
repeat the last step but copy range("I2:K22") to COM 2B >> (the rows would be based on range(R2:S2") for first loop)

Originally Posted by YasserKhalil

Thanks Mr. Stan
As clue for solution ..
We can loop through range("Q2:Q8") and for every loop copy the two temp sheets and rename them based on cell value (Q2 in first loop) adding the suffix A for first temp sheet (COM 1A in first loop) and adding the suffix B for the second temp sheet(COM 1B in first loop) ..
Then Copy range(A2:C22") to COM 1A >> (the rows would be based on range(R2:S2") for first loop)
repeat the last step but copy range("I2:K22") to COM 2B >> (the rows would be based on range(R2:S2") for first loop)

YasserKhalil,

I do not understand why the two worksheets are hidden.

And, I do not understand what you are trying to do.

One last try:

Please manually complete worksheet Sheet1 with its results from the two hidden worksheets, TempA, and, TempB.

Then repost the workbook, worksheets, again.

Thanks again Mr. Stan
Simply I need to copy temp sheets (both of them) number of times according to the range Q2:Q9
I attached two copied sheets for first instance (Q2) COM 1A & COM 1B and show you how the results should be ..

Finally I should have 16 sheets (based on the two temp sheets) 8 sheets for TempA and 8 sheets for TempB
and for every sheet of the 16 specific range to be copied according to columns R:S which have the start row and end row for each range to copy

YasserKhalil,

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

BUMP .. Please help

Thanks a lot
I could do it at last .. I was stuck a little but it is finished at last

Sub CopyTempSheets()
    Dim Ws As Worksheet, I As Integer, startRow As Integer, endRow As Integer
    Set Ws = Sheets("Sheet1")
    
    Application.ScreenUpdating = False
        With Ws
            For I = 2 To .Cells(Rows.Count, "Q").End(xlUp).Row
                startRow = .Range("R" & I).Value
                endRow = .Range("S" & I).Value
                
                Sheets("TempA").Copy After:=Sheets(Sheets.Count)
                ActiveSheet.Name = .Range("Q" & I).Value & "A"
                ActiveSheet.Range("A3").Value = .Range("Q" & I).Value
                .Range("A" & startRow & ":C" & endRow).Copy
                ActiveSheet.Range("A5").PasteSpecial xlPasteValues
                With ActiveSheet.Range("A4:L" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row)
                    .Borders.Weight = xlThin
                    .BorderAround Weight:=xlThin
                End With
                
                Sheets("TempB").Copy After:=Sheets(Sheets.Count)
                ActiveSheet.Name = .Range("Q" & I).Value & "B"
                ActiveSheet.Range("A3").Value = .Range("Q" & I).Value
                .Range("I" & startRow & ":K" & endRow).Copy
                ActiveSheet.Range("A5").PasteSpecial xlPasteValues
                With ActiveSheet.Range("A4:L" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row)
                    .Borders.Weight = xlThin
                    .BorderAround Weight:=xlThin
                End With
            Next I
        End With
        
        Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

I could do it at last .. I was stuck a little but it is finished at last

YasserKhalil,

Nicely done.