Copy and paste of the data from csv to xlsx
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Copy and paste of the data from csv to xlsx
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
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
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy and paste of the data from csv to xlsx
Your workbook already contains such a macro, so what exactly is your problem?
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
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
It is not doing what i wanted to do plz run the macro and see it is unable to copy & paste the data
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy and paste of the data from csv to xlsx
I ran the macro. It did copy the data...
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
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)
If yes then let me know?
and if No then plz correct it (make necessary changes in the code As per ur knowledge)
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy and paste of the data from csv to xlsx
Yes, it pasted to Sheet4 (I cleared Sheet4 before running the macro):
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
Have u seen the sheet5(what is the output of the macro?)
Is your output matched with the result?
Is your output matched with the result?
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
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 )
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy and paste of the data from csv to xlsx
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)
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
Awesome HansV Sir
Thnx Alot for helping me in solving this problem Sir
Have a Awesome Day
Problem Solved
Thnx Alot for helping me in solving this problem Sir
Have a Awesome Day
Problem Solved
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
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
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy and paste of the data from csv to xlsx
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
Last edited by HansV on 21 May 2020, 12:06, edited 1 time in total.
Reason: to correct typo
Reason: to correct typo
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
This code is not giving the correct ouput plz run it & check it
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Copy and paste of the data from csv to xlsx
Sorry, the code in my previous reply contained a typo. I have corrected it
Best wishes,
Hans
Hans
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
Thnx Alot HansV Sir
Problem Solved
Problem Solved
-
- 4StarLounger
- Posts: 588
- Joined: 14 Nov 2012, 16:06
Re: Copy and paste of the data from csv to xlsx
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
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
Thnx Alot Snb Sir for ur Great Help
Problem Solved
Have a Awesome Day
Problem Solved
Have a Awesome Day
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Copy and paste of the data from csv to xlsx
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
_.____________________________________________________________________________________________________
A nice clever compact macro Mr snb , Sir. ( But it puts data in from row 2 if no data originally in the worksheet -
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 4StarLounger
- Posts: 588
- Joined: 14 Nov 2012, 16:06
Re: Copy and paste of the data from csv to xlsx
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'.
-
- Banned
- Posts: 253
- Joined: 22 Apr 2020, 17:24
Re: Copy and paste of the data from csv to xlsx
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
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