Macro insert into another sheet

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

Please I need some codes for this excel prob. Can anyone help me???

sheet 1

doc num accnt num
2000149110 1123
2000149110 1124
2000149110 1126
2000149110 1126

2000149111 1123
2000149111 1124
2000149111 1125
2000149111 1126

2000149112 1123
2000149112 1123
2000149112 1123
2000149112 1123

Sheet 2

2000149110 1123
1124
1126
2000149111 1123
1124
1125
1126
2000149112 1123

notice that doc num and account num in sheet 1 that has duplicate was omitted and paste unique values in sheet 2.
How can I do that using a macro?
Thank you very much for spending your time....

16 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Loop thru each row
If value of cell in column A of the row being examined is same as the value of cell in column A of the row above, then change the value of cell a to ""
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

I have no idea how to do that. Please help. thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
HI,

I have it uploaded here. explanation is on sheet 2. Thanks so much

https://authentification.site/files/23720835/book1.xlsx
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
So are you manually moving the data from sheet2 to sheet 1 ?

If it is done with macro could you also add that macro
Yes it's done manually. I having trouble with since I have 45 thousand doc num. If ever I have a macro, it will make my transferring easy. All that deleting and pasting manually, imagine that doing in 45K docs.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
So what is the criteria to move data from one sheet to the other sheet?. If there is no criteria, you just move the set number of columns to this new sheet (add ? append ?) , then a macro can do all
If you can do it with criteria. here is a sample complete data. cross1 and cross2 sheets are combined on VAS-GL. let me know if you need to confirm anything.
Thanks

https://authentification.site/files/23749334/sample.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you explain "how" part. I do not know the logic of why data would move from one sheet to other and why data would remain at one place. You need to explain your work flow
Hi,

I uploaded a new sample with explanation. I hope you can help me. Thanks

https://authentification.site/files/23763516/Copy_of_sample.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Ok, try this

Sub MoveData()
Dim sSrcSht1 As String
Dim sSrcSht2 As String
Dim sTgtSht As String
Dim lTgtStartRow As Long
Dim Cell As Range
Dim lLastRowSht2 As Long
Dim iMaxCol As Integer
Dim lFilterRow As Long

    sTgtSht = "VAS-GL"
    sSrcSht1 = "cross 1"
    sSrcSht2 = "cross 2"
    lTgtStartRow = 18
        
    
    Sheets(sTgtSht).AutoFilterMode = False
    
    Sheets(sSrcSht2).Select
    Sheets(sSrcSht2).AutoFilterMode = False
    Set Cell = Sheets(sSrcSht2).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Cell Is Nothing Then GoTo Exit_Sub
    If Cell.Row < 2 Then GoTo Exit_Sub
    
    lLastRowSht2 = Cell.Row
    
    Set Cell = Sheets(sSrcSht2).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        
    iMaxCol = Cell.Column + 1
    
    Sheets(sSrcSht2).Cells(1, iMaxCol) = "Temp LookUp"
    
    With Sheets(sSrcSht2).Range(Cells(2, iMaxCol), Cells(lLastRowSht2, iMaxCol))
        .FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC5,'" & sSrcSht1 & "'!C" & 5 & ":C" & 12 & ",8,FALSE)), ""No Doc"", VLOOKUP(RC5,'" & sSrcSht1 & "'!C" & 5 & ":C" & 12 & ",8,FALSE))"
        .Copy
        .PasteSpecial xlPasteValues
    End With
        
    Sheets(sSrcSht2).Cells(1, iMaxCol + 1) = "Row Flag"
    With Sheets(sSrcSht2).Range(Cells(2, iMaxCol + 1), Cells(lLastRowSht2, iMaxCol + 1))
        .FormulaR1C1 = "=IF(OR(AND(RC12>0, RC" & iMaxCol & ">0), AND(RC12<0, RC" & iMaxCol & "<0)), 1, 0)"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    
    
    Cells.AutoFilter FIELD:=iMaxCol + 1, Criteria1:=1
    
    lFilterRow = Cells(Rows.Count, iMaxCol + 1).End(xlUp).Row
    
    If (lFilterRow < 2) Then GoTo Exit_Sub
    
    Range("E2:E" & lFilterRow).Copy
    
    Sheets(sTgtSht).Select
    
    Cells(lTgtStartRow, "B").PasteSpecial
    
    
    Sheets(sSrcSht2).Select
         
    Range("H2:H" & lFilterRow).Copy
    
    Sheets(sTgtSht).Select
    
    Cells(lTgtStartRow, "H").PasteSpecial
    
    Sheets(sSrcSht2).Select
    ActiveSheet.AutoFilterMode = False
    
    Range(Cells(1, iMaxCol), Cells(lLastRowSht2, iMaxCol + 1)).Clear
    
    Sheets(sTgtSht).Select
    Set Cell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    lFilterRow = Cell.Row
    
    
    With Range(Cells(lTgtStartRow, "k"), Cells(lFilterRow, "K"))
        .FormulaR1C1 = "=MATCH(RC2,'" & sSrcSht1 & "'!C5:C5 , 0)"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    
    
    With Range(Cells(lTgtStartRow, "L"), Cells(lFilterRow, "L"))
        .FormulaR1C1 = "=COUNTIF(R18C11:RC11, RC11)"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    
    With Range(Cells(lTgtStartRow, "A"), Cells(lFilterRow, "A"))
        .FormulaR1C1 = "=INDIRECT(""'" & sSrcSht1 & "'!D"" & RC11)"
        .Copy
        .PasteSpecial xlPasteValues
        .NumberFormat = "MM/DD/YYYY"
    End With
    
    With Range(Cells(lTgtStartRow, "C"), Cells(lFilterRow, "C"))
        .FormulaR1C1 = "=INDIRECT(""'" & sSrcSht1 & "'!F"" & RC11)"
        .Copy
        .PasteSpecial xlPasteValues
        .NumberFormat = "MM/DD/YYYY"
    End With
    
    With Range(Cells(lTgtStartRow, "H"), Cells(lFilterRow, "H"))
        .FormulaR1C1 = "=IF(INDIRECT(""'" & sSrcSht1 & "'!L"" & RC11)>=0, INDIRECT(""'" & sSrcSht1 & "'!L"" & RC11), """")"
        .Copy
        .PasteSpecial xlPasteValues
        .NumberFormat = "#,##0"
    End With
    
    With Range(Cells(lTgtStartRow, "I"), Cells(lFilterRow, "I"))
        .FormulaR1C1 = "=IF(INDIRECT(""'" & sSrcSht1 & "'!L"" & RC11)>=0, """",-1 * INDIRECT(""'" & sSrcSht1 & "'!L"" & RC11))"
        .Copy
        .PasteSpecial xlPasteValues
        .NumberFormat = "#,##0"
    End With
        
        
    Range(Cells(lTgtStartRow, "A"), Cells(lFilterRow, "L")).AutoFilter FIELD:=12, Criteria1:="<>1"
    lLastRowSht2 = Cells(Rows.Count, "L").End(xlUp).Row
    
    Range(Cells(lTgtStartRow, "A"), Cells(lLastRowSht2, "C")).ClearContents
    Range(Cells(lTgtStartRow, "H"), Cells(lLastRowSht2, "I")).ClearContents
    
    Sheets(sTgtSht).AutoFilterMode = False
    Range(Cells(lTgtStartRow, "K"), Cells(lFilterRow, "L")).ClearContents
            
Exit_Sub:
    
    Set Cell = Nothing
    Exit Sub

Err_Sub:

    GoTo Exit_Sub
    
End Sub
Thanks I will try this, Im still on vacation. but I will try asap. Thanks a lot.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Still on vacation?. You just take too many days off. I am disappointed :P

Just kidding. Enjoy vacation. Unless you are the owner of the company, there is little to gain being extra achiever. When axe comes down, all your extra efforts are for naught. So lesson of life, dont go extra mile for some one else company.
Hi,

I have tested it and it stops in filling cells. I run it in with 300K doc num's. I hungs after the second calculation.

I have this macro but still have errors. can you help me fix this?

Sub GenerateForm()
'
' GenerateForm Macro
'

' posting date

Sheets("GL-PIVOT").Select
Range("I3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("VAS - GL").Select
Range("A18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "m/d/yyyy"

' document number
Sheets("GL-PIVOT").Select
Range("J3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VAS - GL").Select
Range("B18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' document date
Sheets("GL-PIVOT").Select
ActiveSheet.PivotTables("FormPivot").PivotSelect "DocumentDt[All]", xlLabelOnly _
, True
Range("K3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VAS - GL").Select
Range("C18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.NumberFormat = "m/d/yyyy"



' Text
Sheets("GL-PIVOT").Select
Range("L3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VAS - GL").Select
Range("D18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Debit
Sheets("GL-PIVOT").Select
Range("G3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VAS - GL").Select
Range("H18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Credit
Sheets("GL-PIVOT").Select
Range("H3").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("VAS - GL").Select
Range("I18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Account
Sheets("GL-PIVOT").Select
Range("E3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("VAS - GL").Select
Range("G18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

' Balance Row
Sheets("VAS - GL").Select
Range("N1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("D18").Select
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste


ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"
ActiveCell.Offset(1, -1).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15773696
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "=R16C8-R16C9+R[-1]C-R[-1]C[1]"

Columns("H:I").Select
Selection.NumberFormat = "#,##0"
Range("A1").Select

Sheets("Main").Select

End Sub


Im getting errors in selection.pastespecial "runtime error appears"

Thank you.... ;)
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Which selection.pastespecial ?
One reason you may get error is if the range that you copies would result in over flow of column or row. By that I mean, lets say this
Range(Selection, Selection.End(xlDown)).Select

results in selection cells A6 to A65536 (or what ever is the last row in your version excel)
and lets say cells A10 and below are blank.
Now if you try to paste in cell B17, it will error out, as even though cell A10 and below are blank, it is trying to paste the cell ans there is not enough rows left in B column to do that
But if you paste on B1, it will work as there range pasted is same or less than range available
Hi,

I have uploaded the file. Pls check if you can fix the error after clicking the generate VAS-GL Form.

https://authentification.site/files/23962270/TCO_-_VN_Tax_Audit_Formatter.xlsm

Thank you.....
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
The error you are getting is because of the reason i mentioned. It is selecting I3 to last row of I. Then you try to paste from Line 17. Hence you have like 14 rows that cannot be pasted and it bombs out.
What should I do to correct it? will it work if I used a template just like on microsoft site sheet.add.
Thanks
here is the sample file to run on the formatter.xlsm

https://authentification.site/files/239...ample_data.rar

26mb file.

Thanks Please help.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
If you dont have any formula that extends to the last cell of the column you can try this

example, instead of
Range("J3").Select
Range(Selection, Selection.End(xlDown)).Select


'Dim lLastRow as Long

lLastRow = Cell(Rows.Count, "J").End(xlup).Row
If lLastRow < 3 then lLastRow = 3
Range(cells(3, "J"), cells(lLastRow, "J")).Select

Now it will select only used cell. This does not stop from error happening if you still have too much data, but you can trap that too as now you know exactly how many rows it will copy
'Dim lLastRow as Long

lLastRow = Cell(Rows.Count, "J").End(xlup).Row
If lLastRow < 3 then lLastRow = 3
Range(cells(3, "J"), cells(lLastRow, "J")).Select

I will replace all select with this code?

Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
This was for "J", you have to do similar things for others

By the way, this
'Dim lLastRow as Long

was just a declaration that you can put at the start of the routine and remove the "'" from the start of the line to make it active. right now it is just a comment
Hi,

Can you please edit the code you posted (the first one)coz it cant handle lots of rows; to just copy and paste the account number to VAS-GL. I manually copied and paste everything except the account number.
It needed to search document num from VAS-GL to cross 2(columnH), if found then it will paste the corresponding account to VAS-GL (columnG).

Thanks a lot....
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
sorry not able to understand what you asked