Hello Experts,
I got the below vba code somewhere in the forum & I amended it a little bit with the help of little understanding of vba.But the things I am failed to do is the following.
I cannot make it to paste as Values.
I cannot make it to pull data from column A4 from my closed workbook & paste in the target worksheets from Range A4.
Can anyone please help me out of this.
Please Login or Register to view this content.
Last edited by Ariff_Chowdhury; 05-09-2015 at 02:41 PM.
is there anyone who can give me clue how to achieve this.
Hi,
Can anyone please look into this as why the data is not being copied to specific sheets ?
Please Login or Register to view this content.
If you run it step by step in Debug then it will be easier for you to spot. Without your workbooks it is very difficult for us to help you!
!If all your troubles are solved, then so is the thread. Show this by marking it so using thread tools at the top of the page.
If I helped do this, then please show your appreciation by awarding rep points.
<------ Button for that is over there
Hello Experts,
Can you please look into my files, as I am failed to do what I want to achieve.
I have attached both files If you people could kindly help.
Thanks
Source File 05-08-2015.xlsx
Destination.xlsm
Hello, can you anyone please guide is there any subtitute for Used Range
I dont want to copy first three rows of source worksheets as these contain headings (extra details)Please Login or Register to view this content.
below is the code, i can't do it
Please Login or Register to view this content.
Last edited by Ariff_Chowdhury; 05-09-2015 at 01:31 PM.
bump - need a reply pleaseee
Hi Ariff
Try this
Please Login or Register to view this content.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
THANKS A LOT FOR YOUR REPLY
I have tried your code but didn't work as the way it should do
I have tried this code
Please Login or Register to view this content.
Hi Ariff
Just what does this mean?Do you get an error? Does it NOT Copy/Paste? What is or is not happening that you wish it to do?didn't work as the way it should do
Hi jaslake,
Your code was producing error on below line along with the Clipboard Notification pop ups.
Then I deleted this erroneous line and executed it again but it works only for first sheet that is "Inventory Tree"Please Login or Register to view this content.
in the second Sheet "Cellular Accessories" it didn't copy data and in the third Sheet "New Activation" it pasted the data from H4 (where I have some functions for calculations). & in the rest of the Worksheets it didn't paste the data
There were some extra lines in the code so I removed them now using this code but not working.
Please Login or Register to view this content.
Hi Ariff
strName is not defined anywhere in the Code...thus the error.
The attached is what I get in running the Code.
Thank you jaslake for your response,
YOUR CODE WORKED..THANK YOU SO MUCH
strName is not define.... I can't recall why did I add this...
I have changed the Offset(9).Row to Offset(0).Row then upon running the macro it pasted the data from A5 of the destination worksheet. which is correct!Please Login or Register to view this content.
can you please guide is this the right way to do this or not ? I mean changing offset(9) to (0).
But screen is flickering too much is there any way to handle this ?
Application.ScreenUpdating = False may be I don't know exactly.....can you please fix this?
One thing I wanted to ask that can I rely on this macro? actually I am little afraid of implementing this in my actual file because I have done some calculation in adjacent columns like D,E,F, previously many of the time the macro pasted the data on my calculated columns and all my functions got removed.
Destination.xlsm
Thanks again!
I got the error it is pasting from the ActiveCell in each worksheets
for example: if in the Worksheet "Inventory Tree" if the selected cell is F18 then macro will paste the data from F18.
Hi Ariff
Should the Code ALWAYS post to Row 5?
If so, change this line of Code from
to, simplyPlease Login or Register to view this content.
To manage thisPlease Login or Register to view this content.
After your Dim Statements add thisscreen is flickering too much
And before End Sub add thisPlease Login or Register to view this content.
Regarding thisPlease Login or Register to view this content.I've tested it, you've tested it...does it work? Are you concerned (I see you are!!!)...keep testing and testing...and testing...Does it work? You tell me.can I rely on this macro
I followed your instructions by changing this line
toPlease Login or Register to view this content.
NextRow = 5 (Actually I need this macro to ALWAYS paste (overwrite) the data from Range A5 of my destination worksheet)
But unfortunately it is still pasting on ActiveCell, Let me share the workbook with generated results. Destination.xlsm
Last edited by Ariff_Chowdhury; 05-09-2015 at 03:55 PM.
Hi Ariff
Active Cell is not mentioned in the Code...unless you've modified the Code.
I'm a bit confused...which File are we talking about here...the Source File or the Destination File?I got the error it is pasting from the ActiveCell in each worksheets
for example: if in the Worksheet "Inventory Tree" if the selected cell is F18 then macro will paste the data from F18.
We are talking about destination workbook
It is pasting the data on ActiveCell of my Destination Workbook, for example, In sheet "MI New Activation" the selected cell was C7 macro pasted the data on C7
if you please have glance on the attached file the picture will get clear.I didn't modified your code
Please Login or Register to view this content.
Hi Ariff
We're stepping on each others toes here
What I hear you saying is that you wish Destination Book Worksheets to be CLEARED and RECREATED each time you run the Code.Is this true?
Actually I need this macro to ALWAYS paste (overwrite) the data from Range A4 of my destination worksheet
Keeping it simple,
1 - The macro should get the data from closed workbook by matching worksheets name (the macro is already doing this).
2 - Whenever the macro is run it always paste the data of source file in the destination worsheets on Range A5 (sorry prevously I mistaken & I wrote A4). ALWAYS.
Do i need to share workbook illustrating my intended result ? please let me know
Hope it clarifies!
Hi Ariff
No, you don't need to attach another Workbook.
I need this question answered. If there's Data in Destination File target Worksheets ("Inventory Tree", "Cellular Accessories", etc) , such Data should be cleared before the Code executes.
Is this correct?
Yes such data should be cleared. but It should not clear my calculations (as I mentioned previously I have added some functions in column D,E,F & onward in each worksheets I mean in "Inventory Tree", "Cellular Accessories", etc
Reason : Because I get the source file data from database software on daily basis after doing some calculations on the data I have to prepare reports, so everyday the data are changed that's why I need this file to clear existing data before executing the code.
Hi Ariff
The Code will impact ONLY Columns A through C...if not, I've REALLY screwed up...trust me.
Yes I hope so
Hi Ariff
I've tested this about 20 times and it seems to do as required.
Please note these Setup changes you'll need to make..
Workbook Setup.jpg
Please Login or Register to view this content.
This is the result when i tested it.
1 - I downloaded your both files.
2 - Saved on my Desktop.
3 - Opened the Destination.xlsm.
4 - Change the path accordingly along with slash "\" at the end of path and added ".xlsx" at the end of the file name.
5 - Then I did the following in order to test the macro.
I clicked on the Inventory Tree Worksheet and simply click on the cell "I1" and I did the same with Worksheet Cellular Accessories clicked on the Cell "I1"
In New Activation Worksheet I just clicked (make ActiveCell) as E22, however for Prepaid F11, for Upgrade I1, MI New Activation F8, MI Prepaid G7 & at last MI Upgrade F13.
6 - Then I hit the Macro Button.
7 - Macro pasted the result on ActiveCell in each Target Worksheets. Below is the few screenshot of Target Worksheets
Last edited by Ariff_Chowdhury; 05-11-2015 at 08:29 AM.
Hi Ariff
What version of Excel are you working with?
Microsoft Office Excel 2013
Hi Ariff
I don't have 2013 so can't test. In 2007 and 2010, I took the steps you describeIt performs as expected...pastes to A5 on each worksheet.5 - Then I did the following in order to test the macro.
I clicked on the Inventory Tree Worksheet and simply click on the cell "I1" and I did the same with Worksheet Cellular Accessories clicked on the Cell "I1"
In New Activation Worksheet I just clicked (make ActiveCell) as E22, however for Prepaid F11, for Upgrade I1, MI New Activation F8, MI Prepaid G7 & at last MI Upgrade F13.
6 - Then I hit the Macro Button.
Your profile indicates you have 2010...run the Code there...let me know the results.
Actually I don't have 2010 installed in my system, but do you really think that the result is different due to the Excel version ?
Hi Ariff
I wish I knew...I know it works on my Platform in 2007 and 2010. You know it does NOT work on your Platform in 2013. All excel versions have their incompatibilities but the only thing I've discovered for 2013 and PasteSpecial xlValues is that Skype to Call can cause Excel 2013 Paste issues.
Now, I'm grasping at straws here and breaking a couple of coding Best Practices. I want you to replace the Code with this. The Code forces Cell A5 to be the Active Cell and Pastes there (at least on my Platform.
Let me know...Please Login or Register to view this content.
Hi jaslake,
First of all thank you very much for all your efforts!
I tested your code in my file & it worked well, & it pasted the data on A5, but before implementing this code in my actual file I will give it a full test by tomorrow at my work where MS Office 2010 is installed in the system. I will check your both codes in different workstations at work once I get there by tomorrow & and then will get back to you with feedback.
Thanks again!
Hi Ariff
You're welcome...I enjoy challenges...don't like to use "Brute Force" to solve them...
Get back to me.
Hi Ariff
One more thing, if possible...if you have a machine at work that has 2013 installed, please run both Codes on that machine...I'd like to know if this is a 2013 issue.
Thanks.
Hi jaslake,
Sorry I can't respond to you yesterday!
I have tested your both codes at my work, in MS Excel 2010 version, both codes pasted the data at desired location,
In MS Excel version 2013 the first code encountered the same error which I was facing on my PC at home, i.e. pasting data on selected cells in each target worksheets.(please note that we have Skype installed in all our system)
Other than these some other issues has arisen,
1 - My source files are located at network shared folder, when I provided the shared folder path to the macro, It produced error on line.
And sometimes even on my own Drive like D:/Source Data.xlsx/,(when I have my Destination Workbook on Desktop), Since I have very little knowledge of vba I didn't know how to check the error but I tried my level best to find it so I put two line of codes in order to check what's happening behind the scenePlease Login or Register to view this content.
Please Login or Register to view this content.
In the Message box I got the path string but FileName was empty. Can please look into this to fix this (although I checked all the parameters several time)
Also can you put lines of code for me which provides appropriate message when the file successfully imported data to the file and if the data can't be imported for any reason then let user know about it.
Looking forward to your continued cooperation!
Thank you
Hi Ariff
What is the Path and what is the File Name...this is not a qualified Path...slashes are pointing forward...and it's certainly not the Path to your Desktop.
Hi jaslake,
I have given below path & file name in the cell
Z:\Danish\TCCWorld\RQ4\Sales Data\Sales Raw Data\May 2015\05-11-2015\
Sales Data 05-11-2015.xlsx
Hi Ariff
On my Platform this Code works as expected when the specs are set as follows:
Network Drive.jpg
My Network Drive is on a separate laptop and opens the File and does the Import as expected.
These has been added to the Code:
Please Login or Register to view this content.Please Login or Register to view this content.Please Login or Register to view this content.Please Login or Register to view this content.
Hi jaslake,
I have tested the code thoroughly, and tried it in different machines/data, It worked as expected no any error produced, neither any data removal issues nothing!. All worked, as I was expecting.
It is just due to your efforts that have you put in this to make this successful. I am very thankful to you for your support.
Million Thanks!!!!
You're welcome...glad I could help.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
If this resolves your issue please mark your Thread as SOLVED.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
Hi jaslake,
I have already done that
Hi Ariff
Thanks for the Rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks