Need to create a worksheet dynamically

April 3, 2012 at 01:29:58
Specs: Windows XP
Hi ,
I dont have much idea on writing Macros.

I need to write a macro which would generate a new sheet dynamically which would copy the data from other dynamic sheet. The positions of the data to be copied from those dynamic sheets also not predetermined.

Here is the list of requirement more clearly:-
i) We need to create a new sheet name SWAP
ii) The data in this new sheet SWAP needs to be copied from other dynamic sheet DYNA_X,DYNA_Y and DYNA_Z.
iii) It is NOT necessary that all the three sheets(DYNA_X,DYNA_Y,DYNA_Z) would get generated . It is possible that only one/two/three of the three sheets (DYNA_X,DYNA_Y,DYNA_Z) would get generated .
iv) The data to be copied to the sheet SWAP from the three sheets (DYNA_X,DYNA_Y,DYNA_Z) should just picked from the keyword EARTH in those three sheets(DYNA_X,DYNA_Y,DYNA_Z). It means suppose (DYNA_X,DYNA_Y,DYNA_Z) contains keyword EARTH at row number 35, then SWAP should copy all the rows below the row containing the keyword EARTH.
v) So , the new sheet SWAP should contain data from (DYNA_X,DYNA_Y,DYNA_Z) in a union all fashion based on the search for the keyword EARTH in those sheets.

I would be very gratefull if someone can give me the code with description. I dont have much idea on Macros


See More: Need to create a worksheet dynamically

Report •


#1
April 3, 2012 at 07:44:33
This code makes a few assumptions:

1 - SWAP sheet does not exist.
2 - The keyord "EARTH" will be found in Column A
3 - There is data in Column A below the keyword "EARTH", extending down to last row that is to be copied.

I suggest that you try this code in a backup copy of your workbook since macros can not easily be undone.

Sub SwapSheet()
'Add SWAP sheet as last sheet
  Sheets.Add after:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Name = "SWAP"
'Set sht_Name variable
   For sht = 1 To 3
    If sht = 1 Then sht_Name = "DYNA_X"
    If sht = 2 Then sht_Name = "DYNA_Y"
    If sht = 3 Then sht_Name = "DYNA_Z"
'Prepare for error if sheet not found
      On Error Resume Next
'Set Search Range to Column A
       With Sheets(sht_Name).Columns(1)
'Skip if sheet not found
        If Err <> 0 Then GoTo noSht
'Find Earth in Column A
         Set e = .Find("EARTH")
'If found, copy all rows below it to SWAP aheet
          If Not e Is Nothing Then
'Determine Row where Earth was found, plus 1
            srcRw = e.Row + 1
'Determine last row with data on current sheet
            lastRw = Sheets(sht_Name).Range("A" & Rows.Count).End(xlUp).Row
'Determine next empty Row on SWAP sheet
            dstRw = Sheets("SWAP").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy rows to SWAP sheet
               Sheets(sht_Name).Rows(srcRw & ":" & lastRw).Copy _
                 Destination:=Sheets("SWAP").Range("A" & dstRw)
          End If
       End With
'Label to allow sheets to be skipped if they don't exist
noSht:
   Next
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 3, 2012 at 22:03:56
Hi Derby,
Thanks a lot for your response.
Few clarification on the assumptions :-
1) SWAP sheet does exist.
2) The source sheet from where data below the keyword EARTH needs to be pulled are dynamic. Those sheet names are also dynamic i.e. DYNA_ in DYNA_X,DYNA_Y,DYNA_Z will be constant in the names but X,Y,Z are dynamically given to the sheet for the count of the DYNA_% sheets are not constant.
3) Once the keyword EARTH is found, the data below the keyword EARTH(not only the column A where EARTH has been found but the entire rows with all the columns) needs to be populated. NOTE:- the format of headers(column names) below EARTH should also be copied .

Report •

#3
April 4, 2012 at 08:14:13
re: "1) SWAP sheet does exist."

Then why did you say this in your original post?

i) We need to create a new sheet name SWAP

re: "2)...but X,Y,Z are dynamically given to the sheet for the count of the DYNA_% sheets are not constant."

This appears to be a repeat of what you said earlier: It is NOT necessary that all the three sheets(DYNA_X,DYNA_Y,DYNA_Z) would get generated . It is possible that only one/two/three of the three sheets (DYNA_X,DYNA_Y,DYNA_Z) would get generated

This issue has been dealt with in the code via the error handling routine. If any of those three sheets doesn't exist, the code simply skips the "Copy/Paste" instructions for that sheet.

re: "3) Once the keyword EARTH is found, the data below the keyword EARTH(not only the column A where EARTH has been found but the entire rows with all the columns)"

Note the use of this instruction. It copies the rows as requested.

Sheets(sht_Name).Rows(srcRw & ":" & lastRw).Copy 

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question