Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ExcelVBA
I have a project. I will give the description of the project at the end. For now, suffice to say, I am running into problems with my code.
The macro shortcut keys are CTRL+Shift+N. The current problem is that the ActiveSheet.Paste command is not working. I did some research and read that this happens and that you might try re-starting the workbook. I have done that. The problem starts with the first paste command.
 
One other aspect of the project that I am unsure of is a loop. I want this to loop based upon finding any other records in the East Trial Balance worksheet that have NEW_ACCT in column F. I am not sure how to write that loop.
 
Now for the description of the project...
 
I have a workbook. There are six worksheets I want to use for this project. The first is the reference worksheet called East Trial Balance. This sheet gets populated in columns A thru D from a database. In column F is a formula that looks for a record in worksheet Tax Basis BS East for a value in column B that equals the value of column A in the East Trial Balance. If there is none, the word NEW_ACCT appears.
Using that, I want to loop through any and all records in the East Trial Balance that have NEW_ACCT in column F and I want to add the values from columns A and C to the Tax Basis BS East worksheet.
Here is the tricky part...
The record to be added must go into the section whose name matches the value of column D on the East Trial Balance. It must find the matching section in the Tax Basis BS East by searching column A. It must then move the cursor up one row and then insert a new row. This way I do not loose my formulas. Then in column A of the Tax Basis BS East, add the value from column C of the East Trial Balance and in column B of the Tax Basis BS East, add the value from column A of the East Trial Balance.
Then I need to copy the formulas that exist in columns C through N of the row above this new record to the same columns for the new record.
Not done yet...
before proceeding to the next record that is new, I want to repeat the append portion for worksheets Tax Basis BS Other, Tax Basis BS SL, Tax Basis BS VIE, and Tax Basis BS West.
 
Here is the code I have developed to date...
 
Sub Macro1()
'
' Macro will take line items from the East Trial Balance where column F has
' the text NEW_ACCT and append a record to the Tax Basis BS for each entity
' (East, Other, SL, VIE, West) and add the Account Number and Account Name
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    Dim strSection As String
    Dim intRowA As String
    Dim intRowB As String
    
    ' Go to Trial Balance and search for a New Account
    Sheets("East Trial Balance").Select
    Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ' Move from column F to column D to get the name of the Section for the New Account
    ActiveCell.Offset(0, -2).Select
    strSection = ActiveCell.Value
    
    ' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down
    Sheets("Tax Basis BS-SL").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
    ' Move from column D to column A to get the Account Number
    Sheets("East Trial Balance").Select
    ActiveCell.Offset(0, -3).Select
    Application.CutCopyMode = False
    Selection.Copy
    
    ' Move to the column B and paste the copied Account Number
    Sheets("Tax Basis BS-SL").Select
    ActiveCell.Offset(0, 1).Select
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowB).Select
    ActiveSheet.Paste
    
    ' Go back to the Trial Balance and get a copy of the Account Name from column C
    Sheets("East Trial Balance").Select
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 2).Select
    Selection.Copy
    
    ' Return to the Tax Basis BS and paste the Account Name
    Sheets("Tax Basis BS-SL").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-2, 0).Select
    intRowA = "A" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
        
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-West").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-West").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-Other").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-Other").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
    
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-VIE").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-VIE").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
 
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ' Go to the next Tax Basis BS worksheet and add the copied values to a new row in the correct section
    Sheets("Tax Basis BS-East").Select
    Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(-1, 0).Select
    intRowA = ActiveCell.Row
    intRowB = ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
 
    ' Return to the Tax Basis BS SL and determine the row you are in to create the range of column A and B of that row to copy
    Sheets("Tax Basis BS-SL").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Range(intRowA & ":" & intRowB).Select
    Selection.Copy
 
    ' Move back to the Tax Basis BS West to paste the selected values
    Sheets("Tax Basis BS-East").Select
    intRowA = "A" & ActiveCell.Row
    intRowB = "B" & ActiveCell.Row
    Application.CutCopyMode = False
    Range(intRowA & ":" & intRowB).Select
    ActiveSheet.Paste
 
    ' Go to column c at the row above and copy all the formulas in that row to the added row
    ActiveCell.Offset(-1, 2).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    intRowA = "C" & ActiveCell.Row
    Range(intRowA).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
 
End Sub
 
Again the code fails at the first ActiveSheet.Paste.
Second I need help writing the loop that will allow this code to repeat for any record in the East Trail Balance having NEW_ACCT in column F.
Posted 17-Dec-12 8:34am
Edited 17-Dec-12 8:48am
v2
Comments
rawilken at 17-Dec-12 18:24pm
   
Still have Paste method failed errors. My code is now... Sub Macro1() ' ' Macro will take line items from the East Trial Balance where column F has ' the text NEW_ACCT and append a record to the Tax Basis BS for each entity ' (East, Other, SL, VIE, West) and add the Account Number and Account Name ' then copy the formulas from the line above into the new line ' ' Keyboard Shortcut: Ctrl+Shift+N ' Dim strFound As String Dim strSection As String Dim intWorkSht As Integer strFound = Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Do While strFound <> vbNullString ' Move from column F to column D to get the name of the Section for the New Account ActiveCell.Offset(0, -2).Select strSection = ActiveCell.Value ' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down Sheets("Tax Basis BS-SL").Select Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(-1, 0).Select Range(Range("A" & ActiveCell.Row), Range("B" & ActiveCell.Row)).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' Move from column D to column A to get the Account Number Sheets("East Trial Balance").Select ActiveCell.Offset(0, -3).Select Selection.Copy ' Move to the column B and paste the copied Account Number Sheets("Tax Basis BS-SL").Select ActiveCell.Offset(0, 1).Select Range("B" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues ' Go back to the Trial Balance and get a copy of the Account Name from column C Sheets("East Trial Balance").Select ActiveCell.Offset(0, 2).Select Selection.Copy ' Return to the Tax Basis BS and paste the Account Name Sheets("Tax Basis BS-SL").Select Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(-2, 0).Select Range("A" & ActiveCell.Row).PasteSpecial Paste:=xlPasteValues ' Go to column c at the row above and copy all the formulas in that row to the added row ActiveCell.Offset(-1, 2).Select Range(Range("C" & ActiveCell.Row), Range("N" & ActiveCell.Row)).Select Selection.Copy ActiveCell.Offset(1, 0).Select Range(Range("C" & ActiveCell.Row), Range("N" & ActiveCell.Row)).PasteSpecial Paste:=xlPasteValues intWorkSht = 1 Do While intWorkSht < 5 ' Go to the next Tax Basis BS worksheet and append a new row in the correct section If intWorkSht = 1 Then Sheets("Tax Basis BS-West").Select ElseIf intWorkSht = 2 Then Sheets("Tax Basis BS-Other").Select ElseIf intWorkSht = 3 Then Sheets("Tax Basis BS-VIE").Select ElseIf intWorkSht = 4 Then Sheets("Tax Basis BS-East").Select End If Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(-1, 0).Select Range(Range("A" & ActiveCell.Row), Range("B" & ActiveCell.Row)).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' Return to the Tax Basis BS SL and copy the A

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

There could be several reasons for the behaviour. One thing you could try is to use PasteSpecial instead of Paste. Since you're pasting a range, it could work better for you. Refer to Range.PasteSpecial Method[^]
 
Another thing is that if the selection for target range is differently sized than the source range, it would cause problems.
 
Also try using the debugger to see the ranges and that the copying is done properly.
  Permalink  
Comments
rawilken at 17-Dec-12 15:37pm
   
Newly revised code. Problem still occurs. Paste fails! Need help adding Do loop to repeat for each entry of NEW_ACCT in column F of East Trail Balance. Code... Sub Macro1() ' ' Macro will take line items from the East Trial Balance where column F has ' the text NEW_ACCT and append a record to the Tax Basis BS for each entity ' (East, Other, SL, VIE, West) and add the Account Number and Account Name ' ' Keyboard Shortcut: Ctrl+Shift+N ' Dim strSection As String Dim intRowA As String Dim intRowB As String Dim intWorkSht As Integer ' Go to Trial Balance and search for a New Account Sheets("East Trial Balance").Select Cells.Find(What:="NEW_ACCT", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ' Move from column F to column D to get the name of the Section for the New Account ActiveCell.Offset(0, -2).Select strSection = ActiveCell.Value ' Move to the first Tax Basis BS worksheet, move all rows down from the row above the section name on down Sheets("Tax Basis BS-SL").Select Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(-1, 0).Select intRowA = ActiveCell.Row intRowB = ActiveCell.Row Range(intRowA & ":" & intRowB).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove ' Move from column D to column A to get the Account Number Sheets("East Trial Balance").Select ActiveCell.Offset(0, -3).Select Application.CutCopyMode = False Selection.Copy ' Move to the column B and paste the copied Account Number Sheets("Tax Basis BS-SL").Select ActiveCell.Offset(0, 1).Select intRowB = "B" & ActiveCell.Row Application.CutCopyMode = False Range(intRowB).PasteSpecial Paste:=xlPasteValues ' Go back to the Trial Balance and get a copy of the Account Name from column C Sheets("East Trial Balance").Select Application.CutCopyMode = False ActiveCell.Offset(0, 2).Select Selection.Copy ' Return to the Tax Basis BS and paste the Account Name Sheets("Tax Basis BS-SL").Select Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(-2, 0).Select intRowA = "A" & ActiveCell.Row Application.CutCopyMode = False Range(intRowA).PasteSpecial Paste:=xlPasteValues ' Go to column c at the row above and copy all the formulas in that row to the added row ActiveCell.Offset(-1, 2).Select intRowA = "C" & ActiveCell.Row Range(intRowA).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy ActiveCell.Offset(1, 0).Select intRowA = "C" & ActiveCell.Row Range(intRowA).PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False intWorkSht = 1 Do While intWorkSht < 5 ' Go to the next Tax Basis BS worksheet and append a new row in the correct section If intWorkSht = 1 Then Sheets("Tax Basis BS-West").Select ElseIf intWorkSht = 2 Then Sheets("Tax Basis BS-Other").Select ElseIf intWorkSht = 3 Then Sheets("Tax Basis BS-VIE").Select ElseIf intWorkSht = 4 Then Sheets("Tax Basis BS-East").Select End If Cells.Find(What:=strSection, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate ActiveCell.Offset(-1, 0).Select intRowA = ActiveCell.Row intRowB = ActiveCell.Row Range(intRowA &
Nelek at 17-Dec-12 18:21pm
   
Please don't add code blocks of this size in the comments. It is very difficult to read. You can use the "improve question" widget to change your previous post, use it to add/edit what you need

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Shai Vashdi 1,588
1 OriginalGriff 528
2 Manas Bhardwaj 383
3 Damith Weerasinghe 330
4 Tadit Dash 315
0 Sergey Alexandrovich Kryukov 9,575
1 OriginalGriff 6,006
2 Peter Leow 4,500
3 Maciej Los 3,540
4 Abhinav S 3,513


Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 17 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Use
Layout: fixed | fluid