Macro for copying set of data, but pasting in a different location 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

 

[Roger Govier]

Can you show the code you are currently using.

 

[Pedro's PhD]

Hi Roger Govier,

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("A1:A8").Select
Sheets("Sheet2").Select
Range("A1:A10").Select
Selection.Copy
Windows("Workbook2.xlsx").Activate
Sheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Workbook1.xlsx").Activate
Range("B1:B10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Workbook2.xlsx").Activate
Sheets("Sheet2").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Workbook1.xlsx").Activate
Sheets("Sheet3").Select
Range("A1:A10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Workbook2.xlsx").Activate
Sheets("Sheet3").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Workbook1.xlsx").Activate
Range("B1:B10").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Workbook2.xlsx").Activate
Sheets("Sheet4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub