hi
am struggling on how to start a sequence on copying data columns from excel sheet1 to rows in sheet 2 and sheet 3
sheet 1
sheet2
sheet 3
Suggestion:
- Read range on Sheet1 for B4:AJ6 [Actuals for Last]
- Write range to new Excel sheet “Working Data” from A1
- Invoke VBA to transpose excel table on “Working Data” sheet. ref - VBA Transpose
- On “Working Data”, remove empty rows >> resultant only 12 rows >> Jan to Dec
- Read (entire) range on “Working Data”
- Write Range on “Last Vs Current”, starting D7
- Delete range “Working Data”
Repeat steps 1 to 7 as required, except for
- replacing the read range on Sheet1 accordingly to match the sheets “Last Vs Current” or “Actual vs Forecast”
- replacing the write range for sheets “Last Vs Current” or “Actual vs Forecast”
i have done tell step 4 but i have some errors on Invoke VBA and could not found solution
this is the error
please help thank you
Please change the write range to write cell as shown. The transpose is created in notepad and saved with extension main.bas. Inside the file, you can hard code the range. Invoke VBA code file is main.bas which is placed in project folder (same folder as your project.json), Entry Method Name is Transpose_1. For each range you have to create a separate Sub procedure… Transpose_2, etc.
Between step 4 and 5 you can reverse the order. For example, read range will output to datatable where you can filter data table to remove empty rows.
Otherwise step 4, using excel (go to special, select blanks) and delete cell, then step 5, read range, or VBA.
The error because you should put “Transpose_1” instead of "Working Data = The property value of EntryMethodName is VBA Sub procedure inside file Main.bas.
Two examples of VBA Sub procedures provided - Transpose_1 and Transpose_2 You have to add more Sub procedures yourself.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.