PDA

View Full Version : Solved: Moving Data from one sheet to another based on a value in one cell.



Jarlisle
11-05-2008, 04:50 PM
I need to find out how I can enter data into a worksheet and then at the end of the day move it to a sheet in another document according to a value in one of the cells.

For example: I have a document where I enter workshop registrants into multiple sheets based on who registered the customer. We keep track of these on a daily basis and might change to a weekly basis. I want to be able to then copy and paste these registrant records into another document and use it as a roster based on the week that the event is happening.

Is there a way I can take the sheet and perform a macro that will sort the data according to the week of the event and paste it to the correct sheet in another document?

I have attached an example of the data I want sorted out according to the week number of the event.

MaximS
11-05-2008, 10:16 PM
Try this:


Private Sub Data_Sorting()

Dim LastRow, i, j, Lrow As Long
LastRow = Range("N" & Rows.Count).End(xlUp).Row

If LastRow > 1 Then

Workbooks.Open Filename:= _
"C:\YourFolder\file2.xls"

For i = 2 To LastRow

j = Cells(i, 14).Value

Range("A" & i & ":M" & i).Copy

'file2.xls is destination file
Windows("file2.xls").Activate

Sheets("Week " & j).Select

Lrow = Range("A" & Rows.Count).End(xlUp).Row

Range("A" & Lrow + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

'file1.xls is source file
Windows("file1.xls").Activate

Next i

End If

End Sub

mdmackillop
11-06-2008, 12:08 PM
Hi Maxim,
You need to go back to File1 to get the copy data.
In your Dim line, only Lrow is being dimmed as long, the rest are Variant. You need to be specific for each item, lists don't work.

Private Sub Data_Sorting()

Dim LastRow, i, j, Lrow As Long
LastRow = Range("N" & Rows.Count).End(xlUp).Row
If LastRow > 1 Then
Workbooks.Open Filename:= _
"C:\YourFolder\file2.xls"
For i = 2 To LastRow
Windows("file1.xls").Activate
j = Cells(i, 14).Value
Range("A" & i & ":M" & i).Copy
'file2.xls is destination file
Windows("file2.xls").Activate
Sheets("Week " & j).Select
Lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Lrow + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'file1.xls is source file
Next i
End If
End Sub


Using references for the Source and Target sheets saves on switching beteen them, so simplifies the code and lets it run faster

Option Explicit
Private Sub Data_Sorting()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim WB As Workbook
Dim Rng As Range, cel As Range

'Set source
Set wsSource = ActiveSheet
Set Rng = Range(wsSource.Cells(2, 1), wsSource.Cells(Rows.Count, 1).End(xlUp))
'Open target workbook
On Error Resume Next
Set WB = Workbooks("file2.xls")
If WB Is Nothing Then
Set WB = Workbooks.Open(Filename:="C:\YourFolder\file2.xls")
End If
'Copy data
For Each cel In Rng
cel.Resize(, 13).Copy _
WB.Sheets("Week " & cel.Offset(, 13)).Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next
End Sub

Jarlisle
11-06-2008, 01:40 PM
This worked really well, thanks!

I do have another problem that I didn't forsee when I posted this and that is what happens when I have a blank row in between some rows of data. (This probably should not happen, but it does.)

What happens is I have a Weeknum() formula in the week number column and if I use IFERROR to make it blank it errors because I don't have a "Week " sheet in the destination file. How do I skip the blank row and look at other rows to make sure they aren't blank?

mdmackillop
11-06-2008, 01:50 PM
You can test the Week column (or any other) before copying

'Copy data
For Each cel In Rng
If cel.Offset(, 13) <> "" Then
cel.Resize(, 14).Copy _
WB.Sheets("Week " & cel.Offset(, 13)).Cells(Rows.Count, 1).End(xlUp).Offset(1)
End If
Next

Jarlisle
11-06-2008, 02:27 PM
I couldn't get mdmackillop's code to work. It didn't do anything, that I could see. I did however get MaximS' code to work with a few tweaks to adjust for some other things that I didn't realize would happen.

Thanks for your help guys!

If I have other issues I will repost.

msbharani
11-10-2008, 11:27 AM
I need to find out how I can enter data into a worksheet and then at the end of the day move it to a sheet in another document according to a value in one of the cells.

For example: I have a document where I enter workshop registrants into multiple sheets based on who registered the customer. We keep track of these on a daily basis and might change to a weekly basis. I want to be able to then copy and paste these registrant records into another document and use it as a roster based on the week that the event is happening.

Is there a way I can take the sheet and perform a macro that will sort the data according to the week of the event and paste it to the correct sheet in another document?

I have attached an example of the data I want sorted out according to the week number of the event.