Merging worksheets with VBA Code -- HELP!

G

Guest

Hello I need help with using a VBA code to merge data from a range of
worksheets in a workbook. I have several worksheets that are formatted the
same except they may have a different amount of rows of data on each
spreadsheet and they are named for each of my employees. When I run the
Macro, it is giving me all of the details, columns and rows from the 1st
sheet only, Sonia G then it only gives me the information in colums A, B, C,
E, F, G, I from the other work sheets. However, all of the worksheets have
information in columns A thru AA. I used the following code for a Summary
Sheet by Bernie D...

Sub UpdateSummarySheet()
Dim mySht As Worksheet
Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("Sonia G")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A1")
End With

For Each mySht In Sheets(Array("Lisa P", _
"Javier B", "Priscilla C", "Michelle T"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
Next mySht
End Sub

I have used several codes today and the result is still the same. Can
someone please help! Any help is appreciated.

Thanks!
 
J

Jim Rech

Your code seemed to run okay for me...

I'd suggest that you get rid of the On Error Resume Next since it only
serves to hide problems from you (if there are any). Also I think you want
the line near the end to be:

Worksheets("Summary Sheet").Range("A65536").End(xlUp).Offset(2)

so you get a space between the blocks.

--
Jim Rech
Excel MVP
| Hello I need help with using a VBA code to merge data from a range of
| worksheets in a workbook. I have several worksheets that are formatted
the
| same except they may have a different amount of rows of data on each
| spreadsheet and they are named for each of my employees. When I run the
| Macro, it is giving me all of the details, columns and rows from the 1st
| sheet only, Sonia G then it only gives me the information in colums A, B,
C,
| E, F, G, I from the other work sheets. However, all of the worksheets
have
| information in columns A thru AA. I used the following code for a Summary
| Sheet by Bernie D...
|
| Sub UpdateSummarySheet()
| Dim mySht As Worksheet
| Dim myRange As Range
|
| On Error Resume Next
| Application.DisplayAlerts = False
| Worksheets("Summary Sheet").Delete
| Worksheets.Add.Name = "Summary Sheet"
| Application.DisplayAlerts = True
|
| 'Data transfer summarization
| With Sheets("Sonia G")
| Set myRange = .Range("A1").CurrentRegion
| myRange.Resize(myRange.Rows.Count - 1, _
| myRange.Columns.Count).Copy _
| Worksheets("Summary Sheet").Range("A1")
| End With
|
| For Each mySht In Sheets(Array("Lisa P", _
| "Javier B", "Priscilla C", "Michelle T"))
| Set myRange = mySht.Range("A1").CurrentRegion
| myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
| myRange.Columns.Count).Copy _
| Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
| Next mySht
| End Sub
|
| I have used several codes today and the result is still the same. Can
| someone please help! Any help is appreciated.
|
| Thanks!
|
|
|
|
|
 
G

Guest

Jim---

Thank you so much for the help. I got it to work .... There were columns
hidden on some of the spreadsheets so that was throwing everything out of
wack. Thanks for the suggestion regarding the break between blocks but this
report doesn't require it.

Now I have another problem --- I copied the code for another workbook that
has the same format. I changed the names to match the worksheets, etc...but
when I run the code it doesn't bring over data from all the sheets, it is
only bringing data from the first sheet and a few others. This is the code.
Any ideas??

Sub UpdateSummarySheet()
Dim mySht As Worksheet
Dim myRange As Range

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("Cristina R")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A1")
End With

For Each mySht In Sheets(Array("Brenda P", _
"Estella B", "Isabel F", "Lydia O", "Melanie W", "Melody B", "Nicole R",
"Pat R", "Selia G", "Sonia M", "Sonya C", "Solheia M", "Sue H",
"Vanessa M", "Velma T", "Vivian P"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)

Next mySht
End Sub

Thanks for helping!
 
G

Guest

Jim thank you so much for your help. There were columns hidden on some of
the spreadsheets so it was throwing the whole thing out of wack when I would
run the macro. I fixed it and it is perfect. Thanks for the suggestion on
seperating blocks but it's not required on this report.

Now I have another problem. I used the same code for another report that I
have to do for a different team, however the set-up, columns, rows are the
same. The only thing different is the worksheet names. I adjusted the code
to compensate for that, but now when I run the macro it is only giving me
data from some of the sheets and not all. I have attached the code below. I
removed ther "error resume next" and it highlights the last three rows of the
code under the array part. Any ideas??

Sub UpdateSummarySheet()
Dim mySht As Worksheet
Dim myRange As Range



Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

'Data transfer summarization
With Sheets("Cristina R")
Set myRange = .Range("A1").CurrentRegion
myRange.Resize(myRange.Rows.Count - 1, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A1")
End With

For Each mySht In Sheets(Array("Brenda P", _
"Estella B", "Isabel F", "Lydia O", "Melanie W", "Melody B", "Nicole R",
"Pat R", "Selia G", "Sonia M", "Sonya C", "Solheia M", "Sue H", "Vanessa M",
"Velma T", "Vivian P"))
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)

Next mySht
End Sub
 
J

Jim Rech

May I be so bold as to suggest an improvement?<g> If (big IF) it's true
that all the employee sheets names end in a space and a character, and no
other sheets end this way, then this macro could be used with any workbook
since it does not require the names of the sheets to be in the macro.

Sub UpdateSummarySheet()
Dim mySht As Worksheet
Dim myRange As Range
Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True
For Each mySht In Worksheets
If Right(mySht.Name, 2) Like " ?" Then
Set myRange = mySht.Range("A1").CurrentRegion
myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
myRange.Columns.Count).Copy _
Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
End If
Next
Rows(1).Delete
End Sub

--
Jim Rech
Excel MVP
| Jim thank you so much for your help. There were columns hidden on some of
| the spreadsheets so it was throwing the whole thing out of wack when I
would
| run the macro. I fixed it and it is perfect. Thanks for the suggestion
on
| seperating blocks but it's not required on this report.
|
| Now I have another problem. I used the same code for another report that
I
| have to do for a different team, however the set-up, columns, rows are the
| same. The only thing different is the worksheet names. I adjusted the
code
| to compensate for that, but now when I run the macro it is only giving me
| data from some of the sheets and not all. I have attached the code below.
I
| removed ther "error resume next" and it highlights the last three rows of
the
| code under the array part. Any ideas??
|
| Sub UpdateSummarySheet()
| Dim mySht As Worksheet
| Dim myRange As Range
|
|
|
| Application.DisplayAlerts = False
| Worksheets("Summary Sheet").Delete
| Worksheets.Add.Name = "Summary Sheet"
| Application.DisplayAlerts = True
|
| 'Data transfer summarization
| With Sheets("Cristina R")
| Set myRange = .Range("A1").CurrentRegion
| myRange.Resize(myRange.Rows.Count - 1, _
| myRange.Columns.Count).Copy _
| Worksheets("Summary Sheet").Range("A1")
| End With
|
| For Each mySht In Sheets(Array("Brenda P", _
| "Estella B", "Isabel F", "Lydia O", "Melanie W", "Melody B", "Nicole
R",
| "Pat R", "Selia G", "Sonia M", "Sonya C", "Solheia M", "Sue H", "Vanessa
M",
| "Velma T", "Vivian P"))
| Set myRange = mySht.Range("A1").CurrentRegion
| myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
| myRange.Columns.Count).Copy _
| Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
|
| Next mySht
| End Sub
|
|
|
|
|
| "Jim Rech" wrote:
|
| > Your code seemed to run okay for me...
| >
| > I'd suggest that you get rid of the On Error Resume Next since it only
| > serves to hide problems from you (if there are any). Also I think you
want
| > the line near the end to be:
| >
| > Worksheets("Summary Sheet").Range("A65536").End(xlUp).Offset(2)
| >
| > so you get a space between the blocks.
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > | Hello I need help with using a VBA code to merge data from a range of
| > | worksheets in a workbook. I have several worksheets that are
formatted
| > the
| > | same except they may have a different amount of rows of data on each
| > | spreadsheet and they are named for each of my employees. When I run
the
| > | Macro, it is giving me all of the details, columns and rows from the
1st
| > | sheet only, Sonia G then it only gives me the information in colums A,
B,
| > C,
| > | E, F, G, I from the other work sheets. However, all of the worksheets
| > have
| > | information in columns A thru AA. I used the following code for a
Summary
| > | Sheet by Bernie D...
| > |
| > | Sub UpdateSummarySheet()
| > | Dim mySht As Worksheet
| > | Dim myRange As Range
| > |
| > | On Error Resume Next
| > | Application.DisplayAlerts = False
| > | Worksheets("Summary Sheet").Delete
| > | Worksheets.Add.Name = "Summary Sheet"
| > | Application.DisplayAlerts = True
| > |
| > | 'Data transfer summarization
| > | With Sheets("Sonia G")
| > | Set myRange = .Range("A1").CurrentRegion
| > | myRange.Resize(myRange.Rows.Count - 1, _
| > | myRange.Columns.Count).Copy _
| > | Worksheets("Summary Sheet").Range("A1")
| > | End With
| > |
| > | For Each mySht In Sheets(Array("Lisa P", _
| > | "Javier B", "Priscilla C", "Michelle T"))
| > | Set myRange = mySht.Range("A1").CurrentRegion
| > | myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
| > | myRange.Columns.Count).Copy _
| > | Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
| > | Next mySht
| > | End Sub
| > |
| > | I have used several codes today and the result is still the same. Can
| > | someone please help! Any help is appreciated.
| > |
| > | Thanks!
| > |
| > |
| > |
| > |
| > |
| >
| >
| >
 
G

Guest

Ok so if it isn't obvious enough I am new to VBA. Keep the suggestions
coming -- be as bold as you want. Now I am getting a debug error on this
part of the code...

Worksheets("Summary Sheet").Delete
 
J

Jim Rech

Now I am getting a debug error

We must trap for when the summary does not exit. Overlooked that...

On Error Resume Next ''In case sheet does not exist
Worksheets("Summary Sheet").Delete
''Turn off error trapping so we know if something else is wrong
On Error Goto 0

--
Jim Rech
Excel MVP
| Ok so if it isn't obvious enough I am new to VBA. Keep the suggestions
| coming -- be as bold as you want. Now I am getting a debug error on this
| part of the code...
|
| Worksheets("Summary Sheet").Delete
|
| "Jim Rech" wrote:
|
| > May I be so bold as to suggest an improvement?<g> If (big IF) it's true
| > that all the employee sheets names end in a space and a character, and
no
| > other sheets end this way, then this macro could be used with any
workbook
| > since it does not require the names of the sheets to be in the macro.
| >
| > Sub UpdateSummarySheet()
| > Dim mySht As Worksheet
| > Dim myRange As Range
| > Application.DisplayAlerts = False
| > Worksheets("Summary Sheet").Delete
| > Worksheets.Add.Name = "Summary Sheet"
| > Application.DisplayAlerts = True
| > For Each mySht In Worksheets
| > If Right(mySht.Name, 2) Like " ?" Then
| > Set myRange = mySht.Range("A1").CurrentRegion
| > myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
| > myRange.Columns.Count).Copy _
| > Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
| > End If
| > Next
| > Rows(1).Delete
| > End Sub
| >
| > --
| > Jim Rech
| > Excel MVP
| > | > | Jim thank you so much for your help. There were columns hidden on
some of
| > | the spreadsheets so it was throwing the whole thing out of wack when I
| > would
| > | run the macro. I fixed it and it is perfect. Thanks for the
suggestion
| > on
| > | seperating blocks but it's not required on this report.
| > |
| > | Now I have another problem. I used the same code for another report
that
| > I
| > | have to do for a different team, however the set-up, columns, rows are
the
| > | same. The only thing different is the worksheet names. I adjusted
the
| > code
| > | to compensate for that, but now when I run the macro it is only giving
me
| > | data from some of the sheets and not all. I have attached the code
below.
| > I
| > | removed ther "error resume next" and it highlights the last three rows
of
| > the
| > | code under the array part. Any ideas??
| > |
| > | Sub UpdateSummarySheet()
| > | Dim mySht As Worksheet
| > | Dim myRange As Range
| > |
| > |
| > |
| > | Application.DisplayAlerts = False
| > | Worksheets("Summary Sheet").Delete
| > | Worksheets.Add.Name = "Summary Sheet"
| > | Application.DisplayAlerts = True
| > |
| > | 'Data transfer summarization
| > | With Sheets("Cristina R")
| > | Set myRange = .Range("A1").CurrentRegion
| > | myRange.Resize(myRange.Rows.Count - 1, _
| > | myRange.Columns.Count).Copy _
| > | Worksheets("Summary Sheet").Range("A1")
| > | End With
| > |
| > | For Each mySht In Sheets(Array("Brenda P", _
| > | "Estella B", "Isabel F", "Lydia O", "Melanie W", "Melody B",
"Nicole
| > R",
| > | "Pat R", "Selia G", "Sonia M", "Sonya C", "Solheia M", "Sue H",
"Vanessa
| > M",
| > | "Velma T", "Vivian P"))
| > | Set myRange = mySht.Range("A1").CurrentRegion
| > | myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
| > | myRange.Columns.Count).Copy _
| > | Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
| > |
| > | Next mySht
| > | End Sub
| > |
| > |
| > |
| > |
| > |
| > | "Jim Rech" wrote:
| > |
| > | > Your code seemed to run okay for me...
| > | >
| > | > I'd suggest that you get rid of the On Error Resume Next since it
only
| > | > serves to hide problems from you (if there are any). Also I think
you
| > want
| > | > the line near the end to be:
| > | >
| > | > Worksheets("Summary Sheet").Range("A65536").End(xlUp).Offset(2)
| > | >
| > | > so you get a space between the blocks.
| > | >
| > | > --
| > | > Jim Rech
| > | > Excel MVP
| > | > | > | > | Hello I need help with using a VBA code to merge data from a range
of
| > | > | worksheets in a workbook. I have several worksheets that are
| > formatted
| > | > the
| > | > | same except they may have a different amount of rows of data on
each
| > | > | spreadsheet and they are named for each of my employees. When I
run
| > the
| > | > | Macro, it is giving me all of the details, columns and rows from
the
| > 1st
| > | > | sheet only, Sonia G then it only gives me the information in
colums A,
| > B,
| > | > C,
| > | > | E, F, G, I from the other work sheets. However, all of the
worksheets
| > | > have
| > | > | information in columns A thru AA. I used the following code for a
| > Summary
| > | > | Sheet by Bernie D...
| > | > |
| > | > | Sub UpdateSummarySheet()
| > | > | Dim mySht As Worksheet
| > | > | Dim myRange As Range
| > | > |
| > | > | On Error Resume Next
| > | > | Application.DisplayAlerts = False
| > | > | Worksheets("Summary Sheet").Delete
| > | > | Worksheets.Add.Name = "Summary Sheet"
| > | > | Application.DisplayAlerts = True
| > | > |
| > | > | 'Data transfer summarization
| > | > | With Sheets("Sonia G")
| > | > | Set myRange = .Range("A1").CurrentRegion
| > | > | myRange.Resize(myRange.Rows.Count - 1, _
| > | > | myRange.Columns.Count).Copy _
| > | > | Worksheets("Summary Sheet").Range("A1")
| > | > | End With
| > | > |
| > | > | For Each mySht In Sheets(Array("Lisa P", _
| > | > | "Javier B", "Priscilla C", "Michelle T"))
| > | > | Set myRange = mySht.Range("A1").CurrentRegion
| > | > | myRange.Offset(1, 0).Resize(myRange.Rows.Count - 2, _
| > | > | myRange.Columns.Count).Copy _
| > | > | Worksheets("Summary Sheet").Range("A65536").End(xlUp)(2)
| > | > | Next mySht
| > | > | End Sub
| > | > |
| > | > | I have used several codes today and the result is still the same.
Can
| > | > | someone please help! Any help is appreciated.
| > | > |
| > | > | Thanks!
| > | > |
| > | > |
| > | > |
| > | > |
| > | > |
| > | >
| > | >
| > | >
| >
| >
| >
 
D

Dave Peterson

This portion tries to delete any existing sheet named "Summary Sheet", then it
creates a new worksheet with the same name:

Application.DisplayAlerts = False
Worksheets("Summary Sheet").Delete
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True

You could tell your code to ignore the error if "summary sheet" didn't exist--so
it couldn't be deleted:

Application.DisplayAlerts = False
on error resume next
Worksheets("Summary Sheet").Delete
on error goto 0
Worksheets.Add.Name = "Summary Sheet"
Application.DisplayAlerts = True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to format cells on several worksheets 2
Macro will not work - Please help 4
Lost in code 4
Help with GetFileName in code 3
Create Sheets from a Range and Copy Data on copy Data on newlycreated Sheets with the Sheet Names 2
Selection.AutoFilter Issue 1
Using VBA to Create New Sheet That Consolidates Others 1
Search , then copy and paste from multiple worksheets. VBA help 1

Top