groovu
12-08-2016, 01:11 PM
Hey there, I'm making a tool that imports worksheet data from an excel file and exports it onto worksheet templates. I don't really have a background on VBA or any programming for that matter, but I was able to piece together something that worked for me. Now I would like to optimize it to make it more automatic.
The process I want to automate goes like this. The imported excel workbook can have 1 to X amount of sheets. These sheets have data fields that are always in the same cell, except for a few ranges that always have a specific start but may vary with ending cell due to a lines of data in that column. My method of copying that data looked like this.
ThisWorkbook.Sheets(1).Range("a7:a107").Value = wb.Sheets(1).Range("a13:a113").Value
ThisWorkbook.Sheets(1).Range("f7:f107").Value = wb.Sheets(1).Range("c13:c113").Value
'this goes about 8 more times with different ranges.
My crude solution was to set the last cell in the range 100 away from the starting cell. This ensured that I would not miss any data. I would like to figure out a way to detect the last cell with data and stop there while copying the data.
Another process I would like to automate is the data copy from the imports to export sheet. I don't really know how to describe this, but I think the code will explain.
ThisWorkbook.Sheets(1).Range("a7:a107").Value = wb.Sheets(1).Range("a13:a113").Value
...
ThisWorkbook.Sheets(2).Range("a7:a107").Value = wb.Sheets(2).Range("a13:a113").Value
...
ThisWorkbook.Sheets(3).Range("a7:a107").Value = wb.Sheets(3).Range("a13:a113").Value
'this repeats for as many sheets in the import file with each "..." being the rest of the cells I am trying to copy in that sheet.
I copy all the "copy data from sheet" code, paste it into Notepad and replace all the sheet numbers to the next sheet, then paste that at the end of the code to "loop" for the next sheet, lol. Is there a way to loop this process for the next page then make it stop once it reaches a certain page? I currently have a code that counts the number of pages in the import file, I feel this is key to solving my problem, but I don't even know where to begin trying to fit this into the code above.
Summarized, my goals are:
1. Copy a cell range; given a known start cell and the ending being the last cell with data.
2. Automate this process for a number of pages, knowing to stop at the appropriate page based on the number of pages from the import file.
I'm enjoying learning to do this, it feels like solving a puzzle and looking to Google to find the pieces that fit, but I have a lot of spaghetti code right now. Thank you for reading.
PS. If you want me to post my current code, I can do that. I just didn't wanna waste space by posting a bunch of stuff that may not be relevant.
The process I want to automate goes like this. The imported excel workbook can have 1 to X amount of sheets. These sheets have data fields that are always in the same cell, except for a few ranges that always have a specific start but may vary with ending cell due to a lines of data in that column. My method of copying that data looked like this.
ThisWorkbook.Sheets(1).Range("a7:a107").Value = wb.Sheets(1).Range("a13:a113").Value
ThisWorkbook.Sheets(1).Range("f7:f107").Value = wb.Sheets(1).Range("c13:c113").Value
'this goes about 8 more times with different ranges.
My crude solution was to set the last cell in the range 100 away from the starting cell. This ensured that I would not miss any data. I would like to figure out a way to detect the last cell with data and stop there while copying the data.
Another process I would like to automate is the data copy from the imports to export sheet. I don't really know how to describe this, but I think the code will explain.
ThisWorkbook.Sheets(1).Range("a7:a107").Value = wb.Sheets(1).Range("a13:a113").Value
...
ThisWorkbook.Sheets(2).Range("a7:a107").Value = wb.Sheets(2).Range("a13:a113").Value
...
ThisWorkbook.Sheets(3).Range("a7:a107").Value = wb.Sheets(3).Range("a13:a113").Value
'this repeats for as many sheets in the import file with each "..." being the rest of the cells I am trying to copy in that sheet.
I copy all the "copy data from sheet" code, paste it into Notepad and replace all the sheet numbers to the next sheet, then paste that at the end of the code to "loop" for the next sheet, lol. Is there a way to loop this process for the next page then make it stop once it reaches a certain page? I currently have a code that counts the number of pages in the import file, I feel this is key to solving my problem, but I don't even know where to begin trying to fit this into the code above.
Summarized, my goals are:
1. Copy a cell range; given a known start cell and the ending being the last cell with data.
2. Automate this process for a number of pages, knowing to stop at the appropriate page based on the number of pages from the import file.
I'm enjoying learning to do this, it feels like solving a puzzle and looking to Google to find the pieces that fit, but I have a lot of spaghetti code right now. Thank you for reading.
PS. If you want me to post my current code, I can do that. I just didn't wanna waste space by posting a bunch of stuff that may not be relevant.