Copying data

[Brighton]

I've been trying for a while to write a code that will copy the data between two workbooks (lets say A and B) but will only copy columns A2:T2 and then down the used range. I am unsure as how to state that in the code.

Does anyone have any examples of code they got to work for copying between workbooks in Excel?

This is something I have to run once a month for monthly reports.

 

[texasalynn]

Welcome to MrExcel board....

here is a sample of copying between two sheets

Sub Sheet1_2()
Dim LR As Long, ALR As Long
Dim wb2 As Variant
LR = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
ALR = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
wb2 = "Sheet1"
Sheets("Sheet2").Range("A1:Z" & LR).Copy Destination:=Sheets(wb2).Range("A" & ALR)
End Sub

then change the ranges to your area

 

[Brighton]

I have this:

Sub Sheet1_2()
Dim LR As Long, ALR As Long
Dim wb2 As Variant
LR = Sheets("BSanford").Cells(Rows.Count, 1).End(xlUp).Row + 1
ALR = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row + 1
wb2 = "Master"
Sheets("BSandford").Range("A2:T2" & LR).Copy Destination:=Sheets(wb2).Range("A" & ALR)
End Sub

and I keep getting a runtime error 9 that tells me the script is out of range. Any other ideas?

 

[VoG]

Try

Sheets("BSandford").Range("A2:T" & LR).Copy Destination:=Sheets(wb2).Range("A" & ALR)

 

[Brighton]

I did try that, it gave me the same error.
I'm probably not realizing something very simple. Thanks for trying to help so far though.

 

[VoG]

What is the name of the sheet - red or blue

Sub Sheet1_2()
Dim LR As Long, ALR As Long
Dim wb2 As Variant
LR = Sheets("BSanford").Cells(Rows.Count, 1).End(xlUp).Row + 1
ALR = Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Row + 1
wb2 = "Master"
Sheets("BSandford").Range("A2:T" & LR).Copy Destination:=Sheets(wb2).Range("A" & ALR)
End Sub

 

[Brighton]

It is red. Thanks for catching that!!!

 

[Brighton]

Anyone know how I can loop this for other sheets as well?

 

[texasalynn]

also notice the other change that Peter shows where you had a2:t2 you don't want the 2 after the T.

 

[texasalynn]

Anyone know how I can loop this for other sheets as well?

need a little more information
Do exactly the same thing to copy from sheet 1 to sheet2, 3, 4, etc. Or some other copy?