Assign macro to paste data in a different place each time

[Pedro's PhD]

Hi everyone,

This is a little difficult to explain but hopefully it will make sense....

I have workbook1 sheet1 with data that is entered into columns A and B. Then on workbook1 sheet2 and sheet3 I have formulae in columns A and B which depend on the values entered in Sheet1.

I then have a workbook2, which I run a macro to copy the values from workbook1 into as follows:

Workbook1, Sheet2, column A goes to Workbook2, Sheet1 column A
Workbook1, Sheet2, column B goes to Workbook2, Sheet2 column A
Workbook1, Sheet3, column A goes to Workbook2, Sheet3 column A
Workbook1, Sheet3, column B goes to Workbook2, Sheet4 column A

This works fine. However, the final data here is always copied into column A. When I change the original data entered in workbook1 sheet1 for a new trial, is it possible for that final data to end up in the B columns of workbook2? And so on for every new trial.....

So simply put, is there a way that if the cursor starts in a certain column before running a macro, then that will let all the resultant data to be entered into that column.

Thanks in advance,

Pedro

[Pedro's PhD]

Please Login or Register  to view this content.

[Roger Govier]

Hi

I am assuming that all the columns will remain in alignment, so the following code only finds the last column once, but you could calculate the last column for each sheet if necessary

Please Login or Register  to view this content.

[Pedro's PhD]

Hi,

I am not familiar with VBA, but i have pasted your code in replace of mine and it comes up with the run time error, there appears to be a problem with row 5 of coding

Set wb1 = Workbooks("Workbook1.xlsm")


Thanks

[Roger Govier]

Hi,

I am not familiar with VBA, but i have pasted your code in replace of mine and it comes up with the run time error, there appears to be a problem with row 5 of coding
Set wb1 = Workbooks("Workbook1.xlsm")
Thanks

I assumed the macro was being held in Workbook1, hence I named it as xlsm not xlsx.
Change to
Set wb1 = Workbooks("Workbook1.xlsx")