Previous Topic Next Topic

Macro insert into another sheet

Closed
harris - Aug 5, 2010 at 04:42 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Sep 1, 2010 at 06:36 PM
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 responses

Answer 1 / 16
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 5, 2010 at 06:21 AM
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
mark
Aug 7, 2010 at 12:21 AM
I have no idea how to do that. Please help. thanks
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 7, 2010 at 05:05 AM
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
0
Answer 2 / 16
Harris
Aug 9, 2010 at 12:37 AM
HI,

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

https://authentification.site/files/23720835/book1.xlsx
0
Answer 3 / 16
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 9, 2010 at 10:31 AM
So are you manually moving the data from sheet2 to sheet 1 ?

If it is done with macro could you also add that macro
0
Answer 4 / 16
Harris
Aug 10, 2010 at 01:16 AM
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.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 16
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 10, 2010 at 01:58 PM
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
0
Answer 6 / 16
Harris
Aug 10, 2010 at 09:02 PM
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
0
Answer 7 / 16
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 11, 2010 at 07:28 AM
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
0
Answer 8 / 16
Harris
Aug 11, 2010 at 07:26 PM
Hi,

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

https://authentification.site/files/23763516/Copy_of_sample.xls
0
Answer 9 / 16
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 17, 2010 at 09:39 PM
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
0
Answer 10 / 16
Harris
Aug 24, 2010 at 07:36 AM
Thanks I will try this, Im still on vacation. but I will try asap. Thanks a lot.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 24, 2010 at 07:48 AM
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.
0
Answer 11 / 16
Harris
Aug 25, 2010 at 05:42 AM
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.... ;)
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 25, 2010 at 08:32 AM
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
0
Answer 12 / 16
Harris
Aug 25, 2010 at 10:05 AM
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.....
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 25, 2010 at 11:02 AM
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.
0
Answer 13 / 16
harris
Aug 25, 2010 at 06:44 PM
What should I do to correct it? will it work if I used a template just like on microsoft site sheet.add.
Thanks
0
Answer 14 / 16
Haaris
Aug 25, 2010 at 11:12 PM
here is the sample file to run on the formatter.xlsm

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

26mb file.

Thanks Please help.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 26, 2010 at 06:56 AM
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
0
Answer 15 / 16
Haris
Aug 26, 2010 at 08:42 AM
'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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Aug 26, 2010 at 09:00 AM
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
0
Answer 16 / 16
Haris
Aug 27, 2010 at 06:21 AM
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....
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Sep 1, 2010 at 06:36 PM
sorry not able to understand what you asked
0

Similar discussions

Micro sd card showing "please insert Disk"
Joreece -
meme -

6 responses
Code for macro that conditionally inserts rows
bfleming -
bfleming -

4 responses