Excel macro help needed to copy columns from one open excel toanother open excel, and shall run on a

  • Thread starter Thread starter ashu143s
  • Start date Start date
A

ashu143s

Hello Guys,



I have recorded one macro which copies text from one excel column to another, can you please help this to edit so that it runs on all tabs.-:

Sub Mac()
'
' Mac Macro
'

'
Windows("Copy Doc - Bright Starts_RU.xlsx").Activate
Range("B10").Select
ActiveWindow.SmallScroll Down:=186
Range("B10:B205").Select
Selection.Copy
Windows("Copy Doc - Bright Starts.xlsx").Activate
ActiveWindow.SmallScroll Down:=-12
Range("C10").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=63
End Sub
 
hi,

assuming that the 2 workbooks have the same sheets placed at the same rank,

Sub test()
Set wk1 = Workbooks("Copy Doc - Bright Starts_RU.xlsx")
Set wk2 = Workbooks("Copy Doc - Bright Starts.xlsx")
For i = 1 To wk1.Worksheets.Count
wk2.Sheets(wk1.Worksheets(i).Name).Range("B10:B205").Copy
wk1.Sheets(wk1.Worksheets(i).Name).Range("C10")
Next
End Sub

isabelle

Le 2014-11-24 07:48, (e-mail address removed) a écrit :
 
hi,

assuming that the 2 workbooks have the same sheets placed at the same rank,

Sub test()
Set wk1 = Workbooks("Copy Doc - Bright Starts_RU.xlsx")
Set wk2 = Workbooks("Copy Doc - Bright Starts.xlsx")
For i = 1 To wk1.Worksheets.Count
wk2.Sheets(wk1.Worksheets(i).Name).Range("B10:B205").Copy
wk1.Sheets(wk1.Worksheets(i).Name).Range("C10")
Next
End Sub

isabelle

Le 2014-11-24 07:48, (e-mail address removed) a écrit :
Click to expand...

Hello isabell, I am really thankful to u for reply, Yes both Excel vl have same number and named sheets, but unfortunately I am getting error which says "Öbject doesn't support property or method" on wk1.Sheets(wk1.Worksheets(i).Name).Range ("C10")
 
Hi,

Am Mon, 24 Nov 2014 04:48:49 -0800 (PST) schrieb (e-mail address removed):
Windows("Copy Doc - Bright Starts_RU.xlsx").Activate
Range("B10").Select
ActiveWindow.SmallScroll Down:=186
Range("B10:B205").Select
Selection.Copy
Windows("Copy Doc - Bright Starts.xlsx").Activate
ActiveWindow.SmallScroll Down:=-12
Range("C10").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=63
End Sub
Click to expand...

Insert foolowing code in a module of "Copy Doc - Bright Starts.xlsx" and
save the workbook as workbook with macros .xlsm
Then open both workbooks and run the macro:

Sub Test()
Dim wbk1 As Workbook, wbk2 As Workbook
Dim i As Long

Set wbk1 = Workbooks("Copy Doc - Bright Starts_RU.xlsx")
Set wbk2 = ThisWorkbook

For i = 1 To wbk1.Worksheets.Count
wbk2.Sheets(i).Range("B10:B205").Copy _
wbk1.Sheets(i).Range("C10")
Next
End Sub


Regards
Claus B.
 
Guys, U rock...Thanks a lot.

I also did little modification and it worked for me..


Sub test()
Set wk1 = Workbooks("Copy Doc - Bright Starts.xlsx")
Set wk2 = Workbooks("Copy Doc - Bright Starts_ES-XM.xlsx")
For i = 1 To wk1.Worksheets.Count
wk2.Sheets(wk1.Worksheets(i).Name).Range("B10:B205").Copy
wk1.Sheets(wk1.Worksheets(i).Name).Range("D10:D205").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wk1.Sheets(wk1.Worksheets(i).Name).Range("D10:D205").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Next



End Sub
 
Hello Isabelle and Claus,

This macro works perfectly, however for cell contains single formatting is ok, but if one cell has segments that is red formatted for some text, bold for some and italic for some it doesn't work.. How can I apply different formatting for single cell same as source.
 
Back
Top