What's the correct syntax to copy a range of cells from one Workbook to another?

Question

I'm creating a new Book then opening files in a directory and adding the corresponding sheets and values to the new Book. I've gone through so many forms showing the "Copy Destination:=" or just one range equal to the other, but I can't get my script work. Everything is working (this is just a snippet, so trust me 98% of it works) except this ONE line right before "Workbooks(FileName).Close" in the Else scenario. I normally find my answer and figure it out, but I turn in the towel here. Help please!

Dim SiteUsedCheck As Boolean
Dim NewBook As Workbook
Dim NewSheet As Worksheet
Dim SaveAsName As String
Dim WeekRange As Range

Set WeekRange = Range("I5:O17")

SaveAsName = "Invoice" & "_" & Home.Range("C23").Value & ".xlsm"
MsgBox SaveAsName

Set NewBook = Workbooks.Add
    With NewBook

                Do While FileName <> "" '<---recall FileName variable looks at excele books; it ignores folders

                    Workbooks.Open (Directory & FileName)

                        If Workbooks(FileName).Worksheets("TotalHours").Cells(SecretTest, WeekCol) = 0 Then

                            Workbooks(FileName).Close

                        Else
                            Dim TempSheetName As String
                            Set NewSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count))
                            TempSheetName = Workbooks(FileName).Worksheets("TotalHours").Range("B2").Value
                            NewSheet.Name = TempSheetName

                            NewBook.Sheets(TempSheetName).Range("A1").Value = Workbooks(FileName).Sheets("TotalHours").Range("WeekRange") '<--This is the line that keeps getting an error. But if I put a "1" on the right side of the = it works. So what's wrong with this tiny piece?

                            Workbooks(FileName).Close

                        End If


                    FileName = Dir()

                Loop

        .SaveAs FileName:= _
            InvoiceDirectory & SaveAsName _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

        Workbooks(SaveAsName).Close
    End With

You do not appear to have assigned a value to `SecretTest` or `WeekCol`? — SJR, Jan 10 '17 at 17:09
I see I was looking at the wrong line. `Range("WeekRange")` is wrong because it is not a named range, but why are you trying to set the value of a single cell to a range? — SJR, Jan 10 '17 at 17:18
oh, I thought you could put the whole range into one cell then that cell (in this case A1) will be the top left of everything copied over. And in above snippets WeekRange is defined based on what week it is; I only wrote one example so you guys know what I was using for WeekRange — Emanuel Siu, Jan 10 '17 at 18:21
I've added a one-line answer below as a bit too long for a comment so let me know if it does what you want. — SJR, Jan 10 '17 at 18:26

score 0 · Answer 1 · answered Jan 10 '17 at 17:13

0

The easiest way is to use some standard syntax like:

r1.Copy r2

where r1 and r2 have been Dim'ed as Ranges. Here is a tiny example

Sub BooktoBook()
    Dim r1 As Range, r2 As Range, NewBook As Workbook
    Set r1 = ActiveWorkbook.Sheets("Sheet1").Range("A3:D7")

    Set NewBook = Workbooks.Add
    Set r2 = NewBook.Sheets("Sheet1").Range("A3:D7")

    r1.Copy r2
End Sub

answered Jan 10 '17 at 17:13

Gary's Student

95,722
10
59
99

This with the other answer helped me understand what was going wrong and how to work it. Thank you for breaking it down! – Emanuel Siu Jan 10 '17 at 19:35

score 0 · Accepted Answer · answered Jan 10 '17 at 18:25

0

Perhaps

NewBook.Sheets(TempSheetName).Range("A1").resize(weekrange.rows.count,weekrange.columns.count).Value = WeekRange.value

answered Jan 10 '17 at 18:25

SJR

22,986
6
18
26

interesting. That was so close This worked with no error, but it grabbed it from the wrong workbook. I want to grab that WeekRange from the workbook that opens 1st thing in the Do While – Emanuel Siu Jan 10 '17 at 19:12
You are defining WeekRange at the beginning of the code though before you open any files. Are you saying that it should be defined as that range in each opened workbook? – SJR Jan 10 '17 at 19:16
Yup and that's why it wasn't working. I put the case scenario into the loop and it works now. Thank you so much! I was having trouble understanding how the setting ranges worked. – Emanuel Siu Jan 10 '17 at 19:34

score 0 · Answer 3 · answered Jan 10 '17 at 19:37

Thank you everyone, when I combined what I learned with the two answers given to me I re-worked the WeekRange to actually be in the right place as in the code below.

Dim SiteUsedCheck As Boolean
Dim NewBook As Workbook
Dim NewSheet As Worksheet
Dim SaveAsName As String
Dim TempInvoiceRange As Range



SaveAsName = "Invoice" & "_" & Home.Range("C23").Value & ".xlsm"
MsgBox SaveAsName

Set NewBook = Workbooks.Add
    With NewBook

                Do While FileName <> "" '<---recall FileName variable looks at excele books; it ignores folders

                    Dim OpenRange As Range
                    Workbooks.Open (Directory & FileName)

                    Select Case Home.Range("C25")

                        Case Is = 1
                            Set WeekRange = Workbooks(FileName).Worksheets("TotalHours").Range("A5:G17")    '<---Range for Current Week to transfer to invoice
                            SecretTest = 18                                                                 '<---Row to check if site was used for the week
                            WeekCol = 7                                                                     '<---Column corresponding to current week
                        Case Is = 2
                            Set WeekRange = Workbooks(FileName).Worksheets("TotalHours").Range("I5:O17")
                            SecretTest = 18
                            WeekCol = 15
                        Case Is = 3
                            Set WeekRange = Workbooks(FileName).Worksheets("TotalHours").Range("Q5:W17")
                            SecretTest = 18
                            WeekCol = 23
                        Case Is = 4
                            Set WeekRange = Workbooks(FileName).Worksheets("TotalHours").Range("A19:G31")
                            SecretTest = 32
                            WeekCol = 7
                        Case Is = 5
                            Set WeekRange = Workbooks(FileName).Worksheets("TotalHours").Range("I19:O31")
                            SecretTest = 32
                            WeekCol = 15

                    End Select

                        If Workbooks(FileName).Worksheets("TotalHours").Cells(SecretTest, WeekCol) = 0 Then

                            Workbooks(FileName).Close

                        Else
                            Dim TempSheetName As String
                            Set NewSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count))
                            TempSheetName = Workbooks(FileName).Worksheets("TotalHours").Range("B2").Value
                            NewSheet.Name = TempSheetName
                            NewBook.Sheets(TempSheetName).Range("A1").Resize(WeekRange.Rows.Count, WeekRange.Columns.Count).Value = WeekRange.Value
                            Workbooks(FileName).Close

                        End If


                    FileName = Dir()

                Loop

        .SaveAs FileName:= _
            InvoiceDirectory & SaveAsName _
            , FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

        Workbooks(SaveAsName).Close
    End With

What's the correct syntax to copy a range of cells from one Workbook to another?

3 Answers3