Need help to copy certain cells based on a single cell range

[jkpalmer]

Hi,

I have been searching for the past week, and can't find anything to help me out. I have a report that displays usage data for groups from the previous month and I want to export that data from the workbook that it is stored in to an existing workbook and existing sheets based on the number assigned to that group. I currently a different macro to condense the report from my system down to something that looks like this and is named "Groups":

[TABLE="width: 365"]
<TBODY>[TR]
[TD="align: right"]May-2012</SPAN>[/TD]
[TD]Number</SPAN>[/TD]
[TD]Name</SPAN>[/TD]
[TD]Total Calls</SPAN>[/TD]
[TD]Total Duration</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]1536</SPAN>[/TD]
[TD]AAAA</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]00:00:00.00</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]1601</SPAN>[/TD]
[TD]BBBB</SPAN>[/TD]
[TD]24,381</SPAN>[/TD]
[TD]17:31:15.65</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]1655</SPAN>[/TD]
[TD]CCCC</SPAN>[/TD]
[TD]12</SPAN>[/TD]
[TD]00:00:29.02</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]1660</SPAN>[/TD]
[TD]DDDD</SPAN>[/TD]
[TD]52,402</SPAN>[/TD]
[TD]48:07:47.31</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]1663</SPAN>[/TD]
[TD]EEEE</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]00:00:00.71</SPAN>[/TD]
[/TR]
[TR]
[TD]Group</SPAN>[/TD]
[TD]1695</SPAN>[/TD]
[TD]FFFF</SPAN>[/TD]
[TD]7,325</SPAN>[/TD]
[TD]07:07:21.30</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]


The number of lines on this report can change month by month, but the number in column B will always be between 1536 and 1695. I want to copy the calls and duration in the rows based on column B to an existing Workbook and into existing worksheet's that are named based on column B. The existing Workbook is named "Groups Sorted by Month" (I will add the path later) and contains enough sheets to cover everything between and including 1536 and 1695. These sheets are already named with only the 4 digit number that will appear in column B. In a simple form, I want a macro that can search column B to find the group number and then copy the month, total calls, and total duration to the existing workbook, find the sheet named the same as the number in column B, and then past that information in the next available row; month into column A, total calls into column B, and total duration into column C. I already have header information in all of these sheets, hence wanting it copied into the next available row. My goal is to be able to track group usage month by month to see trends in usage.

Thanks in advance for your help!

 

[JLGWhiz]

This is untested. I used what you had in the OP and put this together on the assumption that all workbooks will be open when the macro runs.
It did not give me a compile error, so that part was tested. Try it on a copy of your files or a mockup and post back if there is a problem.

Code:
Sub CopyToMaster()
Dim sh As Worksheet, lr As Long, rng As Range, wb As Workbook
Dim sName As String, dLr As Long
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 2).End(xlUp).Row
Set rng = sh.Range("B2:B" & lr)
Set wb = Workbooks("Group.xlsm") 'Edit file extension
mo = Format(sh.Range("A1").Value, "mmmm")
For Each c In rng
sName = c.Valuie
dLr = wb.Sheets(sName).Cells(Rows.Count, 1).End(xlUp).Row
wb.Sheets(sName).Range("A" & dLr) = mo
sh.Range("D" & c.Row & ":E" & c.Row).Copy wb.Sheets(xName).Range("B" & dLr)
Next
End Sub
Code:

 

[jkpalmer]

JLG,

I tried running the macro after changing the sheet name and destination workbook, but it didn't work. I made sure that both the source workbook and the destination workbook was open. When I ran the debug tool, it hit on the last line:

sh.Range("D" & c.Row & ":E" & c.Row).Copy wb.Sheets(xName).Range("B" & dLr)

and gave me a Run-time error '9': Subscript out of range. Since I am not sure what all that line is supposed to do, I can't figure out what it is sticking on. Thanks again for your help.

 

[JLGWhiz]

JLG,

I tried running the macro after changing the sheet name and destination workbook, but it didn't work. I made sure that both the source workbook and the destination workbook was open. When I ran the debug tool, it hit on the last line:

sh.Range("D" & c.Row & ":E" & c.Row).Copy wb.Sheets(xName).Range("B" & dLr)

and gave me a Run-time error '9': Subscript out of range. Since I am not sure what all that line is supposed to do, I can't figure out what it is sticking on. Thanks again for your help.

I thought I fixed that typo. Change it to:

Code:
sh.Range("D" & c.Row & ":E" & c.Row).Copy wb.Sheets(sName).Range("B" & dLr)
Code:

I have a bad habit of transposing the x and s.

 

[jkpalmer]

That fixed the problem with it running.

Not sure if I mentioned this, but in the destination sheet, I was looking for the macro to select the next open row in the sheet, right now it pastes everything into row 2. I want to be able to run this macro each month and have the destination workbook show me the data for the past months as well as the current month. Right now it writes everything into row 2, so I loose the data from the previous month. Other than that, it works great! I am also impressed on how fast it sorts through the data. Thanks for all the help!