Error trying to populate new worksheet using macro [Solved/Closed]

Report
Posts
3
Registration date
Tuesday June 16, 2015
Status
Member
Last seen
June 30, 2015
-
Posts
3
Registration date
Tuesday June 16, 2015
Status
Member
Last seen
June 30, 2015
-
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

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
3
Registration date
Tuesday June 16, 2015
Status
Member
Last seen
June 30, 2015

Awesome, thank you so much!