Posts by Ken Santopietro

Embarking on a project to write macro to take financial analysis figures from sheet in excel and to place selected data in a table in a Word document.
This will save preparation and keying figures twice.
I have not seen this done so I am looking for "head start" code to modify for my situation. Thank you, Ken

Re: Vbs copy cells into named range table

I had tried something else, but I am back to this problem macro again. Anyone willing to glance at my description and review the code as it is? Thank you.

Re: Vbs copy cells into named range table

Hello RoyUK,
I created a table on my second tab, and I messed up the first macro. I tried to use the name of it, but I'm getting busted.
On the second macro, now that there is a table, I desperately need assistance to add the information to that table.
I read what you suggested first and I did that and know I am closer, but still inoperable, if you can take another look. Much thanks.
Ken

Re: Vbs copy cells into named range table

Sorry... I was working on only one macro in the sheet. Here it is. Thanks.

Re: Vbs copy cells into named range table

Thanks.
[ATTACH=CONFIG]69885[/ATTACH]

I have sheet1 where I have person key in bid information, 1 row, 11 columns of data.

They may key up to 5 bid lines.

I want to hit a macro button and have these lines inserted at the end of the named data range on sheet2. On sheet2, I call it "BIGBIDLIST"

I have been looking for samples to get started with code, but all of them used unfamiliar terms.

Any help on getting started would be great. Thank you. Ken

Re: Variable sheet reference and dynamic copy references

eureka -- winner. I also had to put in a line for xlPasteFormulasAndNumberFormats
and it now works.
Thank you very much!

Re: Variable sheet reference and dynamic copy references

I really appreciate it. but I plugged it in and.. the paste line just sticks.. nothing pastes still.

For Each ws In Worksheets
        Select Case ws.Name
        Case "ARS", "AUD", "CAD"
             'ws.Activate
            ws.Range(ws.Range("D24").Value, ws.Range("D25").Value).Copy
            ws[COLOR=#00FF00].Range(ws.Range("B24").Value, ws.Range("B25").Value).PasteSpecial xlValues[/COLOR]
            ws.Range(ws.Range("B24").Value, ws.Range("B25").Value).PasteSpecial xlPasteColumnWidths
        End Select
    Next ws

Re: Variable sheet reference and dynamic copy references

Okay. I changed to Name, now it stops at the copy step. This update was made to the vba in the sample worksheet I posted. See anything else? Sorry to trouble.

Private Sub CommandButton3_Click()
Dim ws As Worksheet
 ' loop through data sheets copying an area of known data
For Each ws In Worksheets
    Select Case ws.Name
    Case "ARS", "AUD", "CAD"
    Range(Range("D24").Value, Range("D25").Value).Copy
    Range(Range("B24").Value, Range("B25").Value).PasteSpecial xlValues
    Range(Range("B24").Value, Range("B25").Value).PasteSpecial xlPasteColumnWidths
    End Select
Next ws
 '  Bring back to main screen.
Sheets("USD").Activate
Range("D29").Select
Application.CutCopyMode = False
End Sub

Re: Variable sheet reference and dynamic copy references

I like that separate option.. where you have Case "Sheet3", "Sheet4" ' etc

Still doesn't copy anything, but doesn't break
Here is the file to look at.
Thanks.

Re: Variable sheet reference and dynamic copy references

Thanks!
I now get an error with the "Sheet " & x. You see, Sheet3 is called ARS, Sheet4 is called AUD, etc.
But I wanted to loop through, and I thought that I read that you could still, in VBA, call them by the Sheet numbers. A simple thing has become a problem. Thanks so much for your help with Range.
Ken

I have sheets named after currencies, like USD, CAN...
I want to loop through them referring to them as Sheet3, Sheet4, etc. because I thought I can.
There is a cell reference, like "AC1" in a cell, and it will be the beginning of the copy range.
Then I want to use another cell reference, like "AE1" in a cell, and it will be the beginning of the paste range.
I cannot get this code to stop debugging.

Private Sub CommandButton3_Click()
      Dim x As Long
' loop through data sheets copying an area of known data
      For x = 3 To 15
      Sheets("Sheet " & x).Select
          Range(Cells("D24").Text, Cells("D25").Text).Copy
             Range(Cells("B24").Text, Cells("B25").Text).PasteSpecial xlValues
             Range(Cells("B24").Text, Cells("B25").Text).PasteSpecial xlPasteColumnWidths
      Next x
      '  Bring back to main screen.
Sheets("USD").Activate
Range("D29").Select
Application.CutCopyMode = False
End Sub

Thank you.

Re: must reference sheet1, sheet2, not sheet names

Thanks -- I was trying to modify this original..

' Used help of Ozgrid to do this!
      Dim r As Long, rSheets As Range, rData As Range
' blank on purpose
      With Sheets("USD")
          Set rSheets = .Range("d39").CurrentRegion
          Set rData = .Range(.Range("d27").Value, .Range("d28").Value)
          For r = 1 To rSheets.Rows.Count
              rData.Copy
             Sheets(CStr(rSheets(r, 1))).Cells(3, CStr(rSheets(r, 2))).PasteSpecial xlValues
             Sheets(CStr(rSheets(r, 1))).Cells(3, CStr(rSheets(r, 2))).PasteSpecial xlPasteColumnWidths
          Next r
      End With

When it worked it was copying one range to all of the sheets. I needed to copy a variable range from sheet3, sheet4, etc. onto a variable spot on the same sheets. I have the variable ranges on sheet1.
Thanks.

I want to ignore sheet names and instead reference sheets in a loop as sheet1, sheet2
My sheet names have codes that are good for the user to pick from quickly, but not for my simple coding skills

my vba doesn't work.

loop through data sheets copying an area of known data
      For x = 3 To 15
      Sheets("Sheet " & x).Select
          Range(Sheet1.Range(Cells((42 + x - 3), 6).Value, Cells((42 + x - 3), 7).Value)).Copy
             Sheets(CStr(rSheets(x, 1))).Cells(3, CStr(rSheets(x, 2))).PasteSpecial xlValues
             Sheets(CStr(rSheets(x, 1))).Cells(3, CStr(rSheets(x, 2))).PasteSpecial xlPasteColumnWidths
      Next x

Re: Fix loop for cell reference totally stuck

Can you take another look?

latest effort for this..

Private Sub CommandButton3_Click()
'2.
      Dim r As Long, rSheets As Range, rData As Range
'4.
      With Sheets("USD")
          Set rSheets = .Range("D40").CurrentRegion
          Set rData = .Range(.Range("E28").Value, .Range("E29").Value)
          For r = 1 To rSheets.Rows.Count
              rData.Copy
             Sheets(CStr(rSheets(r, 4))).Cells(3, CStr(rSheets(r, 6))).PasteSpecial xlValues
             Sheets(CStr(rSheets(r, 4))).Cells(3, CStr(rSheets(r, 6))).PasteSpecial xlPasteColumnWidths
         Next r
     End With
'14.
End Sub

What it does is copy from current sheet onto the other sheets.
That is not what I want.
I want to copy from sheet 3 (an area per the chart on sheet 1) to an adjacent area on sheet 3,
then do it again, from sheet 4, 5, 6 etc. through 15.
Cannot figure, and already uploaded worksheet.
Ken

Re: Fix loop for cell reference totally stuck

Can someone pick up the challenge? The format for the line in question is my hurdle.
Ken

Re: Fix loop for cell reference totally stuck

norie,
I tried to post my answer to the thread.
Thank you.

Re: Fix loop for cell reference totally stuck

Sheet1 is actually named USD.
It has a table on it (each column is a calculated reference to a Cell on another Sheet)
for sheets 3 to 15, I have to copy four columns from sheets 3 to 15, and paste to other columns on sheets 3 to 15.
The references of what columns to copy and where to paste are on this table on Sheet1.
Doing this is the crux of the problem.
Ken

Table on Sheet1.

[TABLE="width: 422"]

Sheet #

Sheet

[TD="colspan: 2"]Dependent of this sheet[/TD]

NAME

Copy to

[TD="colspan: 2"]Copy ranges on sheets[/TD]

on sheets

3

ARS

PX

PT1

PW18

4

AUD

PX

PT1

PW18

5

BRL

QA

PW1

PZ18

6

CAD

PZ

PV1

PY18

7

CNY

PU

PQ1

PT18

8

EUR

PU

PQ1

PT18

9

JPY

PU

PQ1

PT18

10

MXN

PU

PQ1

PT18

11

NZD

PU

PQ1

PT18

12

RUB

PU

PQ1

PT18

13

SEK

PU

PQ1

PT18

14

ZAR

QA

PW1

PZ18

15

INR

PU

PQ1

PT18

[/TABLE]

Re: Fix loop for cell reference totally stuck

Quote from tjrt;773927

Working with Tables sometimes require different methods. Can you supply a working sample of your Workbook?

I have, thank you.

Re: Fix loop for cell reference totally stuck

I uploaded the file. I have names on all of the sheets, so decided to just call them by Sheet1, 2, etc. You are allowed to do that, right?