Copy & paste ranges from 1 wkbk to another

[Diddy]

Hi everyone,

I have a workbook that I have set up with formulas, data validation, cond
formatting and macros. Now I need to make a copy of this in another language.
I have the translations set up in exactly the same cells in another workbook
(Source) so I thought the easiest option would be to copy ranges from Source
to Original (Destination). I need to paste as values because the destination
wkbk has conditional formatting.

There are 25 worksheets and the ranges are all odd row starting from 3 going
to between 20 and 25 covering columns A-M. There are a further 7 worksheets
with different information that would probably be easier to amend by hand.

If someone could help me to get started on this it would be brilliant. I was
wondering whether I could use input box to specify which sheets in each
workbook?

Thank you

 

[Joel]

Sub TranslateData()

Set OldBk = ThisWorkbook

filetoOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls")
If filetoOpen = False Then
MsgBox ("Cannot OPen file - Exiting Macro")
Exit Sub
End If

Set TransBk = Workbooks.Open(Filename:=filetoOpen)

For Each OldSht In OldBk.Sheets
Set TransSht = TransBk.Sheets(OldSht.Name)
LastRow = OldSht.Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 3 To LastRow Step 2
OldSht.Range("A" & RowCount & ":M" & RowCount).Copy _
Destination:=TransSht.Range("A" & RowCount)
Next RowCount
Next OldSht
End Sub