Search, Copy and Paste to Specific Worksheet

Excel Excel 2007
April 16, 2010 at 20:59:58
Specs: Windows XP
Hello,

I'm new to the boards and have searched the forum to find the specific code I need to solve my particular issue. A few solutions I've pulled from here and I can't quite get them to work for me.

I have five worksheets all formatted the same with the column headings starting on Row 4 and Colum B.

I need a macro that I attach to a command button on the main worksheet that will search through column D and based on the value in Column D copy and past Cells b-g to the appropriate worksheet.

Example.

Check Register Worksheet (Main sheet with command button)
Contains Columns
Number
Date
Description of Transaction
C
Debit
Credit

Additional Worksheets have the same format.

When Description of Transaction is equal to Car Payment then each Car Payment Transations recorded in the Check Register should be copied to the Car Payment worksheet (however only the values of cells B thru G of each transaction should be copied. If Description of Transaction is equal to Mortgage then all Mortgage entries cells B thru G should be copied to the Mortgage worksheet and so on...

The idea is that when I make an entry in the main check register and click the Update Balances command buttion all the entries for that specific category are copied to their existing worksheet with the same name.

Data in Check Register starts on B6 and cells being copied over should be copied starting at cell B6 and so forth until all the common entries have been made.

Any help you all could give me would be wonderful! Thanks in advance for your help.


See More: Search, Copy and Paste to Specific Worksheet

Report •


#1
April 17, 2010 at 08:14:35
re: The idea is that when I make an entry in the main check register and click the Update Balances command button all the entries for that specific category are copied to their existing worksheet with the same name.

That request is fine for the initial set-up of each category sheet, but I don't think you want to copy all the entries every time you make a change.

I assume you only want to add the latest entries.

The code below will copy all of your existing entries to each sheet, but you need to let us know how you to handle subsequent updates.

Do you want to click the button after each change (easy to code) or do you want to click the bottom after a series of changes (doable, but a little more difficult since the code will need a way to determine how many new entries you just made.)

The other option is to have the code run automatically after you make a change to a specific column, such as the "Credit" column, but you would need to make sure that that column is the last one you change so that it copies all of the data (B:G) for that entry.

Let us know what you have in mind.

Note: The name of your category Sheets has to match the text in Column D of your Register sheet exactly for this to work.

Make sure you try this code in a back-up copy of your workbook in case something goes terribly wrong. Macros can not be undone!

Option Explicit
Sub UpdateTotals()
Dim catSht As String
Dim myTrans, lastTrans, transRow As Integer
'Find last Transaction in Register sheet, Column D
  lastTrans = Sheets("Register").Range("D" & Rows.Count).End(xlUp).Row
'Loop through Column D, copying B:G to the appropriate category sheet
  For myTrans = 6 To lastTrans
'Determine Category Sheet name
   catSht = Sheets("Register").Range("D" & myTrans).Value
'Find last row in Category Sheet
    transRow = Sheets(catSht).Range("D" & Rows.Count).End(xlUp).Row + 1
'Skip Row 5
     If transRow = 5 Then transRow = 6
'Copy line to Category Sheet
      Sheets("Register").Range("B" & myTrans & ":G" & myTrans).Copy _
        Destination:=Sheets(catSht).Range("B" & transRow)
  Next
End Sub


Report •

#2
April 17, 2010 at 11:12:38
DerbyDad,

Thanks so much for the response. As to your question regarding how I wanted to handle updates that is a little trickier. I would like to click the button after all the entries have been made so my thought was to just recopy all the existing entries each time. In this way if a change was made in one of the previous entries the latest balance would calculate correctly.

There won't always be a credit so I would rather just have the entries pasted to the appropriate worksheet based on the values in Column D which will always match exactly to the spreadsheet name created.

The other piece to this is that these are payments being tracked by individuals. Each individual will have their own tab and the main check register worksheet will track all payments made by each individual. (i.e Rob makes a payment and so does John on the same day. These entries will be entered on the check register and when I click on the update balances button I can go to Rob's spreadsheet and see all his previous entries as well as the latest one made. If i go to John's tab I would see the same thing specific to John).

I do have a test copy that has the macro I created that isn't working. I will update that one with the code you provided and see where that gets me while I await your response on the above.

Thanks again!


Report •

#3
April 17, 2010 at 11:22:59
Tried the supplied code and this is great. In addition to handling the updates how do I paste the values only so the format of the columns in the tabs reciving the date don't change?

Thanks so much!


Report •

Related Solutions

#4
April 17, 2010 at 12:44:30
Ok two issues I've discovered that I'm not sure how to solve with the current code supplied. I have to add rows in the Register sheet only so my data now starts on Row 8. I can't simply insert rows the macro stops working. How can I fix this? Also If i add a new sheet to account for a different type of entry I also get an error. How do I resolve this?

Both errors result in a subscript out of range error. Any ideas?

Thanks.


Report •

#5
April 17, 2010 at 14:21:03
The Subscript Out Of Range error is most likely caused by the fact that the code is looking for a sheet named "whatever is in the rows you added above Row 8". In other words, if you put something in D6 or D7 of the Register sheet and there isn't a sheet with that name, you will get a Subscript Out Of Range error. That's because the code is trying to copy everything from Row 6 and below, based on your original requirements.

Adding a new category in Column D and adding a new sheet with the same name should not produce that error, nor should adding a new sheet, in and of itself.

re: "The other piece to this is that these are payments being tracked by individuals. Each individual will have their own tab and the main check register worksheet will track all payments made by each individual."

This doesn't make sense to me. You originally said:

When Description of Transaction is equal to Car Payment then each Car Payment Transations recorded in the Check Register should be copied to the Car Payment worksheet

Now you are saying that "when I click on the update balances button I can go to Rob's spreadsheet and see all his previous entries as well as the latest one made"

Are you asking that the data be copied to the category sheets as well as another sheet named for the individual who made the entry?


Report •

#6
April 17, 2010 at 15:32:53
Hi DerbyDad,

Thanks for the clarification. I made the modification and now the out of range error is gone and it's working as needed. I apologize for the confusion of my previous statements. I was actually attempting to say the same thing. Instead of using the Car Payment or Mortgage I used names as that's what is really being tracked are account payments by individuals.

Are you asking that the data be copied to the category sheets as well as another sheet named for the individual who made the entry?

No. What I'm saying is that the category sheet and the sheet named for the invidiual IS the same sheet. Basically, I'm not really tracking car payments etc...but individuals who are paying but the easiset way to describe what I needed was to use categories such as car payment etc. Essentially the code you provided is working EXACTLY as I need it to.

The only missing piece is how to include the updates which is why I used the example of "when I click on the update balances button I can go to Rob's spreadsheet and see all his previous entries as well as the latest one made" My point is I want to see the updates as well as any changes to the previous entries on the category (or individuals named sheet) That's exactly why I do I do want to copy all the entries every time I make a change or add an entry.

Sorry for all the confusion. I really appreciate your help please let me know if you need further clarification or I can send sample data if that will help.

Thanks!


Report •

#7
April 17, 2010 at 17:29:15
I guess the easist way to handle the updates is to just clear all of the data from the "category" sheets and then copy everything back in.

I making the assuming that your Register sheet is the first sheet and therefore clearing everything in B6:G<LastRow> of Sheets 2 through the last sheet.

Since you changed the "6" to be something else, make sure you make the same changes to this code.

The same caution as far as running this in a backup workbook stills applies.

Sub UpdateTotals()
Dim catSht As String
Dim myTrans, lastTrans, transRow, clrSht, lstRow As Integer

'''New section of code begins

'Loop through sheets clearing old transactions
  For clrSht = 2 To Sheets.Count
'Find last row with transaction
   lstRow = Sheets(clrSht).Range("D" & Rows.Count).End(xlUp).Row
'Clear the range
   Sheets(clrSht).Range("B6:G" & lstRow).ClearContents
  Next

'''New section of code ends

'Find last Transaction in Register sheet, Column D
  lastTrans = Sheets("Register").Range("D" & Rows.Count).End(xlUp).Row
'Loop through Column D, copying B:G to the appropriate category sheet
  For myTrans = 6 To lastTrans
'Determine Category Sheet name
   catSht = Sheets("Register").Range("D" & myTrans).Value
'Find last row in Category Sheet
    transRow = Sheets(catSht).Range("D" & Rows.Count).End(xlUp).Row + 1
'Skip Row 5
     If transRow = 5 Then transRow = 6
'Copy line to Category Sheet
      Sheets("Register").Range("B" & myTrans & ":G" & myTrans).Copy _
        Destination:=Sheets(catSht).Range("B" & transRow)
  Next
End Sub


Report •

#8
April 19, 2010 at 09:30:42
This is exactly what I was looking for. One final question. How do I copy just the values over so the formats don't change. I've tried a few variations of the PasteSpecial Command but I keep getting this error: "Unable to get the PasteSpecial peroperty of the Range class"

Here is the modified code I'm using

Option Explicit
Sub UpdateTotals()
Dim catSht As String
Dim myTrans, lastTrans, transRow, clrSht, lstRow As Integer

'New section of code begins

'Loop through sheets clearing old transactions
For clrSht = 2 To Sheets.Count
'Find last row with transaction
lstRow = Sheets(clrSht).Range("D" & Rows.Count).End(xlUp).Row
'Clear the range
Sheets(clrSht).Range("B6:G" & lstRow).ClearContents
Next

'''New section of code ends

'Find last Transaction in Register sheet, Column D
lastTrans = Sheets("Check Register").Range("D" & Rows.Count).End(xlUp).Row
'Loop through Column D, copying B:G to the appropriate category sheet
For myTrans = 8 To lastTrans
'Determine Category Sheet name
catSht = Sheets("Check Register").Range("D" & myTrans).Value
'Find last row in Category Sheet
transRow = Sheets(catSht).Range("D" & Rows.Count).End(xlUp).Row + 1
'Skip Row 5
If transRow = 5 Then transRow = 6
'Copy line to Category Sheet
Sheets("Check Register").Range("B" & myTrans & ":G" & myTrans).Copy _
Destination:=Sheets(catSht).Range("B" & transRow).PasteSpecial(xlPasteValues)

Next
End Sub

Thanks


Report •

#9
April 19, 2010 at 10:42:43
Please try to use the pre tags found above the comments box when posting code or examples of data. The pre tags allow you to line up your code and data to make it easier to read in the forum. Look at the code in my responses compared to the code in your's.

As for your question, I assume you mean that you do not want the formats on the Destination sheet to change.

When you want to use "PasteSpecial" you can't use the Continuation character (_) and "Destination:=" you have to use separate lines for the Copy and the PasteSpecial operations.

Try this:

Sheets("Check Register").Range("B" & myTrans & ":G" & myTrans).Copy
Sheets(catSht).Range("B" & transRow).PasteSpecial (xlPasteValues)


Report •

#10
April 19, 2010 at 12:07:31
Thanks for the tip on posting code. I didn't realize that was what that tag was for.

You were correct I didn't want the format for the destination sheets to change and the code you provided is exactly what I was looking for.

Thank you so much!


Report •

Ask Question