Link to home
Start Free TrialLog in
Avatar of Nirvana
NirvanaFlag for India

asked on

VBA to copy paste columns form one file to other

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
Avatar of PatHartman
PatHartman
Flag of United States of America image

Why wouldn't you use a query to select the records you want and append them to the "template"?
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.
Avatar of Nirvana

ASKER

@PatHartman: Sorry not sorry how do i run a Query
Avatar of Nirvana

ASKER

@aikmark: its an excel file that i am running attaching the input and output files
Output-file-_555330735.xlsx
Input-File.xlsx
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.
Avatar of Nirvana

ASKER

@PatHartman: I am not using Access at all. it is all in excel
OK, then you are stuck writing code.  One of the Excel experts will help.
Are you trying to split the data into multiple workbooks?
Are the Input Files empty to start with?
Avatar of Nirvana

ASKER

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
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
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
Avatar of Nirvana

ASKER

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 User generated image
and when I debug in the code i am getting the error at line "exit_proc"

do I have to do something different
Sorry add a colon after exit_proc, I must have deleted it when I tidied up

exit_proc:

Open in new window

Avatar of Nirvana

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Nirvana

ASKER

Brilliant
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.
Avatar of Nirvana

ASKER

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