Trying to copy data from one workbook to another

[Wendy]

Hi

I want to copy the daily post over to the issue, receipt, or overdue workbooks depending on which column the postal figure has been entered into. I was trying to use a button on the issue sheet, it doesn't like Range("A & LoopNo").Select and trying to get the R1C1 to change as needed.

Can you help please?

Thanks

Wendy

Sub PosttoIssue()
Dim Rowcounter As Integer 'No of entered rows
Dim LoopNo As Integer 'Loop value
Dim RefPaymt As String
Dim RefName As String


Windows("post.xls").Activate
'Application.Sheets("W1 Mon").Range("G1").Select
'ActiveCell.FormulaR1C1 = "=COUNTA(r[1]c[-5]:r[19]c[-5])" ' counts no of entered rows between B2:B20
Rowcounter = Application.Sheets("W1 Mon").Range("G1").Value 'get No of rows value store it in rowcounter

For LoopNo = 2 To Rowcounter

'c=cell

For Each c In Worksheets("W1 Mon").Range("D" & LoopNo)
If c > 0 Then
Worksheets("W1 Mon").Range("A" & LoopNo, "B" & LoopNo).Select

Selection.Cut
Windows("Issue.xls").Activate

Range("A & LoopNo").Select
Windows("Issue.xls").Activate

RefPaymt = "R"& LoopNo &"C1"
RefName ="R" & LoopNo & "C2"
ActiveCell.FormulaR1C1 = _
"='[POST.xls]W1 Mon'! RefPaymt &"" ""&'[POST.xls]W1 Mon'! Refname"
End If 'c>0

Next c
Next LoopNo

 

[Ardus Petus]

Try:
Range("A" & LoopNo)

HTH
--
AP

"Wendy" <[email protected]> a écrit dans le message de (e-mail address removed)...
Hi
I want to copy the daily post over to the issue, receipt, or overdue
workbooks depending on which column the postal figure has been entered into.
I was trying to use a button on the issue sheet, it doesn't like Range("A &
LoopNo").Select and trying to get the R1C1 to change as needed.
Can you help please?
Thanks
Wendy
Sub PosttoIssue()
Dim Rowcounter As Integer 'No of entered rows
Dim LoopNo As Integer 'Loop value
Dim RefPaymt As String
Dim RefName As String


Windows("post.xls").Activate
'Application.Sheets("W1 Mon").Range("G1").Select
'ActiveCell.FormulaR1C1 = "=COUNTA(r[1]c[-5]:r[19]c[-5])" ' counts no of
entered rows between B2:B20
Rowcounter = Application.Sheets("W1 Mon").Range("G1").Value 'get No of rows
value store it in rowcounter

For LoopNo = 2 To Rowcounter

'c=cell

For Each c In Worksheets("W1 Mon").Range("D" & LoopNo)
If c > 0 Then
Worksheets("W1 Mon").Range("A" & LoopNo, "B" & LoopNo).Select

Selection.Cut
Windows("Issue.xls").Activate

Range("A & LoopNo").Select
Windows("Issue.xls").Activate

RefPaymt = "R"& LoopNo &"C1"
RefName ="R" & LoopNo & "C2"
ActiveCell.FormulaR1C1 = _
"='[POST.xls]W1 Mon'! RefPaymt &"" ""&'[POST.xls]W1 Mon'!
Refname"
End If 'c>0

Next c
Next LoopNo

 

[Wendy]

Thanks, that bit works now.

The R1C1 formula is still a problem.

Wendy

 

[Dave Peterson]

First, these newsgroups are text only--no attachments and no HTML posts. (I
think it makes your message much more difficult to read.)

But this (untested) may work:

ActiveCell.FormulaR1C1 = _
"='[POST.xls]W1 Mon'!" & RefPaymt & "&"" ""&'[POST.xls]W1 Mon'!" & Refname

You want your variables outside the strings--otherwise, you'll see things like
RefPaymt in the formula.