Solved

VBA to copy paste columns form one file to other

Posted on 2016-09-20
20
105 Views
Last Modified: 2016-10-02
I Receive an Input file with thousands of rows and multiple columns

Below are the steps

1: In the Input file I have columns A:R
2: I have file upload template (standard template desighne to upload in oracle)
3: In the Input file I have to filter by customer number (column D) customer numbers would be repeated
4: For each of the customer Copy the Respective columns and paste to Upload template
5: after filtering by unique customer in input file Copy all the rows of input file Column O to Column B of upload template the same way  Column N to D Column G to E Column P to F Column D to M

Input File (Columns)                   Output File(Columns) from Row 10      
O                                                      B
N                                                      D
G                                                      E
P                                                      F
D                                                      M

6: file save the output file with Customer number (column D of the input file or column M of the output file)
7:  Repeat the same for all customers

Please help creating VBA
0
Comment
Question by:Nirvana
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
  • +1
20 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 41809529
Why wouldn't you use a query to select the records you want and append them to the "template"?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 41809559
Are you running a stand-alone VB Classic application or some MS Office application (Word, Excel, Access, etc.)?

Please post some representative input and output files.
0
 

Author Comment

by:Nirvana
ID: 41810140
@PatHartman: Sorry not sorry how do i run a Query
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:Nirvana
ID: 41810219
@aikmark: its an excel file that i am running attaching the input and output files
Output-file-_555330735.xlsx
Input-File.xlsx
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41810779
Nirvana,
The question cannot be answered in a vacuum.    If you are running the code in Access, then you would link to the input spreadsheet.  Create a query that selects the rows/columns you want.  Then export the query back to Excel.  Two lines of code plus a select query which the wizard will help you to build.  If you are doing this in Excel or some other application, you will need more code because you will have to write a loop that performs the function that the query performed in Access and then inside the loop, write each record to the output file as it is selected.
0
 

Author Comment

by:Nirvana
ID: 41811017
@PatHartman: I am not using Access at all. it is all in excel
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 41811022
OK, then you are stuck writing code.  One of the Excel experts will help.
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41814437
Are you trying to split the data into multiple workbooks?
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41814616
Are the Input Files empty to start with?
0
 

Author Comment

by:Nirvana
ID: 41814622
yes i am trying to split the data. from the main data input files will be created based on the column D and from the input files each of the output files will be created
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41814625
oK. I don't think that your destination columns are correct. I think

D goes to M, G to E, H to F, N to D and O to B.

It's the container that seems wron
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41814711
I think this does what you want.

I have cleaned all data from the OutPut file. Both files should be in the same folder with a sub folder called Import. If it needs any adjustments let me know.

Currently producing 44 new workbooks.
Input-File--1-.xlsm
Output-file-.xlsx
0
 

Author Comment

by:Nirvana
ID: 41814799
Hey Roy, getting an error as below

"Cannot run the macro ‘Input-File—1-.xIsm’!ExfractToSheets. The macro may not be available in this workbook or all macros may be disabled,"

I have enabled the macros in both the files error.png
and when I debug in the code i am getting the error at line "exit_proc"

do I have to do something different
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41814803
Sorry add a colon after exit_proc, I must have deleted it when I tidied up

exit_proc:

Open in new window

0
 

Author Comment

by:Nirvana
ID: 41814828
This worked brilliant.. just need small help my output file has macro, so when i change the line to

"sPath = ThisWorkbook.Path & Application.PathSeparator & "Output-file-.xlsm"

for every file it prompts do you want to save the file as macro enabled workbooks how do i change that?

otherwise it is just brilliant. thanks again
0
 
LVL 20

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41814848
Just need s  the file type changing in the saveas code, xlsx becomes xlsm and file type is 52 not 51

'/// saves to a folder named Import in the master workbook's directory
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Import\" & "Customer - " & sNm & ".xlsm", 52
            ActiveWorkbook.Close True

Open in new window

0
 

Author Closing Comment

by:Nirvana
ID: 41815085
Brilliant
0
 
LVL 20

Expert Comment

by:Roy_Cox
ID: 41815379
Pleased to help. Do you still need the other question working on, the code that I posted there works but I think this makes it unnecessary.
1
 

Author Comment

by:Nirvana
ID: 41823391
Hi Roy sorry to come back to I am facing some challenges
1: if i have the data less than 57 rows, in output file i get data from previous files ( which means in any case the data is getting copied till 57 rows)
2: IN column B i have to copy text as "MI" this also gets copied to only to 57 rows
3: changing the output file to xls; the oracle is accepting on xls files. I have changed the type to xls and number to 57

'/// saves to a folder named Import in the master workbook's directory
            ActiveWorkbook.SaveAs ThisWorkbook.Path & "\Import\" & "Customer - " & sNm & ".xls", 57
            ActiveWorkbook.Close True

please help me out
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question