copy & paste between workbooks

[dOOb]

Hi All,
I’m stuck with a problem that I’m sure is very simple to solve, but my brain is just not working today.
I need to copy a range from workbook A, paste it in workbook B, then copy another range from workbook B and paste it in workbook A.
Workbook B always has the same file and sheet name, but for Workbook A, the file name and sheet name are always different.

This is my code:

Sub Macro1()

Dim myWb As Workbook
Dim mySheetName As String

mySheetName = ActiveSheet.Name

Range("A:G"). Copy
Workbooks("Personal.xlsm"). Sheets(2).Activate ‘ this always the same workbook and sheet
Range("A1").Select
ActiveSheet.Paste

…Rest of my code, and then …

Range("H:H").Copy

and now, how do I go back to Workbook A to paste it there????

i though something like the below should work but it doesn't

ActiveWorkbook.Worksheets(mySheetName).Activate
Range("H1").Select
ActiveSheet.Paste

please?

[AndyLitch]

Please Login or Register  to view this content.

[HaHoBe]

Hi, dOOb,

maybe just set objects and use them? I assume that workobok personal.xlsm is open (and I would definitely have a different name as not to interfere with the personal macro book):

Please Login or Register  to view this content.

Instead of copying whole columns you might like to experiment with an intersect of UsedRange and these columns.

Ciao,
Holger

[dOOb]

Thanks a lot for your replies,

Andy, the solution you offered pastes the values from column H in Workbook B (copies&pastes over itself) and not A where i need it - i tried something similar already before, with no result - is it possible something is wrong with my excel?

Holger, your solution causes a Run Time error (Object required) in line:

msheets.Range("H:H").Value = .Range("H:H").Value

- i don't understand what's "msheets" - (should be "sheets" maybe?)

thank you both for that, but the problem is still unsolved

[HaHoBe]

Hi, dOOb,

my fault as I hadn´t tested the code, it should read as you expected

Please Login or Register  to view this content.

Ciao,
Holger

[dOOb]

Holger, thank you, i've corrected that (and the typo in "Range"), but unfortunately it still does not do what i need - i don't understand where's the issue

[HaHoBe]

Hi, dOOb,

(and the typo in "Range"

If you mean the dot preceeding Ranges you should leave them in place as this is within a With-Statement where the name of the workbook and the worksheet are being referenced.

Written in long it should look like this:

Please Login or Register  to view this content.

which was shortend to read

Please Login or Register  to view this content.

Ciao,
Holger

[dOOb]

no, i meant the Ramge instead of Range in:
.Range("A:G").Value = mySheet.Ramge("A:G").Value

but still, with that changed it does not work

[HaHoBe]

Hi, dOOb,

oops - you´re right about that one. I build a quick working model but I couldn´t encounter any problems or run-time errors when running the corrected code (exccept for the extra time as only 35 rows were filled but the whole columns were copied).

Ciao,
Holger