Copy and paste of the data from csv to xlsx

[zyxw1234]

Plz see the file that i have attached
I am looking for a copy and paste vba macro, that will copy the data from (sheet 1 )Alert..csv & paste it to AlertCodes.xlsx (sheet4)
Sheet name can be anything
all files are located in different path
in sheet5 of AlertCodes.xlsx i pasted the result that i need so plz have a look
I have pasted the result in sheet 5 only for understanding purpose, I need that result in sheet4 of AlertCodes.xlsx


files
https://drive.google.com/open?id=1ohB3b ... Gd9MASUXHq
https://drive.google.com/open?id=1ZXlo- ... EnnVZqJZEU
https://drive.google.com/open?id=11RpX5 ... _T5btJ3eZH

[HansV]

Your workbook already contains such a macro, so what exactly is your problem?

[zyxw1234]

The code is not proper, Plz correct it HansV Sir
It is not doing what i wanted to do plz run the macro and see it is unable to copy & paste the data

[HansV]

I ran the macro. It did copy the data...

[zyxw1234]

Has it copied and pasted the data to sheet4 of AlertCodes.xlsx
If yes then let me know?
and if No then plz correct it (make necessary changes in the code As per ur knowledge)

[HansV]

Yes, it pasted to Sheet4 (I cleared Sheet4 before running the macro):

S3317.png

[zyxw1234]

Have u seen the sheet5(what is the output of the macro?)
Is your output matched with the result?

[zyxw1234]

In copy and paste process if there will be any data then paste the data below(We don't have to clear any data Bcoz we need that data )

[HansV]

If you want to paste below existing data, you shouldn't paste to WS2.Cells(1, 1), but to WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Offset(1)

[zyxw1234]

Awesome HansV Sir
Thnx Alot for helping me in solving this problem Sir
Have a Awesome Day
Problem Solved

[zyxw1234]

If sheet4 is blank and i run the code then the data is pasting from 2nd row & i dont want that so plz let me know the necessary changes

[HansV]

Code: Select all

Sub STEP15()
    Dim w1 As Workbook, w2 As Workbook
    Dim WS1 As Worksheet, WS2 As Worksheet
    Dim r As Long
    Application.ScreenUpdating = False
    Set w1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Hot Stocks\Alert..csv")
    Set w2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\Files\AlertCodes.xlsx")
    Set WS1 = w1.Worksheets(1)
    Set WS2 = w2.Worksheets(4)
    ' Try to find the last used row
    On Error Resume Next
    r = WS2.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    WS1.UsedRange.Copy Destination:=WS2.Cells(r + 1, 1)
    w1.Close SaveChanges:=False
    w2.Close SaveChanges:=True
    Application.ScreenUpdating = True
End Sub

[zyxw1234]

This code is not giving the correct ouput plz run it & check it

[HansV]

Sorry, the code in my previous reply contained a typo. I have corrected it

[zyxw1234]

Thnx Alot HansV Sir
Problem Solved

[snb]

Code: Select all

Sub M_snb()
  with Workbooks.Open("C:\Users\**I've been banned**\Desktop\Files\AlertCodes.xlsx")
    sn=.sheets.add( , .sheets(sheets.count),,"C:\Users\**I've been banned**\Desktop\Hot Stocks\Alert.csv").usedrange
    .sheets(.sheets.count).delete
    .sheets(1).cells(rows.count,1).end(xlup).offset(1).resize(ubound(sn),ubound(sn,2))=sn
    .close -1
  end with
End Sub

[zyxw1234]

Thnx Alot Snb Sir for ur Great Help
Problem Solved
Have a Awesome Day

[Doc.AElstein]

I am looking for a copy and paste vba macro, that will copy the data from (sheet 1 )Alert..csv

Alert..csv does not have a Sheet1.
Alert..csv does not have any worksheets
Alert..csv is not an Excel file. It is a text file. A text file does not have sheets. It is text data only
_.____________________________________________________________________________________________________

Code: Select all

Sub M_snb().....

A nice clever compact macro Mr snb , Sir. ( But it puts data in from row 2 if no data originally in the worksheet -

If sheet4 is blank and i run the code then the data is pasting from 2nd row & i dont want that

[snb]

In that case you will have to amend the code. I only offered a suggestion for a method. I don't want to give 'solutions'.

[zyxw1234]

Alert..csv does not have a Sheet1.
Alert..csv does not have any worksheets
Alert..csv is not an Excel file. It is a text file. A text file does not have sheets. It is text data only


I understood this point bu the thing is i need that data to be stored in csv only not in a notepad or text files,bcoz that i have to put in the system & system doesn't support any files instead of csv
Thnx Alot Doc Sir for the Great Info