Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Spreadsheet #2 from Cell references on Spreadsheet #1
Hi,
Please excuse the vagueness of my subject... let me explain: I have a spreadsheet - called Spreadsheet #1 which has 4 columns of data: Column 1 : Sheet Name Column 2 : Row Number Column 3 : Column Number Column 4 : Value Now, I have another spreadsheet, Spreadsheet #2 which has a number of difference worksheets, formatting etc. What I want to do is load the data from Spreadsheet #1 into Spreadsheet #2, using the first 3 columns of data to identify where to put the data. I am using the following code: Workbooks(SourceFile).Activate Sheets("Datasheet").Cells(1, 1).Select While ActiveCell < "" Workbooks("Frontend").Sheets(ActiveCell.Offset(0, 1)).Cells(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3)) = ActiveCell.Offset(0, 4) ActiveCell.Offset(1, 0).Select Wend But I am getting a "Type Mismatch" error. Any suggestions or help appreciated. Rael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Spreadsheet #2 from Cell references on Spreadsheet #1
Your offsets are wrong
Workbooks(SourceFile).Activate Sheets("Datasheet").Cells(1, 1).Select While ActiveCell < "" Workbooks("Frontend").Sheets(ActiveCell).Cells(Act iveCell.Offset(0, 1), ActiveCell.Offset(0, 2)) = ActiveCell.Offset(0, 3) ActiveCell.Offset(1, 0).Select Wend "rael_lucid" wrote: Hi, Please excuse the vagueness of my subject... let me explain: I have a spreadsheet - called Spreadsheet #1 which has 4 columns of data: Column 1 : Sheet Name Column 2 : Row Number Column 3 : Column Number Column 4 : Value Now, I have another spreadsheet, Spreadsheet #2 which has a number of difference worksheets, formatting etc. What I want to do is load the data from Spreadsheet #1 into Spreadsheet #2, using the first 3 columns of data to identify where to put the data. I am using the following code: Workbooks(SourceFile).Activate Sheets("Datasheet").Cells(1, 1).Select While ActiveCell < "" Workbooks("Frontend").Sheets(ActiveCell.Offset(0, 1)).Cells(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3)) = ActiveCell.Offset(0, 4) ActiveCell.Offset(1, 0).Select Wend But I am getting a "Type Mismatch" error. Any suggestions or help appreciated. Rael |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populate Spreadsheet #2 from Cell references on Spreadsheet #1
Thanks for the response Joel.
I made the change but I was still getting the same error. I think it had something to do with the fact that I was switching between files and MS Excel (or me??) was getting confused with "Activecell". So in the end, I split the solution into 2 parts: 1) Open Spreadsheet #1 and move the required sheet into Spreadsheet #2. Close, but don't save Spreadsheet #1 2) Use the newly created sheet in Spreadsheet #2 to load the existing sheets. Remove once done. This seems to do the trick. It is not a very clean solution but it runs pretty quickly and seems to be more reliable anyway... "Joel" wrote: Your offsets are wrong Workbooks(SourceFile).Activate Sheets("Datasheet").Cells(1, 1).Select While ActiveCell < "" Workbooks("Frontend").Sheets(ActiveCell).Cells(Act iveCell.Offset(0, 1), ActiveCell.Offset(0, 2)) = ActiveCell.Offset(0, 3) ActiveCell.Offset(1, 0).Select Wend "rael_lucid" wrote: Hi, Please excuse the vagueness of my subject... let me explain: I have a spreadsheet - called Spreadsheet #1 which has 4 columns of data: Column 1 : Sheet Name Column 2 : Row Number Column 3 : Column Number Column 4 : Value Now, I have another spreadsheet, Spreadsheet #2 which has a number of difference worksheets, formatting etc. What I want to do is load the data from Spreadsheet #1 into Spreadsheet #2, using the first 3 columns of data to identify where to put the data. I am using the following code: Workbooks(SourceFile).Activate Sheets("Datasheet").Cells(1, 1).Select While ActiveCell < "" Workbooks("Frontend").Sheets(ActiveCell.Offset(0, 1)).Cells(ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3)) = ActiveCell.Offset(0, 4) ActiveCell.Offset(1, 0).Select Wend But I am getting a "Type Mismatch" error. Any suggestions or help appreciated. Rael |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate a spreadsheet from a data set | Excel Worksheet Functions | |||
Populate Excel Spreadsheet and Preserve Cell Formatting | Excel Programming | |||
Copying Spreadsheet with Cell References Into A Different Workbook | Excel Discussion (Misc queries) | |||
Using a current spreadsheet to populate a new one | Excel Discussion (Misc queries) | |||
can you use cell references from Microsoft spreadsheet in excel ch | Excel Worksheet Functions |