Nirvana
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
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
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.
Please post some representative input and output files.
ASKER
@PatHartman: Sorry not sorry how do i run a Query
ASKER
@aikmark: its an excel file that i am running attaching the input and output files
Output-file-_555330735.xlsx
Input-File.xlsx
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.
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.
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?
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
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
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
ASKER
Hey Roy, getting an error as below
"Cannot run the macro ‘Input-File—1-.xIsm’!Exfra ctToSheets . The macro may not be available in this workbook or all macros may be disabled,"
I have enabled the macros in both the files
and when I debug in the code i am getting the error at line "exit_proc"
do I have to do something different
"Cannot run the macro ‘Input-File—1-.xIsm’!Exfra
I have enabled the macros in both the files
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:
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
"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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
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