Copy/paste in different workbook

[vivaruthland]

Hi
I want to create a macro with the purpose to call a diferent workbook "Nomedo" and once the workbook is opened the macro should copy and paste a range in this Workbook, but the problem is that the row of the range change, so I can't select a range in this way:
Workbooks().sheets().range("").select
So I expected to use this way
Workbooks().sheets().range(cells(),cells()).select

but it didnt work

Sub actual()
Dim i%, j%, a%, aa%, k%, t%, e%, p%, iEdo%, sNomPP$, sNomShp$, sNomPib$, sNomp$, sNomEdo$, sNome$, iCont%, iConta%, iContb%
Dim aIni%, aFinal%, aPP%, aDatoR%, aInip%
sNomPib = "PIB ESTATAL ANUAL.xlsx"
sNomp = "PIB ESTATAL 06"
sNome = "INGRESO PO"
sNomPP = "PP 1990-2035"
sNomShp = "PP CON CICLO ESTATAL"
p = 11
a = 40
aa = 400



Do While Cells(p, 5) <> ""
iEdo = iEdo + 1 'Cálculo del numero de estados a actualizar
p = p + 1
Loop

'Guarda en memoria los datos para el escenario PIB
aIni = Cells(9, 12)
aFinal = Cells(10, 12)
aDatoR = Cells(11, 12)
aPP = Cells(12, 12)


iContfvar = 521 + (aFinal - 2035)


For e = 1 To iEdo 'esto es para el cada estado

sNomEdo = Cells(e + 10, 5) 'Guarda en -memoria el nombre del Edo

'Abre el archivo estado "e" que se trabajará
Workbooks.Open ("C:\RUTH\RESULTADOS PROYECTO DEMOGRAFIA\ESTADOS\" & sNomEdo), UpdateLinks:=0
Workbooks(sNomEdo).Sheets(sNome).Activate 'Activa la hoja Ingreso PO del archivo estado



'Inserta la variación si se desea insertar un año mayor al 2035
If aFinal > 2035 Then
Workbooks(sNomEdo).Sheets(sNome).Range(Cells(516, 1), Cells(516, 46)).Copy destination:=Workbooks(sNomEdo).Sheets(sNome).Range(Cells(516, 1), Cells(iContfvar, 46))

End If

next e
end sub


I really apreciate if someone could bring me some help

 

[Brianwarnock]

Change

Sheets(sNome)

to

WorkSheets("sNome")

Brian

 

[vivaruthland]

thank u for ur suggestion, but sNome is a string variable that contains the name of the sheet.


The big deal is how to select a range in another workbook. I also test this way

Workbooks(sNomEdo).Sheets(sNome).Activate
Workbooks(sNomEdo).Sheets(sNome).Range(Cells(iContfvar - 1, 1), Cells(iContfvar - 1, 40)).Select
Selection.Copy
Workbooks(sNomEdo).Sheets(sNome).Cells(iContfvar, 1).Select
ActiveSheet.Paste


but it didn't work

thx for ur help

 

[Brianwarnock]

Sheets is used with an index number
Sheets(1)

Brian

 

[Brianwarnock]

Just tried a simple test and this worked, which surprised me.

Guess my previous post was incorrect.

Brian


Dim sheetname As String
Dim r As Integer
sheetname = "sheet5"
r = 2
Sheets(sheetname).Range(Cells(r, 1), Cells(r + 1, 1)).Value = "FRED"

 

[vivaruthland]

yeah!
this is the idea, and it works in the same workbook that is the macro saved, the problem is that I want to do this in a diferent workbook .
;D

 

[Brianwarnock]

It probably isn't your addressing
Change
ActiveSheet.Paste
to
Selection.pastespecial

Brian

 

[Brianwarnock]

Having decided that your pointing at the Range was a red herring I thought about your copy a bit more and was going to suggest using the destination keyword when I noticed that you had in the original,however this part
Range(Cells(516, 1), Cells(iContfvar, 46))

of

Workbooks(sNomEdo).Sheets(sNome).Range(Cells(516, 1), Cells(516, 46)).Copy destination:=Workbooks(sNomEdo).Sheets(sNome).Range(Cells(516, 1), Cells(iContfvar, 46))

looks very suspect surely you should only quote the first cell of the destination.

Brian

Strange that you should leave without a word whilst we are working on this

 

[vivaruthland]

thank u for ur advice. but finally, i could find a way.

For e = 1 To iEdo 'esto es para el cada estado
Cells(14, 14) = iContfVar
sNomEdo = Cells(e + 10, 5) & ".xlsx" 'Guarda en -memoria el nombre del Edo
If aFinal > 2006 Then
Workbooks(sNomEdo).Activate
Workbooks(sNomEdo).Sheets(sNome).Activate
ActiveSheet.Range(ActiveSheet.Cells(iContfVar - 1, 1), ActiveSheet.Cells(iContfVar - 1, 40)).Select
Selection.Copy
Workbooks(sNomEdo).Sheets(sNome).Cells(iContfVar, 1).Select
ActiveSheet.Paste

End If
Next e
End Sub