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

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

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