Error trying to populate new worksheet using macro

Solved/Closed
AmitMehta96 Posts 3 Registration date Tuesday June 16, 2015 Status Member Last seen June 30, 2015 - Jun 29, 2015 at 01:07 PM
AmitMehta96 Posts 3 Registration date Tuesday June 16, 2015 Status Member Last seen June 30, 2015 - Jun 30, 2015 at 11:54 AM
Hey everyone,

I've been working on a macro to create a new worksheet based on the information entered into a column of a "data" worksheet which populates a template that I created. For every new column of information entered into the "data" sheet, a new worksheet is generated, formatted according to the template, and should be populated from that specific column of the "data" sheet. The information in the data sheet is not in order through the rows, and it cannot be due to how we would be filling this out. I've got the code running partially, until I hit this line:

ActiveSheet.Cells(2, 4).Value = Worksheets("Data").Cells(1, Worksheets("Data").Columns(MyCell)).Value


The error is a type mis-match, and I cannot figure out how to fix it.
I'm trying to fill the cell D2 in the new worksheet (currently blank) with the information from the "data" worksheet that is in row one of the column that the sheet was created off of. I cannot get it to loop through using my variable MyCell as a reference to the column that I want the cell to be picked from.

I'm new to VBA and using macros so this is starting to get frustrating. Please let me know what you think I could do to solve this.

Thanks.

1 reply

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Jun 30, 2015 at 11:46 AM
Hi Amit,

Let's take a look at this part:
Cells(1, Worksheets("Data").Columns(MyCell))

Cells(Row, Column)
You got the row, which is 1.
Now for the column. MyCell is a range, so a row and a column. So you have to let Excel know you want to use the column index from MyCell. This is done which the addition of: .Column

Here is the adjusted part:
Cells(1, Worksheets("Data").Columns(MyCell.Column))

Hopefully I have relieved you of the frustration and you can start enjoying VBA again!

Best regards,
Trowa
1
AmitMehta96 Posts 3 Registration date Tuesday June 16, 2015 Status Member Last seen June 30, 2015
Jun 30, 2015 at 11:54 AM
Awesome, thank you so much!
0